How do I make an Nz() function return a Currency value?

[Seth Schrock]

I have a report that I use as an invoice to charge our customers. Some of the charges are blank. I would like to be able to have it so that if the value is blank, it would put $0.00. I tried using the Nz() function (Nz(PerEntryChar ge, 0), but that just returns 0 even though the format for the control in the report is set to currency. I tried nesting the CCur() function inside the Nz() function like this: Nz(PerEntryChar ge, CCur(0)), but that still returned 0. I don't know what else to try.

I believe that I have run into a similar problem before, but I can't remember which database so I can find what I used.

[NeoPa]

Seth, what you say sounds unlikely, but you have not actually said what you are using precisely.

I would expect any zero value (Explicitly not a string of a zero) to be formatted by the control's format property. The fact that it appears not to be leads me to believe your explanation is faulty somewhere. As your explanation is not at all precise, it's hard to say exactly what may be happening.

Please try a specific scenario and report the full details of that for our consideration.

[Seth Schrock]

I'm not sure why I didn't tell you this before, I'm trying this in the query that is the record source for the report. The [PerEntryCharge] field is the cost per unit of our service. Depending on the agreement we have with our customer this might be $0.00 or it might be more (like $0.09 or something). In this case, the price is $0.00. The price is stored in the customer table. If the field is left blank in the table (explicitly a ZLS), then I want it to show on the report as $0.00. To get this, I tried adding the following field to the query:

Nz(tblCustomer.PerEntryCharge, 0) As EntryCharge

and changed the record source for the control on the report to this field. The control does have a format of Currency (I double checked to be sure), but it still just puts the 0 in the report.

I then thought that if I changed the value to a currency value in the query that the report would do the same. So I tried:

Nz(tblCustomer.PerEntryCharge, CCur(0)) AS EntryCharge

Both in the query and the report (I don't care about the query except that it might help determine what is happening) still produced a 0 instead of $0.00.

I hope this is clearer. My mind is going in circles trying to figure this out. I have re-read this post several times to make sure I have all the information that I can think of included, but if I have missed something please let me know and I will do my best to give a clear answer.

[Seth Schrock]

I just got a sudden inspiration. I moved the CCur to outside the Nz() function instead of inside it. So now my query reads:

CCur(Nz(tblCustomer.PerEntryCharge,0))

So far this seems to be working, but I will continue testing to make sure that it keeps working.

**I spoke too soon**
The query shows the $0.00 like I want it too but the report doesn't :( Back to square one.

[NeoPa]

Interesting.

I cannot see why a value of 0 is not displayed in the Currency formatted control as $0.00. Converting the result explicitly to a Currency value will do no harm, but nor should it help at all.

Try setting the query field to :

0 As EntryCharge

See what that displays.

Logically it should show as $0.00 from your explanation, but from the results you've reported so far, I expect it won't.

[Seth Schrock]

As you guessed, it just displays 0. I can't figure out why the Currency format on the textbox doesn't convert it.

[Stewart Ross]

Seth, you mention in post 3 that:

The price is stored in the customer table. If the field is left blank in the table (explicitly a ZLS), then I want it to show on the report as $0.00

Do you really mean that the blank value is stored as a zero-length string (which is what I take ZLS to mean)? If so, the underlying storage field cannot be of type currency (or any other numeric field). Text fields do not respond to the built-in formatting options available on text boxes, so it is very important that you clarify the data type involved.

I have tested a report based on a query converting true null currency values to currency values of £0.00 with no problems at all, so all I can think of is that, as mentioned, the data type of the field is text, or alternatively that the report textbox is using an expression involving Nz.

Please note that although Nz returns a variant type this is interpreted as text in a query (or a calculated field), so unless you do a typecast of Nz to a numeric or currency type you cannot apply numeric formatting to an Nz expression directly (hence the problem with your earlier use of Nz before you applied the CCur to it).

I should mention for completeness that Nz will not work with zero-length strings; a zero-length string is not a null value (null being the complete absence of any value set for a field).

-Stewart

[Seth Schrock]

I was wording it like NeoPa did in post 2. But no, the data type in the table is currency. I was just meaning that the field was blank, null, no characters, whatever the proper term is, but does not contain the character "0".

[NeoPa]

Seth:
I was wording it like NeoPa did in post 2.

I'm not sure I follow that. You seem to be inferring from my comment that if it's not a string of zero ("0") then it must be a ZLS. This is absolutely not my implication as a Currency field would never contain a string of any type. It would, and could, only contain a numeric value. Numeric values are not strings, even though they look that way when displayed.

If a Currency typed field shows with no data then it must contain a Null. Any numeric value would show at least one digit plus any formatting characters. To see nothing indicates the value must be Null. String fields are the only ones where an empty string (ZLS) and a Null are both possible (Hence the potential confusion).

This does seem to have reached a point where an attachment would prove helpful. I'd be happy to look at an attachment for you, but I warn you now, I'm almost certain that the results will prove that you have posted incorrect and misleading information somewhere along the line, as I believe what you've posted to be impossible. That said, I'm equally sure that anything posted was done in good faith, and as a result of confusion rather than laziness or malice.

I suspect that Stewart's idea of the control being linked to a formula rather than the field itself is where the problem lies. Probably in a query used as the RecordSource of the form.

When posting attachments please be sure to follow these instructions carefully - Attach Database (or other work).

[Seth Schrock]

In looking back I see where I miss read your wording. So I should have said explicitly a null value.

I will post my database on Monday when I get back to work (I don't have access to it while at home). What version of Access do you have so that I can post it in the proper format for you. I just also thought of trying it at home as I have recently had a database that didn't do as it was supposed to on my work computer but did on my home computer.

[NeoPa]

I usually use A2003, but I can handle later versions on A2010 on my new laptop. It's harder to get it onto there, and the file structure isn't set up yet to support all my Bytes work there, but it can be done if required.

Trying it on your home PC first is a good idea. That gives more information, which is never a problem :-)

[Seth Schrock]

Well I finally remembered to email the database to myself so that I could test it at home. Still didn't work.

Unfortunately, I can't save it as Access 2003 format :(

Instructions:
Open frmCustomer (my one sample record will come up)
Click on the Billing tab
Enter start date and end date to capture all of January '13
Select billing number 1 (only option)
Click Preview invoice
You will see a "Per File Charge" and a "Per Entry Charge" in the middle red line. There isn't anything under "Per Entry Charge" (because the field is null). If you change the data source to [PerEntryCharge1], a "0" will appear as the value. The data source for the report is qrySpecialCount WIIF. The report is rptInvoice. I have tried to arrange the query nicely, but it never saves the layout for some reason so my apologies for the big mess of SQL code in the query. I just don't know what to do to make it keep its layout.

[NeoPa]

It's so nice (and unfortunately so rare) to work with a member who can follow instructions properly and well. I'm not on my laptop ATM so can't check it out now, but I've downloaded it for checking on later. Watch this space.

[Seth Schrock]

I got it fixed. I just came to me to try the Format() function and it worked. So I placed the following in the query and then based the control on my report on the query (in other words, I didn't use any function in the report itself):

Nz(PerEntryCharge, Format(0, "Currency")) As PerEntryCharge1

Any ideas why Format(0, "Currency") works and CCur(0) doesn't? I did a Google search to see what the difference is between the two functions, but couldn't come up with anything and I don't know where to look it up in MSDN.

[Seth Schrock]

Unfortunately, using this combination of Nz() and Format() functions makes it so that if the value is not null, then it displays as just a number and not a currency value. I will continue to try the possible combinations and see if I can get one that works for both Null and non-Null values.