date data type in RunSQL method

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

Andrej

Hello!

I was attempting a simple APPEND query to fill up a table.
This query had two variables; work_order of INTEGER datatype
and date_of_production of DATE datatype. These two atributes
represent the filter for my data before appending. And I can
use either one of them, both or none at all ...

But ...

I can't work with date out of a VBA code. Date filtering works
fine if I use it in a query as a criteria like this:

[forms]![frm_test]![txt_date].[Value]

but that doesn't work if I choose not to filter data by date.
(you know, txt_date.Value = NULL !!)

If I try to filter data on date through a RunSQL method then I
either get an error on incompatible datatype or an empty
selection as a result.
The really stupid thing that bugs me is that I take values for
a filter directly from a table where they're stored and still
nothing?!?! I don't get it?? How should I form that SQL so it
would work?

These return nothing:
DoCmd.ApplyFilter , "date_of_production = #" &
FormatDateTime(cbo_date.Value, 2) & "#"
DoCmd.ApplyFilter , "date_of_production = #" & cbo_date.Value & "#"

I even tried it with BETWEEN to get SOMETHING as a result, but
still nothing so I really don't have a clue to whats wrong ...


There must be a special way to formulate this into working, but
I couldn't find anything on the problem and I'm just out of
ideas!
Can anyone please offer me some?

Thank you,
Andrej
 
Try the following

DoCmd.ApplyFilter , "date_of_production = #" &
Format(cbo_date.Value, "dd mmm yyyy") & "#"

Hope That Helps
Gerald Stanley MCSD
 
Back
Top