Date & SQL problem

[Butch]

I'm having a problem with my code. I'm looking for dates on a table and open a form after, so I use this SQL statement to extract the dates.

DoCmd.openForm "frmJan"
With Forms!frmJan

strSQL = "SELECT * FROM [tblKevin]" & _
"WHERE ([entryDate] = # " & (Me.txtReturnDate) & " #);"

.RecordSource = strSQL
End With
It finds any date between 10/04/2004 to 30/04/2004 no problem, but any dates between 01/04/2004 and 09/04/2004 it can't find them at all.

Do I have to change format, and if so how is it done?

thanks

 

[Mile-O]

It's not the format; it's the spaces and also the lack of a space.

strSQL = "SELECT * FROM [tblKevin] " & _
"WHERE ([entryDate] = #" & CDate(Me.txtReturnDate) & "#;"

Why are you building this SQL in VBA though? Just build a defined QueryDef as it is the proper way to do this - since your SQL statement is not dynamic.

 

[Butch]

It's not the format; it's the spaces and also the lack of a space.




Why are you building this SQL in VBA though? Just build a defined QueryDef as it is the proper way to do this - since your SQL statement is not dynamic.

I never use the 'QueryDef' function.
Could you please give me an example?!

 

[Mile-O]

Just make a query. Once you've saved it, that's your QueryDef.

 

[Butch]

I tried that sql code with the extra space and CDATE function but still having my original problem??

 

[Mile-O]

All you need to do is make one query with the SQL being:

SELECT *
FROM tblKevin
WHERE tblKevin.EntryDate = CDate(Forms!MyForm!txtReturnDate);

Change MyForm to the name of the form you are referring to. Put it in brackets if, by chance, it contains spaces or _ characters.

Save it.

Since your form obviously puts this query into its RowSource, just set the RowSource property of the form to the name of the query.

Why are you trying to make this so difficult?

 

[Butch]

That worked perfectly.
Thanks for your help!

 

[Mile-O]

Yeah!