VBA dating

  • Thread starter Thread starter Andrej
  • Start date Start date
A

Andrej

Hello!

I am interested in using a Date datatype in an SQL call from VBA??

for example like this:
DoCmd.ApplyFilter , "date_of_production = #" & _
FormatDateTime(cbo_date.Value, 2) & "#"

or this:
DoCmd.ApplyFilter , "date_of_production = #" & cbo_date.Value & "#"

where cbo_date.value represents a valid date.

I'm wondering because I think this should work, but it doesn't!
I get no selected records out of it, whether I get a correct result

when I put this in a query criteria:
[forms]![frm_test]![cbo_date].[Value]

which is basically the same thing! Or is it?? I just don't know why I get
different results each time ... ????

Does anyone know?? phleeese ...

Thank you,
Andrej
 
The difference is that in the ApplyFilter statements, you used *literal*
dates which must be in the US format "mm/dd/yyyy" enclosed in hashes. My
guess is that you Regional Settings use a different "Short Date" format from
above and therefore Access doesn't filter correctly.

Try:

DoCmd.ApplyFilter , "[date_of_production] = #" & _
Format(Me.cbo_date.Value, "mm/dd/yyyy") & "#"

or simpler, try:

DoCmd.ApplyFilter , "[date_of_production] = [cbo_date]"

or

DoCmd.ApplyFilter , "[date_of_production] = Form![cbo_date]"

and Access should interpret your filter correctly.
 
Much obliged, good sir!!

I would take you out for a beer, or whatever you prefer, if I could!!

You have really brightened up my day!! Your solution works perfectly! :D

Thanks a lot,
Andrej
 
Back
Top