Contatenate and null

[FrankMars]

I'm a beginner with Access. I have contatenated an equal sign (=) to a field in a query expression, getting a desired result such as "= $50.33". How would I prevent the = sign from showing in null fields?
Thank You in advance.

 

[MajP]

string & null = string
string + null = null

so you can do something like
"=" + [fieldName]
instead of
"=" & [fieldName]

 

[FrankMars]

Thanks MajP, unfortunately its not working for me. My expression is RateCalc: " = " & [Price]/[SF]. When I replace the & with + I see the null fields blanking out but get a #Error in the other fields in the column.

 

[MajP]

Sorry that only works with strings and your value is a currency. This would work
ratecalc: IIf(IsNull([Price]),Null,"= " & [Price])

 

[PHV]

What about this ?

RateCalc: IIf(Price Is Null OR Nz(SF,0)=0,""," = " & Price/SF)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[FrankMars]

MajP- The code worked fine on the price portion of the formula, the fields with content have the = sign and the fields w/o content have no = sign. But when I divide by the SF field I get the #Error.

PHV- The code worked fine. The result numbers have 13 digits after the decimal. How would I format them with a $ sign and 2 digits after decimal?

 

[MajP]

If this is in a query, in design view you can right click on the field and select properties. You can select currency from the Format pulldown.

 

[MajP]

oops, strike two. I forgot about the "=" sign so you will have to use a format function.

 

[MajP]

Try
RateCalc: IIf([Price] Is Null Or Nz([SF],0)=0,"",Format([Price]/[SF],"= $#.##"))

 

[FrankMars]

Thanks MajP, that works good. We've got =,$,digits,and blank null fields. The last challenge for this column is the zero values, which are now reading "= $." Would there be a way for them to be blank?

 

[MajP]

Try
IIf(Nz([Price],0)=0 Or Nz([SF],0)=0,"",Format([Price]/[SF],"= $#.##"))

 

[FrankMars]

That works good MajP, thanks!