Solved Error 94 (Invalid use of Null) on a line in a Function used in a database adapted from Northwind 2.0 Developer (1 Viewer)

[MajP]

'PURPOSE:
'   Convert the date to ISO 8601 International date format as expected by Access. Needed when building a SQL string with literal
Public Function ToAccessDate(ByVal dt As Date) As String
    ToAccessDate = Format(dt, "yyyy-mm-dd hh:nn:ss")
End Function

 

[GPGeorge]

I would not call that USA format?

Possibly not the best way to express it. The point is that dates are to be handled in the "yyy-mm-dd hh:mm:ss" format in order to be consistent across all locations, rather than allow them to default to the US format, which fails in some situations.

 

[gemma-the-husky]

Is the dlookup guaranteed to work. Maybe the problem is that the dlookup to the stocktake is not finding a match and returns null. In which case wrap the dlookup in NZ(0)

I mean in the ProductLastStockTakeQuantity assignment.

 

[Josef P.]

Briefly to:

StringFormatSQL("intProductID_FK = {0} and dtStockTakeDate = CDate({1})", lngProductID, ToAccessDate(dtStockTakeDate))

ToAccessDate returns a string => the return value is inserted in {1} as a string.

For comparison (tested with NW 2.3):

Dim lngProductID As Long
Dim dtStockTakeDate As Date

lngProductID = 1
dtStockTakeDate = Date

Debug.Print ToAccessDate(dtStockTakeDate)
Debug.Print StringFormatSQL("intProductID_FK = {0} and dtStockTakeDate = {1}", lngProductID, ToAccessDate(dtStockTakeDate))
Debug.Print StringFormatSQL("intProductID_FK = {0} and dtStockTakeDate = {1}", lngProductID, dtStockTakeDate)

Output:

2023-12-01 00:00:00
intProductID_FK = 1 and dtStockTakeDate = '2023-12-01 00:00:00'
intProductID_FK = 1 and dtStockTakeDate = #2023-12-01 00:00:00#

 

[jdraw]

George, David

Are you familiar with Access Blog Microsoft ? I stumbled onto the site after watching Daniel Pineault's new youtube video re New SQL View Option. I noticed on the "official Access Blog" there was an update to the NW2 templates, but I have not seen that site referenced elsewhere (perhaps I don't look hard enough). It doesn't say, and Daniel comments, that this site is not necessarily maintained nor complete. Daniel is re-identifying that Access is missing from the M365 apps update history.

Note: I posted here because the thread deals with NorthWind 2 Developer template and I noticed my version did not have a function the OP was referencing. I downloaded the latest NW2Dev template from the link in my version of Access and it also did not have the function.

 

[GPGeorge]

George, David

Are you familiar with Access Blog Microsoft ? I stumbled onto the site after watching Daniel Pineault's new youtube video re New SQL View Option. I noticed on the "official Access Blog" there was an update to the NW2 templates, but I have not seen that site referenced elsewhere (perhaps I don't look hard enough). It doesn't say, and Daniel comments, that this site is not necessarily maintained nor complete. Daniel is re-identifying that Access is missing from the M365 apps update history.

Note: I posted here because the thread deals with NorthWind 2 Developer template and I noticed my version did not have a function the OP was referencing. I downloaded the latest NW2Dev template from the link in my version of Access and it also did not have the function.

Did the version from the web url include the newest version?

I think what happens is that once you've used the in-app method to obtain the template, it's stored locally in

C:\Users\yourusernamegoeshere\AppData\Roaming\Microsoft\Templates\Access

If Access sees there is already a copy there, it doesn't bother to replace it, so you keep using the original. I think if you rename or move or delete the template, Access will download the latest version.

 

[jdraw]

George,

Yes, the link you provided did have the function. I have renamed the versions of NW20Dev.
Thanks.