Problems getting the currency sign to display using the Nz

[eagles396]

Hi,

I have a problem displaying the £ sign in my query. Instead of the query displaying £448.87 it displays 448.87.

You can see from the below image that the value of AllCost is £448.87 and I don't understand why its displaying 448.87 when I run the other query called All Cost 2. The Nz function is used to display £0.00 if the AllCost field is null.

The below image is when I run the "All Cost 2" query

Thanks!
Chris

 

[gemma-the-husky]

i think once you nz it, it becomes a number, rather than a currency

however when you display it in a text box on a form, you can set the format back to currency there.

note that because you have formatted the expn with an nz, a text box based on this won't be editable

 

[Brianwarnock]

Gemma , I think Nz turns all fields to Text, hence the left justification.

Brian

 

[Road_Warrior]

IIf(IsNull([AllCost]),0,[Allcost])

 

[gemma-the-husky]

brian

surely nz(numbervariable,0) turns data into numeric not text?

not sure though

 

[Brianwarnock]

My experience is that it returns a text formatted field, it caused problems for a poster who tried to do arithmetic on his, I always use Val(Nz(numericfield,0)).

I know this seems daft and is not mentioned anywhere, but I can only go by own, limited, experience.

Brian

 

[boblarson]

I believe Brianwarnock is correct.

 

[gemma-the-husky]

just checked Access help

says nz will return zero or a zero length string, depending on the input type

i certainly have had no problems with either

if nz(number,0) = 0
and if nz(string,vbnullstring)=vbnullstring depending on the data type.

 

[grnzbra]

So how about Format(NZ(AllCost),0),"$0.00")

Sorry, don't have symbol for Pounds here.

 

[Road_Warrior]

The below code really works, try it

IIf(IsNull([AllCost]),0,[Allcost])
This leaves the field as currency

 

[Brianwarnock]

Road_Warrior please don't get upset we know your formula works , its just that we are now discussing Nz

Gemma I agree with all that your last post says but honestly afield that has Nz applied to it is text. I have attached a simple DB

Query1 shows an IIF working which is what your example was.

Query2 shows what happens if you try to use + on fields created using NZ (t2), but notice the arithmetic works if Nz is applied to the fields at that time.(t1)

Query3 shows the use of Val to overcome the problem.

The conclusion i draw is that Nz should be used carefully, ie check what you are doing and understand the problems

Brian

 

[Road_Warrior]

Not upset in the least, just wanted to be sure the original poster saw a work around. I agree with your conclusions and usually only use NZ with number fields and go with If Not Is Null for other data types for that very reason.