473,890 Members | 1,381 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Seth Schrock
2,965 Recognized Expert Specialist
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.
Jan 4 '13 #1
24 11909
32,584 Recognized Expert Moderator MVP
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.
Jan 4 '13 #2
Seth Schrock
2,965 Recognized Expert Specialist
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 4 '13 #3
Seth Schrock
2,965 Recognized Expert Specialist
I just got a sudden inspiration. I moved the CCur to outside the Nz() function instead of inside it. So now my query reads:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 4 '13 #4
32,584 Recognized Expert Moderator MVP

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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 5 '13 #5
Seth Schrock
2,965 Recognized Expert Specialist
As you guessed, it just displays 0. I can't figure out why the Currency format on the textbox doesn't convert it.
Jan 5 '13 #6
Stewart Ross
2,545 Recognized Expert Moderator Specialist
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).

Jan 5 '13 #7
Seth Schrock
2,965 Recognized Expert Specialist
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".
Jan 5 '13 #8
32,584 Recognized Expert Moderator MVP
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).
Jan 6 '13 #9
Seth Schrock
2,965 Recognized Expert Specialist
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.
Jan 6 '13 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: aa | last post by:
I have beem recommended to use the Nz() function in the ORDER BY part of an Access2000 stored query. The query runs correcly from within Access2000 Yet when ran from ASP, it causes an error: Microsoft JET Database Engine (0x80040E14) Undefined function 'Nz' in expression. Where should I define this function?
by: Dan | last post by:
Is it possible to have a javascript function return a value to set a webpage elements property? Ie. <td bgcolor=get_background_colour()> Thanks for any help, Dan.
by: Pink Panther | last post by:
Using the following SQL can the results be explained? Using A97 (with the SP2 for Jet 3.5) or A2002 CREATE TABLE Test (PK Number CONSTRAINT PK_TEST PRIMARY KEY, ParentID Number, Child Number); INSERT INTO Test VALUES(1,1,1); INSERT INTO Test VALUES(2,1,2);
by: James Bremner | last post by:
I am having trouble with adding data from two tables. The tables in question are a stock in and a stock out table. For various reasons it is not suitable to join them into one transaction table. Obviously, what I want to do is subtract the "out" from the "in" and get a value for how many of each item is in our cupboard. The problem arises when nothing of particular product has been removed, meaning there is no entry in the "out" table. I...
by: MX1 | last post by:
Help. I have a complex Access database that I've tied into using ASP. Things work great, except I can't seem to have queries that originate in ASP use the NZ function. Need it in case there are nulls. It would be a nightmare to rewrite the queries. Anyone know of an alternate way to connect to the database so my NZ calcs would be supported? Many thanks!
by: pelcovits | last post by:
How do I get Access to put a blank in a report column (but not eliminate the record completely), when the field in question is currency format, and the value is zero? My understanding is that null is not permitted as a currency value. thanks
by: MLH | last post by:
For example: Nz(,0) returns "300" if the value in field is 300 (currency data type) and "0" if the value is zero or null. I get strings in the query output - they are all left aligned and I cannot add them without first converting them to values. What might be causing this?
by: Paul M | last post by:
Hi, Is there an equivalent to the old NZ function from VB6? Cheers...Paul
by: Paul | last post by:
Anyone have code that emulates the Nz function in Microsoft Access? In Access it is: Nz(Value as variant, Optional ValueIfNull as Variant) as Variant
by: Will | last post by:
Hi, I need to handle blank values in a query calculation. I have 636 records at the moment but when I sum over these records the blank fields are not returned. I have looked around here and on Access help and the Nz function seems to be the way forward. I am summing over 6 fields, all or none of which may contain values. The expression syntax I have used is as follows:
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.