Data type mismatch in criteria expression for opening a recordset

[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

 

[reggied]

I was using the recordset to lookup a specific record based on a date selected from a Summary query. Here is the rest of the code:

    Dim rs As Recordset
    
    Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 LDOW FROM qryWeeklyForecast WHERE LDOW=""" & LDOW & """", dbOpenSnapshot)
    
    If rs.EOF Then
        MsgBox "No date found"
    Else
        
    End If
    
    DoCmd.OpenForm "frmEstReqPurchByDateAll", acFormDS, , "LDOW= #" & LDOW & "#"

    rs.Close
    Set rs = Nothing

 

[plog]

No, you were testing for the existence of a record, you didn't look up anything you didn't already know.

Further, if you have two records with the same LDOW value the form would open to just one of them.

 

[reggied]

Yes, i see it now. I got rid of all that stuff and stuck with just opening the form i need. I think I originally put those lines in there before I created the new forms I wanted to open.

 

[Ken Sheridan]

DoCmd.OpenForm "frmEstReqPurchByDateAll", acFormDS, , "LDOW= #" & LDOW & "#"

When building a date literal in code it prudent to format the date either in US short date format, as in MajP's reply above, or in an otherwise internationally unambiguous format such as the ISO standard for date notation of YYYY-MM-DD, as in cheekybuddha's reply. Otherwise, on a system like mine, not using US date format, the date could be misinterpreted, changing 4th July to 7th April for instance.

When opening a form filtered to one or more rows you can check for the existence of a match in the form's Open event procedure, e.g by calling the DLookup function. The Open event procedure has a Cancel argument whose return value can be set to TRUE if no match is found. The form will not then open. Be sure to handle the ensuing error in the calling code when the OpenForm method is called. If opening a report, the report's NoData event procedure can be used in the same way.