access 2000 conversion format function

[rjmdt]

We recently converted from access 97 to access 2000. A number of queries and reports used a format function. I've attached an example. Can someone explain why the format function doesn't work and the appropriate syntax I should use. Thanks in advance.


=IIf([batchsect]="J","",IIf([LGN]="","",IIf([LGN]="GRAMS",Format([WTQTYREQ],"0.000") & " LBS","")))

 

[dhookom]

Have you attempted to compile your code? There is a brief description of this at

http://www.access.hookom.net/Articles.htm.

You might need to fix your references. Check Doug Steele's page at

http://www.accessmvp.com/djsteele/AccessReferenceErrors.html.

I hope you realize that if a field is blank, it probably is Null which is not the same as "".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Golom]

What's happening?

Are you getting an error?

No error but invalid results?

The Format syntax looks OK to me.

You might try

=IIf([batchsect]="J" OR [LGN]<>"GRAMS", "", Format([COLOR=red]NZ([/color][WTQTYREQ][COLOR=red])[/color],"0.000") & " LBS")