IIf help

  • Thread starter Thread starter JohnLute
  • Start date Start date
J

JohnLute

[WeightUOM] and [TotalWeightUOM] are text fields. I'm trying to do this:

TotalWeightUOM: IIf([WeightUOM] Is Not Null,[WeightUOM])+IIf([WeightUOM] Is
Null,[TotalWtUOM])

It returns a column with null values when what's expected is data.

When I remove the +IIf([WeightUOM] Is Null,[TotalWtUOM]) part then the first
part of the code works. Vice versa. I suspect I've got something wrong with
Is Not Null and Is Null...?

Thanks for your help!
 
HOW SIMPLE! Once again I find myself complicating the un-complicated!

Thanks a bunch, Dennis!

--
www.Marzetti.com


Dennis said:
TotalWeightUOM: IIf(IsNull([WeightUOM]),[TotalWtUOM],[WeightUOM])

JohnLute said:
[WeightUOM] and [TotalWeightUOM] are text fields. I'm trying to do this:

TotalWeightUOM: IIf([WeightUOM] Is Not Null,[WeightUOM])+IIf([WeightUOM] Is
Null,[TotalWtUOM])

It returns a column with null values when what's expected is data.

When I remove the +IIf([WeightUOM] Is Null,[TotalWtUOM]) part then the first
part of the code works. Vice versa. I suspect I've got something wrong with
Is Not Null and Is Null...?

Thanks for your help!
Click to expand...
Click to expand...
 
Hi, Dale.

How can I make Nz work for this scenario? Or can't it? I've tried this and
of course, it returns "Wrong number of arguments used." Apparently Nz only
works when a maximum of 2 fields are referenced...?

CSL: Nz([CGODL],[CNL],[PKMSOED],[PKMSOEL],[THED],[THEL])

Thanks!

--
www.Marzetti.com


Dale_Fye via AccessMonster.com said:
Or even simplier:

TotalWeightUOM: NZ([WeightUOM], [TotalWtUOM])

TotalWeightUOM: IIf(IsNull([WeightUOM]),[TotalWtUOM],[WeightUOM])
[WeightUOM] and [TotalWeightUOM] are text fields. I'm trying to do this:
Click to expand...
[quoted text clipped - 8 lines]
Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
You need to nest them in the order you wish them to be tested: inside to
out:
Start by writing them in order, leaving the second argument blank until
the final test:
Nz([CGODL],)
Nz([CNL],)
Nz([PKMSOEL],)
Nz([PKMSOED],)
Nz([THED],[THEL])

See? Each Nz returns a single value that can be used as the second
argument of the prior test.

Now, copy the final test - Nz([THED],[THEL]) - into the second argument
of the prior one:

Nz([PKMSOED],Nz([THED],[THEL]))

This now becomes the final test, so copy it into the prior one:
Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL])))

Repeat:
Nz([CNL],Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL]))))

And finally:
Nz([CGODL],Nz([CNL],Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL])))))

Just to be sure: count the parentheses ... the open parens should each
have a matching closing paren.

Ughhh ... but it works. Too bad the Jet team never implemented CASE or
COALESCE.


Hi, Dale.

How can I make Nz work for this scenario? Or can't it? I've tried
this and of course, it returns "Wrong number of arguments used."
Apparently Nz only works when a maximum of 2 fields are referenced...?

CSL: Nz([CGODL],[CNL],[PKMSOED],[PKMSOEL],[THED],[THEL])

Thanks!

--
www.Marzetti.com


Dale_Fye via AccessMonster.com said:
Or even simplier:

TotalWeightUOM: NZ([WeightUOM], [TotalWtUOM])

TotalWeightUOM: IIf(IsNull([WeightUOM]),[TotalWtUOM],[WeightUOM])

[WeightUOM] and [TotalWeightUOM] are text fields. I'm trying to do
this:

[quoted text clipped - 8 lines]

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
WHOA! Alrighty, then! Take THAT, Dale! <g>

