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

[alan2013]

I'm trying to create a database based on the Northwind 2.0 Developer database, but capturing & displaying dates (including stock-take dates) in Medium format (dd-mmm-yy) instead of Short Format (as in Northwind).

I'm encountering a 'Invalid use of Null' error (Error 94) that point to the following line in the ProductLastStockTakeQuantity Function in Module modInventory.

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

My best guess is that the error is due to dtStockTakeDate in an unexpected format. (Please correct me if I'm wrong).

How should I change this line of code to eradicate the error, please ? Thanks in advance.

 

[CJ_London]

I'm not familiar with the function StringFormatSQL, if you debug.print, what do you get?

i.e. put this line before your ProductLastStockTakeQuantity line

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

 

[GaP42]

CDate is for converting a string to date - I think you already have a date - you need to use Format(Date, "dd-mmm-yy") ?

 

[alan2013]

I'm not familiar with the function StringFormatSQL, if you debug.print, what do you get?

i.e. put this line before your ProductLastStockTakeQuantity line

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

Thanks for your reply. I'm nowhere near my PC just now, and won't be for another few hours now. As soon as I'm able to, I'll try that, and let you know. Thanks again.

 

[cheekybuddha]

Don't mess with dates in SQL strings!

SQL requires that dates be passed in an unambiguous format which means either:
US date format: #mm/dd/yyyy#
or:
ISO date format: #yyyy-mm-dd#

Forget about how you display dates - the conversions will be handled appropriately by Access and your regional format.

The SQL string date format will be handled by the ToAccessDate() function.

Go with:

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

 

[GPGeorge]

I'm trying to create a database based on the Northwind 2.0 Developer database, but capturing & displaying dates (including stock-take dates) in Medium format (dd-mmm-yy) instead of Short Format (as in Northwind).

I'm encountering a 'Invalid use of Null' error (Error 94) that point to the following line in the ProductLastStockTakeQuantity Function in Module modInventory.

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

My best guess is that the error is due to dtStockTakeDate in an unexpected format. (Please correct me if I'm wrong).

How should I change this line of code to eradicate the error, please ? Thanks in advance.

I'd have to crack open the accdb to check for certain. I would say this, though. It's almost always better NOT to apply any formatting to dates prior to the point where you actually want to DISPLAY them in a form or report. It makes no difference to the actual stored value anyway, so you're only increasing internal complexity by applying a Display Format elsewhere.

StrongFormatSQL StringFormatSQL is a function that takes parameters -- here that would be the ProductID and StockTakeDate -- and returns a properly crafted SQL String.

Note that this includes another function called ToAccessDate(dtStockTakeDate). It was added to Developer edition specifically to return a valid date format that Access can use because there are different standards in different locations. I.e. in the US, we use "MM/DD/YYYY", whereas in Europe and other locations, it is "DD/MM/YYYY".
If you've already flipped that dtStockTakeDate to still another format (e.g. Medium Date) it's quite likely that does not handle it properly.

My general advice is to leave the formatting off of date fields until you get to the form or report. If you do need to do it, though, probably the safest approach is yyyymmdd, which is unambiguous.

 

[GPGeorge]

I'd have to crack open the accdb to check for certain. I would say this, though. It's almost always better NOT to apply any formatting to dates prior to the point where you actually want to DISPLAY them in a form or report. It makes no difference to the actual stored value anyway, so you're only increasing internal complexity by applying a Display Format elsewhere.

StrongFormatSQL StringFormatSQL is a function that takes parameters -- here that would be the ProductID and StockTakeDate -- and returns a properly crafted SQL String.

Note that this includes another function called ToAccessDate(dtStockTakeDate). It was added to Developer edition specifically to return a valid date format that Access can use because there are different standards in different locations. I.e. in the US, we use "MM/DD/YYYY", whereas in Europe and other locations, it is "DD/MM/YYYY".
If you've already flipped that dtStockTakeDate to still another format (e.g. Medium Date) it's quite likely that does not handle it properly.

My general advice is to leave the formatting off of date fields until you get to the form or report. If you do need to do it, though, probably the safest approach is yyyymmdd, which is unambiguous.

Whew. I think I made more editty fidgets in that reply than anything before. I bet there's still an error somewhere in it too.

 

[jdraw]

George:

Note that this includes another function called ToAccessDate(dtStockTakeDate). It was added to Developer edition specifically to return a valid date format that Access can use because there are different standards in different locations. I.e. in the US, we use "MM/DD/YYYY", whereas in Europe and other locations, it is "DD/MM/YYYY".

My current NW2Dev doesn't have that function, and I just downloaded the NW2Developer template again and it doesn't have that function. I am running Access 365 Version 2310 - Build 16.0.16924.20150 64-bit
Can you point to the NW2Dev file with the latest updates?
Thanks.

 

[cheekybuddha]

I'm encountering a 'Invalid use of Null' error (Error 94) that point to the following line in the ProductLastStockTakeQuantity Function in Module modInventory.

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

My best guess is that the error is due to dtStockTakeDate in an unexpected format. (Please correct me if I'm wrong).

My guess is that the DLookup() is not finding a matching record and that ProductLastStockTakeQuantity is declared as an Integer or Long.

Try declaring it as variant, and then test whether it is Null after the DLookup(), and handle accordingly.

That will avoid the error.

If not, then dtStockTakeDate might be Null

 

[cheekybuddha]

George:



My current NW2Dev doesn't have that function, and I just downloaded the NW2Developer template again and it doesn't have that function. I am running Access 365 Version 2310 - Build 16.0.16924.20150 64-bit
Can you point to the NW2Dev file with the latest updates?
Thanks.

I don't have Northwind, but I asked another member here to post the function in question:
See here

 

[jdraw]

Thanks David. My concern is that there are/may be various versions of NW2Developer. Since the function "ToAccessDate" in the OP's post and George confirmed the function had been added, and yet the current NW2Dev template doesn't have that function.
Perhaps there is an up to date NW2Developer template with all post-release changes.

 

[cheekybuddha]

Perhaps there is an up to date NW2Developer template with all post-release changes.

I'm sure there is, but I'm afraid I can't help - despite my curiosity to see NW2, it's not enough to make me invest in upgrading from my old copy of Acc2k7.

 

[alan2013]

Thanks to everyone who's made suggestions so far. I'm working through them, continuing to try to get the ProductLastStockTakeQuantity Function to work. It seems that I also have some issue with Function ProductLastStockTakeDate (and of course the ProductLastStockTakeQuantity Function depends on the ProductLastStockTakeDate Function).

I'm getting Error 91 (Object variable or With block variable not set) on Line 50 of this :

Public Function ProductLastStockTakeDate(ByVal lngProductID As Long) As Date
10        On Error GoTo Err_Handler

          Dim sql             As String
          Dim rsStockTake     As DAO.Recordset

20        ProductLastStockTakeDate = g_dtCompanyInception

30        If lngProductID = 0 Then GoTo Exit_Handler

40        sql = StringFormatSQL("select * from tblStockTakes where intProductID_PK = {0} order by dtStockTakeDate desc;", lngProductID)

50        Set rsStockTake = g_dbApp.OpenRecordset(sql, dbOpenDynaset)

60        If rsStockTake.RecordCount = 0 Then
              'No stock take was ever done for this Product.
70            With rsStockTake
80                .AddNew
90                !dtStockTakeDate = Nz(DLookup("AddedOn", "tblProducts", "intProductID_PK = " & lngProductID), Now())
100              !intProductID_PK = lngProductID
110              !intQuantityOnHand = 0
120              .Update
130              .Move 0, .LastModified    'Move to the record just added, so we can read from it.
140           End With
150       End If

160       ProductLastStockTakeDate = rsStockTake!dtStockTakeDate
170       rsStockTake.Close
180       Set rsStockTake = Nothing

Exit_Handler:
190       Exit Function

Err_Handler:
200       clsErrorHandler.HandleError "modInventory", "ProductLastStockTakeDate"
210       Resume Exit_Handler
End Function

 

[cheekybuddha]

Just as a test, try changing line 50 to:

50        Set rsStockTake = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

 

[alan2013]

Just as a test, try changing line 50 to:

50        Set rsStockTake = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

Thanks. I think I see why you made that suggestion. Trying that, I get a 'Too few parameters. Expected 1' error.

 

[cheekybuddha]

OK, so it seems that you had lost your g_dbApp object, perhaps due to an unhandled error or perhaps it was never set in the first place. (I'm guessing it's a global object variable going by its name)

Trying that, I get a 'Too few parameters. Expected 1' error.

This is due to a mistake in your SQL - either you have mis-spelt intProductID_PK or dtStockTakeDate, or one of them is not actually a field in tblStockTakes

 

[alan2013]

OK, so it seems that you had lost your g_dbApp object, perhaps due to an unhandled error or perhaps it was never set in the first place. (I'm guessing it's a global object variable going by its name)


This is due to a mistake in your SQL - either you have mis-spelt intProductID_PK or dtStockTakeDate, or one of them is not actually a field in tblStockTakes

omg, I've realized I made a 'school-boy' error.

I replaced

sql = StringFormatSQL("select * from tblStockTakes where intProductID_PK = {0} order by dtStockTakeDate desc;", lngProductID)

with

sql = StringFormatSQL("select * from tblStockTakes where intProductID_FK = {0} order by dtStockTakeDate desc;", lngProductID)

And both Functions appear (TBC !) to be working much better now.

Thank you.

 

[cheekybuddha]

omg, I've realized I made a 'school-boy' error.

I replaced

sql = StringFormatSQL("select * from tblStockTakes where intProductID_PK = {0} order by dtStockTakeDate desc;", lngProductID)

with

sql = StringFormatSQL("select * from tblStockTakes where intProductID_FK = {0} order by dtStockTakeDate desc;", lngProductID)

And both Functions appear (TBC !) to be working much better now.

Thank you.

 

[GPGeorge]

George:



My current NW2Dev doesn't have that function, and I just downloaded the NW2Developer template again and it doesn't have that function. I am running Access 365 Version 2310 - Build 16.0.16924.20150 64-bit
Can you point to the NW2Dev file with the latest updates?
Thanks.

We added it to the template after getting feedback from some non-US developers. I'm pretty sure it was at least September, or maybe October when we released that update. There has been one more since then as well. Can you go to https://support.microsoft.com/en-us...emplates-e14f25e4-78b6-41de-8278-1afcfc91a9cb and download a fresh copy?

Here's the function in question:

'PURPOSE:
'   Convert the date to US 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

 

[Gasman]

Thanks to everyone who's made suggestions so far. I'm still trying to get the ProductLastStockTakeQuantity Function working.....but it seems I'm also having problems with Function ProductLastStockTakeDate in Module modInventory (in that it's currently not finding the latest stock-take date, and is reverting to 'inception' date instead). And of course, function ProductLastStockTakeQuantity depends on function ProductLastStockTakeDate finding the last stock-take date.

We added it to the template after getting feedback from some non-US developers. I'm pretty sure it was at least September, or maybe October when we released that update. There has been one more since then as well. Can you go to https://support.microsoft.com/en-us...emplates-e14f25e4-78b6-41de-8278-1afcfc91a9cb and download a fresh copy?

Here's the function in question:

'PURPOSE:
'   Convert the date to US 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

I would not call that USA format?