VB Recordset problem

Bevrules
Bevrules used Ask the Experts™
on
Any ideas on what could be wrong with the following code?  It's very basic just to make sure i could get started on this report!  The Inputboxes work just fine (and I am entering valid dates), but I'm getting this error:  
Run-time error '13'.  Type mismatch.

When this error shows and I click debug, this line is highlighted:
Set rst = db.OpenRecordset(SQL_1, dbOpenSnapshot)

Any thoughts or suggestions?
----------------------------------------------------------
Private Sub Report_Open(Cancel As Integer)

Dim db As Database
Dim rst As Recordset
Dim Start_Date As Date
Dim End_Date As Date
Dim SQL_1 As String

Set db = CurrentDb

Start_Date = InputBox("Please enter report Start Date:" & vbCrLf & vbCrLf & "(e.g. 01/01/2002)", "Start Date", Format(Date, "mm/dd/yyyy"))
End_Date = InputBox("Please enter report End Date:" & vbCrLf & vbCrLf & "(e.g. 01/31/2002)", "End Date", Format(Date, "mm/dd/yyyy"))

lbl_Start_Date.Caption = "From:  " & Start_Date
lbl_End_Date.Caption = "Through:  " & End_Date

SQL_1 = "SELECT ROW_ID, NAME " & _
        "FROM HELP_DESK;"

Set rst = db.OpenRecordset(SQL_1, dbOpenSnapshot)

End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Change:

Start_Date = Cdate(InputBox("Please enter report Start Date:" & vbCrLf & vbCrLf & "(e.g. 01/01/2002)", "Start Date", Format(Date, "mm/dd/yyyy")))

do the same for the end date

The problem is that the input returns a string and Start_Date Expects a date.  By the way if the use enters and invalid date it will abend.  Better move the result of the input to a string variable and check that variable.  As in:

strDate = InputBox("Please enter report Start Date:" & vbCrLf & vbCrLf & "(e.g. 01/01/2002)", "Start Date", Format(Date, "mm/dd/yyyy"))
if (isDate(strDAte) = true) then
   Start_Date = Cdate(strDate)
else
   msgbox("I say a Date Moron... Look in the Calendar")
   ' Okay this is probably a not very polite way of saying it
end if

Much better, use the datePicker control. It will produce a Calendar.  Users wont be able to enter an invalid date and returns the value in Date format.

Author

Commented:
These are good suggestions for controlling the date, but I'm still getting the above error (Run-time error '13'.  Type mismatch.)--even when every other line of code is commented out!
Whats the ;  after your table name in your sql statement for?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2012

Commented:
Change this line:
SQL_1 = "SELECT ROW_ID, NAME " & _
       "FROM HELP_DESK;"

To:
SQL_1 = "SELECT ROW_ID, [NAME] " & _
       "FROM HELP_DESK;"

Anthony
Top Expert 2012
Commented:
Or if you have both the DAO and the ADO libraries referenced in your project than do one of the following:
1. Remove the ADO library
2. Move the DAO library before the ADO library
3. Change these lines:
Dim db As Database
Dim rst As Recordset

To:
Dim db As DAO.Database
Dim rst As DAO.Recordset

Anthony

is is just me or is it in fact the case that you are NOT making any use of the Start and End dates?   I see NOTHING in the SQL_1 that makes any use of the dates in any way?

You write the dates to the two Label Controls, and then do nothing with them at all.

Arthur Wood

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial