OpenRecordset vs. Query

[crispy-bacon]

So I've run into a problem and I've tried everything I can think of and can't seem to find a solution. The problem I am having is the following:

Dim rs As DAO.Recordset
Set db = CurrentDb()


Set rs = db.OpenRecordSet("...")

So when I insert a query name (or SQL string) instead of "..." in the OpenRecordset command it does not return any records. When I run the query with the exact same criteria (same SQL statement) by clicking run or datasheet view it returns the appropriate records.

There are no errors given by ms access it is always rs.EOF when I check if there are records. I have concatenated the criteria into the SQL string properly as far as I know as I have redone it 3 or 4 times.

One more detail is that the strSQL that I am trying to use in the OpenRecordset command is selecting data from another query (not table).

Any help is appreciated, I can post more details if required.
Thanks!

[orange]

I think you should show us all your vba code and the SQL string.

[crispy-bacon]

Will post the code first thing tomorrow, I am not currently on the machine with the access program

[Mordred]

Will post the code first thing tomorrow, I am not currently on the machine with the access program

You mean you don't take it home??

[crispy-bacon]

I've tried various things changing weekDate to referencing the control on the form with the right value with no luck.

Code:

Private Sub equipConflict(weekDate As Date, weekNum As Integer)
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim Beam As String
Dim lstName As String
 
strSQL = "SELECT [qryEquipConflict].[AncillaryID], [qryEquipConflict].[contIndex], [qryEquipConflict].[Beam], [qryEquipConflict].[StartDate], [qryEquipConflict].[EndDate] " _
& "FROM [qryEquipConflict] " _
& "WHERE ([qryEquipConflict].[StartDate] Between " & weekDate & " And " & (weekDate + 6) & " );"
 
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
 
If Not rs.EOF Then rs.MoveFirst
 
'Do While Not rs.EOF Never enters loop
 
rs.Close
Set rs = Nothing
End Sub

[orange]

You have a function with weeknum parameter, but you don't use it???

You open a recordset but you don't loop through it??

You may wan t to do some research on using Dates in access, and the need for the # delimiter.

You may also do some investigation of debugging techniques-- msgbox, step through code; debug.print...

[ssanfu]

You are sure the query "qryEquipConflict" has records??
The two date parameters need to be delimited.

Try:

& "WHERE ([qryEquipConflict].[StartDate] Between #"  & weekDate & "# And #" & (weekDate + 6) &  "#);"

[crispy-bacon]

@orange:
-I removed irrelevant code from the lines of code I posted. The weekNum parameter is used
-Line 17 is the Loop that uses the recordset which i also did not include

Thank you both orange and ssanfu the delimeters were what was needed the strSQL now returns records.

Silly mistake thanks!