Data type mismatch in criteria expression for opening a recordset (1 Viewer)

[reggied]

I'm trying to fix a code that was working the last couple weeks but suddenly stopped working.

I have a form with a cells that fire actions when the user double clicks. So, out of no where, I am getting this error message regarding the below line of VBA:


Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 LDOW FROM qryWeeklyForecast WHERE LDOW=""" & LDOW & """", dbOpenSnapshot)

LDOW is a date field in both the query I am referencing and in the form where the data is coming froom. Is there a better way to reference LDOW (last day of week)?

 

[pbaldy]

The correct delimiter for a date field is #.

 

[RonPaii]

Any chance LDOW is something that is not a date when you execute the query.

Being as LDOW is a date use # instead of quotes.

Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 LDOW FROM qryWeeklyForecast WHERE LDOW=#" & LDOW & "#;", dbOpenSnapshot)

 

[cheekybuddha]

As per pbaldy, if LDOW is really a datetime datatype, then try:

Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 LDOW FROM qryWeeklyForecast WHERE LDOW=" & Format(Me.LDOW, "\#yyyy\-mm\-dd\#"), dbOpenSnapshot)

 

[plog]

What's the point of that code? Your querying for the exact value you already have.

 

[MajP]

I am with plog, that make no sense. The only thing I can think you are trying to determine if that date already exists. If you are then there are easier way.

if Dcount("*","qryWeeklyForecast", "LDOW = #" format (me.Ldow,"mm/dd/yyyy") & "#") > 0