tLookUp not obtaining a value

[LindaLooUK]

I have a function that I'm using in an update query but it fails because of a null value. The purpose of the query is to insert the Invoice ID (InvID) into the field InvoiceRunID where InvoiceRunID is currently null (and the Status = 5).

gInvDate is a global variable that is set before the query is run and when I run debug.print after the procedure fails, it shows the correct date as shown here.

tbl_InvoiceRuns
InvID InvDateRun
14 02/01/2015 20:59:22

Public Function fGetInvoiceID() As Long
'get the ID of the latest invoice run

fGetInvoiceID = tLookup("[InvID]", "tbl_InvoiceRuns", "InvDateRun = #" & gInvDate & "#")

End Function

Here's the SQL of the query that contains the function:

UPDATE tbl_xyz SET tbl_xyz.InvoiceRunID = fGetInvoiceID()
WHERE ((tbl_xyz.InvoiceRunID) Is Null) AND ((tbl_xyz.Status)=5));

What am I missing guys?!

 

[MarkK]

Check your date comparisons, noting that #1/1/15# <> #1/1/15 00:00:01#. It is a common pattern to run VBA.DateValue() against a date to drop it's time component when comparing date/time values . . .

fGetInvoiceID = DLookup("[InvID]", "tbl_InvoiceRuns", "DateValue(InvDateRun) = #" & DateValue(gInvDate) & "#")

Hope this helps,

 

[LindaLooUK]

Thanks for the reply Mark, it worked a treat.

 

[MarkK]

You bet Linda,