Null value to $0.00 value

[zacharyjunior]

I have a query that returns all customer concerns that have cost the company money by either shipping free parts or paying an invoice. The query returns all data and some fields are blank. For example, if I ship parts only the invoice data is blank as vice versa. I want the null value to show $0.00 in both the query and the report. Where and how do I do this.

I tries to do it under the field in the query under crieteria. This is what I had.

Nz([PartsCost],0)

Thanks for your help.

 

[David R]

I think if I understand you correctly, you are almost right. You want to place the Nz() statement in the 'source' line (1st line) as a new calculated field, not in criteria. Then your form and report should both show correctly.

If I am mistaken on what you want, just post back.

HTH,
David R

 

[zacharyjunior]

Ok It worked. Thanks. The only probelm is that it changes the format. The fields use to be currency but now they are just numbers.

Any thoughts?

 

[Pat Hartman]

Format(Nz(YourField,0),"Currency")

 

[zacharyjunior]

I put the following command in line one of the field (in the design view of the query) as you said

Your Statement:
Format(Nz(YourField,0),"Currency")

My Statement:
Format(Nz(Parts,0)"Currency")

This is what it changes to:
Expr1: Format(Nz([Parts],0),[Currency])

When I run the querry it is asking for me to enter criterial for currency.

I must be an idiot. I do not understand whats going on here.

Thanks

 

[David R]

Strange. I just copied what you had into my database and it came out as currency. No parameters.

Look for a typo in the word currency. If it is surrounding it in brackets, that is my first guess. "Currency" is a pre-defined number format.

Here's what I used, just to be absolutely sure:

EventIDS: Format(Nz([Devices].[EventID],0),"Currency")

I had to put the table name in because I had the field EventID in two linked tables.

David R

 

[SteveA]

The entry you keyed in was:

Format(Nz(Parts,0)"Currency")

The problem is that you are missing a , before "Currency"). The following should work:

Format(Nz(Parts,0),"Currency")

HTH
SteveA