Date Comparison

[elvenmaiden]

Am trying to compare a date time picker value to a date field in access, but cannot come up with the right syntax. Would appreciate any suggestions

i.e. .Open "Select * from tblDate where EffectiveDate = # & dtpEffectiveDate.Value & #

This syntax does not retreive any records.

Also tried to set the dtpEffectiveDate.Value to a date variable and use that in the Select statement and tried assigning DATEVALUE(dtpEffectiveDate.Value) to the variable and used this in the select.

Thanks for any help

 

[x508]

Are you sure you have the field in Db as data type "Date"

Anyway, this is what worked for me.

It searches for dates between, but I'n sure you might find it usefull

Where (Group = '" & lstGroups.List(i) & "') AND (Date BETWEEN #" & date1.Value & "# AND #" & date2.Value & "#)"

**********************************
May the Code Be With You...
----------
x50-8 (X Fifty Eigt)

 

[BrianLW]

Try This:

[tt]
.Open "Select * from tblDate where EffectiveDate = #" & format(dtpEffectiveDate.Value,"mm/dd/yyyy&quot & "#
[/tt]

Or:

[tt]
.Open "Select * from tblDate where EffectiveDate = #" & cdate(dtpEffectiveDate.Value) & "#
[/tt]