Date Syntax

[LQ]

This is stumping me, although it must be something obvious.

I have an unbound form with a text box for users to enter a date. I want to take that date and generate an SQL statement that will open up my main form showing entries with the specified date. Here is what I have, but it will not work. (similar statements work for other, non date-related textboxes on my form, so I am pretty sure this is a date-specific syntax problem)

'This creates a dynamic SQL statement that searches the ADR log
Dim mySql As String
Dim myWhere As String

'search the [tblADR] table
mySql = "SELECT * FROM tblADR"
myWhere = ""
If Nz(Forms!frmsearchadr!txtadrdate) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " ([letterdate] like " & Forms!frmsearchadr!txtadrdate & ")"
End If

If myWhere <> "" Then
mySql = mySql & " WHERE " & myWhere
End If

DoCmd.OpenForm "frmADR", , mySql
DoCmd.Close acForm, "frmsearchadr"
End Sub

TIA for any suggestions...

 

[Jack Cowley]

Try this:

[letterdate] like '" & Forms!frmsearchadr!txtadrdate & "'"

 

[LQ]

Thanks for the suggestion, Jack. Maybe I am reading your reply wrong, but when I tried that, I got a run-time error 2501 saying the OpenForm action was cancelled.

Here is what I have:

myWhere = myWhere & " ([letterdate] like '" & Forms!frmsearchadr!txtadrdate & "')"

Do I have my single and double quotes in the wrong places? Does the fact that this is a date mean I need to insert some # signs in there somewhere?

 

[Jack Cowley]

myWhere = myWhere & "[letterdate] like '" & Forms!frmsearchadr!txtadrdate & "'"

Give that a whirl...

 

[LQ]

I copied your suggestion, Jack, and now I'm not getting that error, but I am not getting the correct records, either. I put a msgbox in to show what the value of mysql is right before I open the form, and now it says

Select * from tblADR where False

instead of where letterdate = 5/4/01 etc.

Any other thoughts?

 

[Jack Cowley]

This code works for me:

MyWhere = myWhere & " [letterdate] like '" & Forms!frmsearchadr!txtadrdate & "'"


DoCmd.OpenForm "frmADR", , , myWhere

You don't need the sql and the myWhere part was in the wrong place.

 

[LQ]

I thought that I had to use mySql to insert the "WHERE" in my SQL statement? When I use myWhere instead of mySQL, I get ALL records in the database instead of just those with the specified criteria.

I just keep thinking this has to be something with the syntax for date, because I have 16 other (non date) fields in my search form, and everything else runs fine (and pulls up the appropriate records).

 

[Jack Cowley]

Are you putting the Where clause in the right place in OpenForm? The code above filters by date and shows only dates selected from an unbound text field.

 

[LQ]

Thanks for sticking with me, Jack!

I *thought* I was putting the where clause in the right place. I mean, it works correctly for every other unbound field, so why does this one field not work?

Also, if I try to put in this

DoCmd.OpenForm "frmADR", , , mySql

with three commas like you have, I get an error msg "Syntax error in query expression...". I don't get that error if I use only two commas with OpenForm.

 

[Jack Cowley]

You should be able to use just your Where clause (forget the SQL bit) in the 'Where' argument of the OpenForm method for all your requests, not just the Date one. Try it, it should work . Where you have your SQL statement is in the Filter argument of OpenForm and why the Date one did not work there I cannot tell you.

 

[LQ]

I still can't get the form to open up with a specific date as a criteria. I will keep plugging away at it, but luckily the Powers That Be have now decided that the date field is not a necessary search criteria, so I am off the hook so to speak. Still, it drives me crazy when I can't get something to work right!

Thanks again for your all your help, Jack.

 

[Jack Cowley]

Obviously I don't know why your code is not working and mine is. All you can do is what you doing so keep plugging away and good luck.

 

[Coop]

LQ, I have a search form set up. This form allows you to search under any critira in a database including date. Also you can search under multiple criteria and will display search results in a subform.

I will email it to you if you wish, it will give you the answer's to your questions, by checking the code or else it is very easy to add to you current database.

This one has saved me a lot of time in the past.

Regards,
Coop