MS Access query shows "INVALID USE OF NULL" error when I call using VBA

[johny6685]

MS Access query shows "INVALID USE OF NULL" error when I call using VBA, but when I run directly it works.

I tried calling the parameters value i.e. form field value before calling the query, it shows the correct data, but not sure why the error pops up. Please help me to debug.

MsgBox Form_frmDBC.txtProdDt.Value
MsgBox Form_frmDBC.Logidas.Value
 
Debug.Print DLookup("[Overall]", "QryProPerc")
MsgBox DLookup("[Overall]", "QryProPerc", "[ProcessorID] = '" & Form_frmDBC.Logidas.Value & "'")

My Query's SQL view is as below.

PARAMETERS [Forms]![frmDBC]![Logidas] Text ( 255 ), [Forms]![frmDBC]![txtProdDt] DateTime;
SELECT QryPro_Npro.ProcessorID, QryPro_Npro.ProductionDt, TimeValue(Format(([QryPro_Npro].[Productive]),"hh:nn:ss"))/TimeValue("08:00:00") AS Productive, TimeValue(Format(([QryPro_Npro].[Non-Productive]),"hh:nn:ss"))/TimeValue("08:00:00") AS NonProductive, TimeValue(Format(([QryPro_Npro].[Overall Time Spent]),"hh:nn:ss"))/TimeValue("08:00:00") AS Overall, 1-[Productive] AS ProdBalTime, 1-[QryPro_Npro].[Non-Productive] AS NPBalTime, 1-[QryPro_Npro].[Overall Time Spent] AS OABalTime
FROM QryPro_Npro
GROUP BY QryPro_Npro.ProcessorID, QryPro_Npro.ProductionDt, TimeValue(Format(([QryPro_Npro].[Productive]),"hh:nn:ss"))/TimeValue("08:00:00"), TimeValue(Format(([QryPro_Npro].[Non-Productive]),"hh:nn:ss"))/TimeValue("08:00:00"), TimeValue(Format(([QryPro_Npro].[Overall Time Spent]),"hh:nn:ss"))/TimeValue("08:00:00"), 1-[Productive], 1-[QryPro_Npro].[Non-Productive], 1-[QryPro_Npro].[Overall Time Spent]
HAVING (((QryPro_Npro.ProcessorID) Like [Forms]![frmDBC]![Logidas]) AND ((QryPro_Npro.ProductionDt) Like [Forms]![frmDBC]![txtProdDt]));

[twinnyfo]

johny,

For whatever reason, Access is not a fan of using references to Forms when it calls Queries in VBA (I've run across your same error!).

One way to do it is to gather the values on the form using variable in your procedure first, then create the query using those variables. Another is to use global variables, which are assigned when the values on the form are updated. You could also make use of the TempVars variables, which have some usefulness in such situations.

Hope this hepps!

[johny6685]

Thanks Twinnyfo, let me try with and get back.

[johny6685]

Hi Twinnyfo, I have quick question here, Please confirm how to call the variable in Query?

[NeoPa]

Have you checked references in the SQL to the parameters? Is there any such reference which would fail if it were replaced by Null?

If so then that is an error and needs to be rectified. Failing isn't aberrant behaviour at all but exactly how it's supposed to work.

[twinnyfo]

If you have a Global Variable, the only way to use it in a Query is to also create a Public Function that calls it. Example:

Public Standalone Module:

Option Compare Database
Option Explicit
 
Public gstrMyText as String
 
Public Function GetMyText()
    GetMyText = gstrMyText
End Function

Then, in your forms:

Private Sub txtMyText_AfterUpdate()
    gstrMyText = Me.txtMyText
End Sub

In your query:

SELECT GetMyText() AS MyTextValue ...

This is where a TempVars might be more useful.

Private Sub txtMyText_AfterUpdate()
    TempVars.Add "MyTextString", Me.txtMyText.Value
End Sub

and then in your query:

SELECT TempVars("MyTextString") AS MyTextValue ...

You must be sure to use the .Value property of your Controls, as TempVars (for some reason) wants to assign the control itself to your variable if not.

Hope this hepps!

[johny6685]

Yes NeoPa, i check before the query executes the parameters does have the values but don't know how it gets nul value while executes.

[johny6685]

hi Twinnyfo, I will try this one

[johny6685]

Thanks Twinnyfo, I tried declaring the global variable and it worked.. thanks Bro... :-)

[twinnyfo]

Glad I could be of some help!