Filter date

[omaggi]

Hi everybody.
I'm doing a job in access 2007 with a db MySQL.
In one form I need to filter 4 box (2 are ID and 2 are date).
For this form I did a query (select *) and after I use the key "filter".
So, I don't have problems with the 2 ID but when I try to filter the date I occured in an error: (Error run-time '3464').

The Code that I wrote is the following:
Private Sub bCerca_Click()
DoCmd.RunCommand acCmdRefresh
Dim Filtra As String
If ccPuntoMisura <> "" Then
Filtra = Filtra & " AND id_PuntoMisura = " & ccPuntoMisura
End If
If ccObis <> "" Then
Filtra = Filtra & " AND id_Obis = " & ccObis
End If
Filtra = Mid$(Filtra, 5)
Me.Filter = Filtra
Me.FilterOn = True
End Sub

Now I have txtBox1 with the beginning date and txtBox2 with the finish date. I tried with between date1 and date2 but doesn't work...

Do you have any suggestions?

Thanks a lot and sorry for my english...

See ya

 

[namliam]

Where is the "between" ??

ID's or numbers you dont need to enclose anything but if your using dates or strings/texts you have to enclose them.

#value# for dates
"text" for texts

I think that looks to be the problem, welcome to AWF

 

[omaggi]

Thanks for the welcome...

Value?
I don't understand...

The things is that the two comboBox (these with Ids) work perfectly and filter without problems. Is the date that I have problem to write it down the code.

I'm in big trouble...

Maybe you know a simplest method to do these filters...

Any Suggestions?

 

[namliam]

#value# for dates
"text" for texts

Value as in a date...

You cannot do in a query:
SomeDateFieldInTable = 19-10-2009
While you can do
SomeNumberFieldInTable = 19

With dates it has to be:
SomeDateFieldInTable = #10-19-2009#

Note the ## and the US Date Format that is required to make it work correctly!

For texts you need:
SomeTextFieldInTable = "Some name or city stuff"

 

[omaggi]

@namliam

Thanks a lot. Now works properly.
It's incredible I've lost about 2 days for this thing.
My Error was to write ' instead of #...

Thanks and have a good evening... Greetings omaggi

 

[namliam]

With dates it has to be:
SomeDateFieldInTable = #10-19-2009#

Note the ## and the US Date Format that is required to make it work correctly!

PLEASE take special note of above "US Date Format"

Or you will be back with further questions in 2 weeks why this is now failing