For as "ugly" as this might be it's still way better than what I'm currently
doing! I have several other aliases that need this treatment which will
greatly help to reduce some very complex queries and reports.

I tried Help for Nz but it took me to an internet error page.

Thanks a bunch, Bob! That's great how you mapped it out - makes it really
easy to comprehend.

--
www.Marzetti.com


Bob Barrows said:
You need to nest them in the order you wish them to be tested: inside to
out:
Start by writing them in order, leaving the second argument blank until
the final test:
Nz([CGODL],)
Nz([CNL],)
Nz([PKMSOEL],)
Nz([PKMSOED],)
Nz([THED],[THEL])

See? Each Nz returns a single value that can be used as the second
argument of the prior test.

Now, copy the final test - Nz([THED],[THEL]) - into the second argument
of the prior one:

Nz([PKMSOED],Nz([THED],[THEL]))

This now becomes the final test, so copy it into the prior one:
Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL])))

Repeat:
Nz([CNL],Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL]))))

And finally:
Nz([CGODL],Nz([CNL],Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL])))))

Just to be sure: count the parentheses ... the open parens should each
have a matching closing paren.

Ughhh ... but it works. Too bad the Jet team never implemented CASE or
COALESCE.


Hi, Dale.

How can I make Nz work for this scenario? Or can't it? I've tried
this and of course, it returns "Wrong number of arguments used."
Apparently Nz only works when a maximum of 2 fields are referenced...?

CSL: Nz([CGODL],[CNL],[PKMSOED],[PKMSOEL],[THED],[THEL])

Thanks!

--
www.Marzetti.com


Dale_Fye via AccessMonster.com said:
Or even simplier:

TotalWeightUOM: NZ([WeightUOM], [TotalWtUOM])


Dennis wrote:
TotalWeightUOM: IIf(IsNull([WeightUOM]),[TotalWtUOM],[WeightUOM])

[WeightUOM] and [TotalWeightUOM] are text fields. I'm trying to do
this:

[quoted text clipped - 8 lines]

Thanks for your help!
Click to expand...
Click to expand...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
 
Hi, Bob.

I ran into a little problem. These fields are numeric and I need to format
them to 4 decimal places. Unfortunately, the format properties don't appear
for the alias.

Is Nz the best method here? If so, is formatting possible? The query returns
some very long numbers and I need to trim them down. The text boxes in the
related report are fixed to 4 decimals but those aren't taming the length,
either.

Thanks for your response!

--
www.Marzetti.com


Bob Barrows said:
You need to nest them in the order you wish them to be tested: inside to
out:
Start by writing them in order, leaving the second argument blank until
the final test:
Nz([CGODL],)
Nz([CNL],)
Nz([PKMSOEL],)
Nz([PKMSOED],)
Nz([THED],[THEL])

See? Each Nz returns a single value that can be used as the second
argument of the prior test.

Now, copy the final test - Nz([THED],[THEL]) - into the second argument
of the prior one:

Nz([PKMSOED],Nz([THED],[THEL]))

This now becomes the final test, so copy it into the prior one:
Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL])))

Repeat:
Nz([CNL],Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL]))))

And finally:
Nz([CGODL],Nz([CNL],Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL])))))

Just to be sure: count the parentheses ... the open parens should each
have a matching closing paren.

Ughhh ... but it works. Too bad the Jet team never implemented CASE or
COALESCE.


Hi, Dale.

How can I make Nz work for this scenario? Or can't it? I've tried
this and of course, it returns "Wrong number of arguments used."
Apparently Nz only works when a maximum of 2 fields are referenced...?

CSL: Nz([CGODL],[CNL],[PKMSOED],[PKMSOEL],[THED],[THEL])

Thanks!

--
www.Marzetti.com


Dale_Fye via AccessMonster.com said:
Or even simplier:

TotalWeightUOM: NZ([WeightUOM], [TotalWtUOM])


Dennis wrote:
TotalWeightUOM: IIf(IsNull([WeightUOM]),[TotalWtUOM],[WeightUOM])

