CODEWITHSUNDEEP
sqlvbams-accessms-access-2010
leeand00
leeand00

Reputation: 26402

Dlookup with date criteria query parameter?

I have a query I want set query parameters from within for a DLookup:

Query checkDate_Count_ForDangerousTickets:

SELECT count(*) AS numRelatedTickets
FROM DangrousTicketList
WHERE DateValue(backUpDate)>=DateValue(StartDate) And DateValue(backUpDate)<=DateValue(EndDate)
GROUP BY backUpDate;

I call the query from dlookup setting the query parameter backUpDate:

DLookUp("[numRelatedTickets]", "[checkDate_Count_ForDangerousTickets]", _ 
"[backUpDate]=" & Format("01-27-2013", "\#mm\/dd\/yyyy\#"))

And I get the error:

Run-time Error '2471'
The expression you entered as a query parameter produced this error: 
'backUpDate'

It's as though the query parameter backUpDate is not being set for some reason.

Upvotes: 1

Views: 1493

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123819

Based on HansUp's comment and my own tinkering I believe you're going to have to use a little VBA function to run your parameter query:

Public Function LookupDangerousTicketCount(backUpDate As Date) As Variant
    Dim qdf As DAO.QueryDef, rst As DAO.Recordset
    Set qdf = CurrentDb.QueryDefs("checkDate_Count_ForDangerousTickets")
    qdf!backUpDate = backUpDate
    Set rst = qdf.OpenRecordset(dbOpenSnapshot)
    If rst.BOF And rst.EOF Then
        LookupDangerousTicketCount = Null
    Else
        LookupDangerousTicketCount = rst!numRelatedTickets
    End If
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
End Function

Then instead of your DLookup() call you can just use

LookupDangerousTicketCount(#2013-01-27#)

Upvotes: 3

Related Questions