Invalid Use of Null

[Newbie8]

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]

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]

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]

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.

 

[Newbie8]

i am receiving division by zero Me.B3 = Nz([txtAmount] * 43560) / Nz([B1]) / Nz([B2]) - Nz([A4]) + Nz([A5]).

 

[theDBguy]

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?

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...

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)

 

[Newbie8]

I am receiving Division By Zero

 

[theDBguy]

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:

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]

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]

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]

don't use "OR", use And:

IIf(Nz([B1], 0) = 0 [COLOR="blue"]And[/COLOR] Nz([B2], 0) = 0, Null, …

 

[Newbie8]

Does anyone have Skype to screen share? I would like for someone to help me with fixing. I am welling to pay.

 

[theDBguy]

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]

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?

 

[Newbie8]

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

Thank you so much, I am west cost time

 

[theDBguy]

Thank you so much, I am west cost time

That's perfect! I'm available now. You can search for me in Skype (try theDBguy).

 

[MajP]

Can you upload you DB? This is probably really easy to do if we can see it.

 

[Newbie8]

Resolved, I used On Error Resume Next

 

[Newbie8]

sent you a skype im