[WeightUOM] and [TotalWeightUOM] are text fields. I'm trying to do
this:

[quoted text clipped - 8 lines]

Thanks for your help!
Click to expand...
Click to expand...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
 
Nz when used in a query often returns numbers as a string type instead of a
number type.

If you are sure to get a number returned (never get a null) then you can use

CCur(Nz([CGODL],Nz([CNL],Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL]))))))

IF that could expression could ever return null, then you either need to test
the expression or add one more Nz to the end of the expression that returns 0
or some other numeric value.

Either:
CCur(Nz([CGODL],Nz([CNL],Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],NZ([THEL],0)))))))

OR
IIF(Nz([CGODL],Nz([CNL],Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL]))))) is
Null,Null,
CCur(Nz([CGODL],Nz([CNL],Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL])))))))

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Hi all,
I wonder if someone could help me tell me what I need to put in my query to
do the following.

I have a report with gets it's information from a query, well related
queries actually.

The report lists every job down for a 12 month period, a job number and job
title, the number of groups done for each job ( were a market Research
recruiting company for focus groups) The problem I have instead of I guess
grouping all the groups as one, it will show the job number and job title
again and again depending on how many groups were done under each job
number.

The format is this.
Job # Title No of Groups . Date rang of groups, Total No of groups per
job, Total Number of recruiters who worked on the job. Job Location.

This is what report currently shows.
2510 PROJECT NON & HON (A 17/05/2007 14/05/2007 2 1 Melbourne
2510 PROJECT NON & HON (A 17/05/2007 14/05/2007 2 1 Melbourne
2510 PROJECT NON & HON (A 17/05/2007 14/05/2007 2 1 Melbourne
2510 PROJECT NON & HON (A 17/05/2007 14/05/2007 2 1 Melbourne
2519 GEN Y (AMEND) 1/05/2007 14/05/2007 1 1
Melbourne
2519 GEN Y (AMEND) 1/05/2007 14/05/2007 2 1
Melbourne

Where as I want it to show this

2510 PROJECT NON & HON (A 17/05/2007 14/05/2007 4 8 Melbourne
2519 GEN Y (AMEND) 1/05/2007 14/05/2007 2 6
Melbourne

Does this make sense?

The problem is with one of the queries, any suggestions?

Thanks in advance

Phil
 
Yet another "Whoa!"

Thanks, John! I *should* always have a numeric return however I saw where
there were a couple Nulls so I went with the more complicated IIf string
because the other didn't suffice.

The CCur is new to me. I've never had a reason to use it before. It flipped
me out when I ran the query prior to formatting the column to Fixed/4
Decimals - the currency return was a little bizarre!

Anyway, this now works perfectly! A little more complicated then I'd like
but it works.

Thanks so much for all of the detail!

--
www.Marzetti.com


John Spencer said:
Nz when used in a query often returns numbers as a string type instead of a
number type.

If you are sure to get a number returned (never get a null) then you can use

CCur(Nz([CGODL],Nz([CNL],Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL]))))))

IF that could expression could ever return null, then you either need to test
the expression or add one more Nz to the end of the expression that returns 0
or some other numeric value.

Either:
CCur(Nz([CGODL],Nz([CNL],Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],NZ([THEL],0)))))))

OR
IIF(Nz([CGODL],Nz([CNL],Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL]))))) is
Null,Null,
CCur(Nz([CGODL],Nz([CNL],Nz([PKMSOEL],Nz([PKMSOED],Nz([THED],[THEL])))))))

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Hi, Bob.

I ran into a little problem. These fields are numeric and I need to format
them to 4 decimal places. Unfortunately, the format properties don't appear
for the alias.

Is Nz the best method here? If so, is formatting possible? The query returns
some very long numbers and I need to trim them down. The text boxes in the
related report are fixed to 4 decimals but those aren't taming the length,
either.

Thanks for your response!
Click to expand...
Click to expand...
 
Back
Top