Invalid Use of Null (1 Viewer)

Newbie8

Banned
Local time
Today, 14:03
Joined
Jun 29, 2019
Messages
30
I am receiving invalid use of null for my vba code

Me.B3 = IIf(Nz([B1], 0) = 0 Or Nz([B2], 0) = 0, Null, CLng(((([txtAmount] * 43) / [B1]) / [B2]) - (Nz([A4], 0) + Nz([A5], 0))))
 

Ranman256

Well-known member
Local time
Today, 17:03
Joined
Apr 9, 2015
Messages
4,339
Instead of making long hairy functions,
Make a query that corrects all nulls,
Select nz(field1) as A, nz(field2) as B from table

Then use that query to do math:
A+B/C
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:03
Joined
Feb 19, 2013
Messages
16,553
the whole of the iif statement is evaluated before returning a value so your clng function is generating the error

you need to use if....then...end

alternatively you could wrap the nz function around the B1 and B2 values as you have for A4 and A5
 

bdra2778

Registered User.
Local time
Today, 14:03
Joined
Feb 14, 2019
Messages
34
I am receiving invalid use of null for my vba code

Me.B3 = IIf(Nz([B1], 0) = 0 Or Nz([B2], 0) = 0, Null, CLng(((([txtAmount] * 43) / [B1]) / [B2]) - (Nz([A4], 0) + Nz([A5], 0))))

What type of data is "B3", if is txt, instead of Null put "" or vbNullString.

Variables defined as Integer o long cannot be Null in VBA, try to define the variable as Variant data type.
 
Last edited:

Newbie8

Banned
Local time
Today, 14:03
Joined
Jun 29, 2019
Messages
30
i am receiving division by zero Me.B3 = Nz([txtAmount] * 43560) / Nz([B1]) / Nz([B2]) - Nz([A4]) + Nz([A5]).
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
i am receiving division by zero Me.B3 = Nz([txtAmount] * 43560) / Nz([B1]) / Nz([B2]) - Nz([A4]) + Nz([A5]).
Hi. If it's not one thing, it's another, isn't it. How about trying this out?
Code:
Me.B3 = Nz([txtAmount]) * 43560 / IIf(Nz([B1],0)=0,1,Nz([B1])) / IIf(Nz([B2],0)=0,1,Nz([B2])) - Nz([A4]) + Nz([A5])
Just a guess...


PS. Or maybe even...
Code:
Me.B3 = IIf(Nz([B1],0)=0 OR Nz([B2],0)=0,Nz([txtAmount]) * 43560 / IIf(Nz([B1],0)=0,1,Nz([B1])) / IIf(Nz([B2],0)=0,1,Nz([B2])) - Nz([A4]) + Nz([A5]))
(untested)
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
I am receiving Division By Zero
Hi. How about breaking it down, so you can see where the problem is. For example, start out with this:
Code:
IIf(Nz([B1],0)=0,0,Nz(Me.txtAmount,0)*43560/IIf(Nz([B1,0)=0,1,Nz([B1],0)))
If that works, you can start adding B2, and so on...
 

Newbie8

Banned
Local time
Today, 14:03
Joined
Jun 29, 2019
Messages
30
That code works perfectly in the control. I have several columns 1,2,3,4,5,6,7. The primary columns you enter values are 4 and 5. Let's say you enter all the values and realize you enter the wrong number in one of the textboxes in column 4. You edit the value with the correct number, but the values in column 3 rows need to refresh to show the correct number. The figures below that row needs to update. The original code did work, but it already had values in textbox. When i cleared my entry by pressing backspace to reset to 0, then invalid use of null, and now today it is showing Division by Zero.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
That code works perfectly in the control. I have several columns 1,2,3,4,5,6,7. The primary column you enter values are 4 and 5. Let's you enter all the values and realized I enter the wrong number in one of the textboxes in column 4, and edit the value to the column. The figures below that row needs to update. The original code did work, but it already had values in textbox. When i cleared my entry by pressing backspace to reset to 0, then invalid use of null, and now today it is showing Division by Zero.
Okay, like I was saying earlier, you slowly build your expression, making sure each piece works before moving on. So, for example, if the above shorter expression works without errors, you then try to add the [B2] piece to it and fix any errors before adding [A4] and then [A5].
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:03
Joined
May 7, 2009
Messages
19,169
don't use "OR", use And:
Code:
IIf(Nz([B1], 0) = 0 [COLOR="blue"]And[/COLOR] Nz([B2], 0) = 0, Null, …
 

Newbie8

Banned
Local time
Today, 14:03
Joined
Jun 29, 2019
Messages
30
Does anyone have Skype to screen share? I would like for someone to help me with fixing. I am welling to pay.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
Does anyone have Skype to screen share? I would like for someone to help me with fixing. I am welling to pay.

I could try to help you via Skype. What is your time zone?
 

Newbie8

Banned
Local time
Today, 14:03
Joined
Jun 29, 2019
Messages
30
Maybe someone can guide me in the right direction. When I first start a new project, the textboxes are set to show 0. The error is occurring because there are no integers in the textboxes ahead of them. When there are integers in place the code works like a charm. What is best solution to get this fixed?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:03
Joined
May 21, 2018
Messages
8,463
Can you upload you DB? This is probably really easy to do if we can see it.
 

Users who are viewing this thread

Top Bottom