Errors Trying to Open Recordset...

[aregan1]

I'm fairly new to Access VBA coding, and having a problem creating a recordset. I have the following code in an Access 2000 application:

Dim rstResources As Recordset
Dim strSQL2 As String

strSQL2 = "SELECT EventID, " _
& "[quantity reserved], ReservationStartDate, " _
& "ReservationStartTime, ReservationEndDate, " _
& "ReservationEndTime FROM EventResourcesDetails " _
& "WHERE ResourceID = " & cboResourceName _
& " AND (#" & txtStartDate _
& "# BETWEEN ReservationStartDate and ReservationEndDate OR #" _
& txtEndDate _
& "# BETWEEN ReservationStartDate and ReservationEndDate);"

Set rstResources = CurrentDb().OpenRecordset(strSQL2)

------------------------------------------------

When I run this code as is, I get a "type mismatch" error on the last statement. I tried adding double quotes, changing it to:

Set rstResources = CurrentDb().OpenRecordset("strSQL2&quot

and then get the following error instead of "type mismatch":

"The Microsoft Jet database engine cannot find the input table or query 'strSQL2'. Make sure it exists and that its name is spelled correctly."

I displayed the strSQL2 string, and it looks okay. Can anyone help me? Thanks in advance...

- Anita

 

[cmmrfrds]

The code you had here does not look right. You need to surround the date literals with #, NOT the field name you are checking.

& " AND ( txtStartDate " _
& "BETWEEN #" & ReservationStartDate & "# and " &
& "#" & ReservationEndDate & "# OR " _

 

[aregan1]

I'm sure you're right about the dates, but just for testing purposes, I first tried running the following simpler SQL, with no date criteria at all, and still got a "type mismatch" error:

strSQL2 = "SELECT EventID, " _
& "[quantity reserved], ReservationStartDate, " _
& "ReservationStartTime, ReservationEndDate, " _
& "ReservationEndTime FROM EventResourcesDetails " _
& "WHERE ResourceID = " & cboResourceName

ResourceID is defined in my table as a long integer, and in my test, cboResourceName held the value 34, so the result looked like:

SELECT EventID, [quantity reserved], ReservationStartDate, ReservationStartTime, ReservationEndDate, ReservationEndTime FROM EventResourcesDetails WHERE ResourceID = 34

Do you have any idea why this would get a type mismatch? Thanks for the help...

- Anita

 

[aregan1]

I found the solution to my "type mismatch" by searching through old threads. Someone had recommended going into the references, and unselecting MS ADO 2.1, while selecting DAO 3.6. After I did this, the error disappeared. But thanks for your help...

- Anita

 

[cmmrfrds]

Apparently, you are using Access 2000 or above where you have the option of using either ADO or DAO record objects. You can use either or both, if you explicitly define your objects and have the correct libraries checked. The libraries can coexist that way.

Dim rs as DAO.Recordset
Dim rs as ADODB.Recordset