By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,603 Members | 1,617 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,603 IT Pros & Developers. It's quick & easy.

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

P: 66
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.

Expand|Select|Wrap|Line Numbers
  1. MsgBox Form_frmDBC.txtProdDt.Value
  2. MsgBox Form_frmDBC.Logidas.Value
  3.  
  4. Debug.Print DLookup("[Overall]", "QryProPerc")
  5. MsgBox DLookup("[Overall]", "QryProPerc", "[ProcessorID] = '" & Form_frmDBC.Logidas.Value & "'")
My Query's SQL view is as below.

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![frmDBC]![Logidas] Text ( 255 ), [Forms]![frmDBC]![txtProdDt] DateTime;
  2. 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
  3. FROM QryPro_Npro
  4. 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]
  5. HAVING (((QryPro_Npro.ProcessorID) Like [Forms]![frmDBC]![Logidas]) AND ((QryPro_Npro.ProductionDt) Like [Forms]![frmDBC]![txtProdDt]));
Jan 26 '15 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public gstrMyText as String
  5.  
  6. Public Function GetMyText()
  7.     GetMyText = gstrMyText
  8. End Function
Then, in your forms:

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtMyText_AfterUpdate()
  2.     gstrMyText = Me.txtMyText
  3. End Sub
In your query:

Expand|Select|Wrap|Line Numbers
  1. SELECT GetMyText() AS MyTextValue ...
This is where a TempVars might be more useful.

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtMyText_AfterUpdate()
  2.     TempVars.Add "MyTextString", Me.txtMyText.Value
  3. End Sub
and then in your query:

Expand|Select|Wrap|Line Numbers
  1. 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!

Share this Question
Share on Google+
9 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,483
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!
Jan 26 '15 #2

P: 66
Thanks Twinnyfo, let me try with and get back.
Jan 26 '15 #3

P: 66
Hi Twinnyfo, I have quick question here, Please confirm how to call the variable in Query?
Jan 26 '15 #4

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jan 26 '15 #5

twinnyfo
Expert Mod 2.5K+
P: 3,483
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public gstrMyText as String
  5.  
  6. Public Function GetMyText()
  7.     GetMyText = gstrMyText
  8. End Function
Then, in your forms:

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtMyText_AfterUpdate()
  2.     gstrMyText = Me.txtMyText
  3. End Sub
In your query:

Expand|Select|Wrap|Line Numbers
  1. SELECT GetMyText() AS MyTextValue ...
This is where a TempVars might be more useful.

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtMyText_AfterUpdate()
  2.     TempVars.Add "MyTextString", Me.txtMyText.Value
  3. End Sub
and then in your query:

Expand|Select|Wrap|Line Numbers
  1. 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!
Jan 26 '15 #6

P: 66
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.
Jan 26 '15 #7

P: 66
hi Twinnyfo, I will try this one
Jan 26 '15 #8

P: 66
Thanks Twinnyfo, I tried declaring the global variable and it worked.. thanks Bro... :-)
Jan 26 '15 #9

twinnyfo
Expert Mod 2.5K+
P: 3,483
Glad I could be of some help!
Jan 27 '15 #10

Post your reply

Sign in to post your reply or Sign up for a free account.