[AccessD] Nz and Val

Hi Tony

You didn't mention a combobox. Such one always returns a string, thus your unbound textbox will hold a string as well. If you wish to perform math on the values - you must convert the string to a numeric before doing so. 
This will do as Nz by default returns a zero length string for Null and Val converts a zero length string to zero (0):

If Me!InvoiceAmount + Val(Nz(Me!TotalInvoice)) > Val(Nz(Me!Budget)) Then
 ..... display Yes/No message
End If

/gustav


>>> iggy at nanaimo.ark.com 04-06-2011 16:49 >>>
Hey All
Spent the last couple of hours testing the problem. I  very rarely use Nz.
This doesn't work

A)If Me!InvoiceAmount + Nz(Me!TotalInvoice, 0) > Nz(Me!Budget, 0) Then
 ..... display Yes/No message
End If

But this does
B) If Me!InvoiceAmount + val(Nz(Me!TotalInvoice, 0)) > val(Nz(Me!Budget, 0)) Then
 ..... display Yes/No message
End If

And it (the Duh the light finally came on) makes sense TotalInvoice and 
Budget are numbers pulled from a ComboBox. If they are null display a 
zero, otherwise display the number which is text not numeric.

Just curious again, what I don't understand is if I do
1. Msgbox Me!InvoiceAmount + Nz(Me!TotalInvoice,0) it displays 1000
2. Msgbox Nz(Me!TotalInvoice,0)+ Nz(Me!TotalInvoice,0) it displays 800900
With 1. The total is correct but I would have thought I would have 
gotten an error message instead when trying to add a value to text??  
Which is why A) above doesn't work.

Thanks Again
 
Gustav Brock wrote:

>Hi Tony
>
>Let Nz return a numeric:
>
>If Me!InvoiceAmount + Nz(Me!TotalInvoice, 0) > Nz(Me!Budget, 0) Then
> ..... display Yes/No message
>End If
>
>/gustav
>
>
>  
>
>>>>iggy at nanaimo.ark.com 03-06-2011 18:23 >>>
>>>>        
>>>>
>Hey All
>I have four controls on a form.
>JobCode - a combo box
>Budget -an unbound text box control source = 
>format(me!JobCode.column(2),"#.###")
>TotalInvoices - an unbound text box control source = 
>format(me!JobCode.column(4),"#.###.00")
>InvoiceAmount - a bound text box
>What I am doing (On BeforeUpdate) is letting the user know if the 
>current invoice amount + the total invoice amount may exceed the budget.
>Just curious, can someone explain to me why this doesn't work
>me!InvoiceAmount=$200
>me!TotalInvoice=$800
>me!Budget=$900
>If me!InvoiceAmount + nz(me!TotalInvoice)> nz(me!Budget) then
> ..... display Yes/No message
>endif
>When I check, the values come out to 200>900
>But this does work
>If me!InvoiceAmount + val(nz(me!JobCode.column(4))> nz(me!Budget) then
> ..... display Yes/No message
>endif
>When I check, the values come out to1000>900