473,395 Members | 2,713 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

Where to put"nz" in a formula in an Access query?

Need help again,sorry

B: IIf([Miles]>150,130*3.8,IIf([Miles]>19.9 And [Miles]<150.1,([Miles]-20)*3.8))

How do I put in this "if null put me a 0 in" ?

My other sums don't work if I don't put a zero in, sometimes I could scream !

Regards, Teresa
Jan 23 '11 #1

✓ answered by NeoPa

Teresa, this is good thinking, but on balance for this formula I'd go with ADezii's idea of using IsNull() just the once, rather than Nz() three times. You'll find the proposed solutions in the other thread - Excel Formula into an Access Query.

4 1740
TheSmileyCoder
2,322 Expert Mod 2GB
I don't mean to sound rude but have you tried the helpfile on the nz function?

Anyways since [Miles]is the only thing that can be null, thats the part you want to check for. So around the [Miles] you could put:
Expand|Select|Wrap|Line Numbers
  1. Nz([Miles],0)
Jan 23 '11 #2
Thanks I will give it a go.

I did try the Microsoft help file with nz, but the explanation went way over my head !
Jan 23 '11 #3
No, couldn't get that to work.

If it was a simply Nz([Miles], 0) I could do, but there are to many ( [ for me to work out where the nz's go !

Help !
Jan 23 '11 #4
NeoPa
32,556 Expert Mod 16PB
Teresa, this is good thinking, but on balance for this formula I'd go with ADezii's idea of using IsNull() just the once, rather than Nz() three times. You'll find the proposed solutions in the other thread - Excel Formula into an Access Query.
Jan 24 '11 #5

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

Similar topics

3
by: Scott | last post by:
I have a database with an input form. The field "DailyID" on the form is to increment automatically with each new entry, then reset itself back to "1" at midnight and/or the change of date. ...
5
by: Larry R Harrison Jr | last post by:
I use the Lebans calendar control and love it; the only thing is that it commonly starts out at the year 1899 rather than the current year. Is there a way to specifically tell it to start at the...
6
by: Clément Collin | last post by:
I working on a GIS project, with Access link which just need a little routine in VBA, but I haven't knowledges in VBA language. It's very simple, and it looks like that in a TPascal way : .......
7
by: Lyn | last post by:
Hi and Season's Greetings to all. I have a question regarding the use of a qualifier word "Global". I cannot find any reference to this in Access help, nor in books or on the Internet. "Global"...
2
by: JC | last post by:
I am trying to conditionally print a field on a report based on the value of another field in the query that the report is based on. In the Control Source for SerialB I have typed: = IIF(nz(QtyB)...
7
by: Drum2001 | last post by:
I have created a database to track employee time. Within that, I have also created a report off the following query: SELECT ALLTasksFilter.ProjectName, Sum(.NumberOfCompletions) AS...
7
by: PW | last post by:
Hi, I have a form with unbound fields on it. The user selects a record from a recordset and I populate the unbound fields. When I try to change the unbound quantity text box, Access 2003 tells...
10
ChaseCox
by: ChaseCox | last post by:
I have a Database Called FalconAnalysis.mdb. The table I am wanting to use is called Generic Material. The Query I want to pass the value into is called Material Query. The field in the query is...
3
by: eskelies | last post by:
Good day all...I have a combo box that selects numbers (ie. 1,2,3, etc.). What I am looking for is the combo box to have an "ALL" selection. Right now this is what I have for my SQL code: SELECT...
4
by: gazza10001 | last post by:
Hi i hope you can help my company uses access and has modified for its needs usually what happens is you serach for the invoice by its number and then it brings all the information up such as...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
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,...
0
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...
0
Oralloy
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,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
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...

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.