DoCmd.RunSQL statement

[Vcscwi]

I'm updating some old code which had dates hard coded into the "WHERE" of an SQL statment.

Here is the original which works but isn't practical.

DoCmd.RunSQL ("INSERT INTO CommMatrix ( HICode, HealthInsurer, FormerName) SELECT v2_CommMatrixReport.HICode, v2_CommMatrixReport.HealthInsurer, v2_CommMatrixReport.FormerName FROM v2_CommMatrixReport WHERE (((v2_CommMatrixReport.CommMonth)=#1/1/00#));")

To increase flexibility I created a date variable called dJan. This pulls data from a form where the user can pick the year in a combo box. So it looks like:

dJan = CDATE("January 1, " & combo117.value) or 1/1/2000
The debugger show it as a date with the correct value.

When I changed the "WHERE" from #1/1/00# to dJan, the statement didn't recognize dJan as a value. It prompts me to enter the date.

Any ideas would be great!

 

[PHV]

Replace this:
=dJan));")
By this:
=#" & dJan & "#))")

Or, to avoid localisation issue:
=#" & combo117.Value & "-01-01#))")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244