IIF returning text value


J

Julie

I have a query with fields Q1, Q2, Q3, Q4. I want to collapse the answers
for questions 1-4 into either 1 or 0. So, I am using Iif....

Q1KR: IIf([Q1]<4,"0","1")
Q2KR: IIf([Q2]<4,"0","1")
Q3KR: IIf([Q3]<4,"0","1")
Q4KR: IIf([Q4]<4,"0","1")

Then I want to create a new field that totals those...

Total: [Q1KR]+[Q2KR]+[Q3KR]+[Q4KR]

Instead of 3, I'm getting 1 0 1 1

What is going on?

Incidently if I change the total to: [Q1]+[Q2KR]+[Q3KR]+[Q4KR], it sums
just fine.
 
Ad

Advertisements

P

Petr Danes

Probably you have these defined as text fields, which will cause the machine
to interpret + as concatenate. To force them to be seen as numeric values,
use Val([Q1KR])+Val([Q2...

Pete
 
D

Duane Hookom

All of your IIf()s are returning string/text values, not numbers. Try
Q1KR: IIf([Q1]<4,0,1)
or
Q1KR: Abs([Q1]>=4)
 
J

Julie

IIf([Q1]<4,0,1) worked for summing the fields later on.

If you're up for problem two:

I just noticed that I have null values in some of my fields and I want them
to stay null after the IIf. So, my if would return nothing if IsNull[Q1], 0
if [Q1]<4 and 1 if [Q1]>=4.

I tried: Q2KF: IIf(Nz([Q2]," ")<4, 0, 1) but I get an error.

Thoughts? And thanks for the help.





Duane Hookom said:
All of your IIf()s are returning string/text values, not numbers. Try
Q1KR: IIf([Q1]<4,0,1)
or
Q1KR: Abs([Q1]>=4)


--
Duane Hookom
Microsoft Access MVP


Julie said:
I have a query with fields Q1, Q2, Q3, Q4. I want to collapse the answers
for questions 1-4 into either 1 or 0. So, I am using Iif....

Q1KR: IIf([Q1]<4,"0","1")
Q2KR: IIf([Q2]<4,"0","1")
Q3KR: IIf([Q3]<4,"0","1")
Q4KR: IIf([Q4]<4,"0","1")

Then I want to create a new field that totals those...

Total: [Q1KR]+[Q2KR]+[Q3KR]+[Q4KR]

Instead of 3, I'm getting 1 0 1 1

What is going on?

Incidently if I change the total to: [Q1]+[Q2KR]+[Q3KR]+[Q4KR], it sums
just fine.
 
P

Petr Danes

You're getting an error here: Nz([Q2]," ")

Nz takes one parameter, you're giving it two - [Q2] and " ".

What might work for you is the Switch function:
Switch(IsNull([Q1]),Null,Nz([Q1])<4,0,Nz([Q1])=>4,1)

You need the Nz function here, because Switch evaluates everything,
regardless of whether it is ued or not, and if Q1 is null, you would get an
error in the compares.

Pete



Julie said:
IIf([Q1]<4,0,1) worked for summing the fields later on.

If you're up for problem two:

I just noticed that I have null values in some of my fields and I want
them
to stay null after the IIf. So, my if would return nothing if IsNull[Q1],
0
if [Q1]<4 and 1 if [Q1]>=4.

I tried: Q2KF: IIf(Nz([Q2]," ")<4, 0, 1) but I get an error.

Thoughts? And thanks for the help.





Duane Hookom said:
All of your IIf()s are returning string/text values, not numbers. Try
Q1KR: IIf([Q1]<4,0,1)
or
Q1KR: Abs([Q1]>=4)


--
Duane Hookom
Microsoft Access MVP


Julie said:
I have a query with fields Q1, Q2, Q3, Q4. I want to collapse the
answers
for questions 1-4 into either 1 or 0. So, I am using Iif....

Q1KR: IIf([Q1]<4,"0","1")
Q2KR: IIf([Q2]<4,"0","1")
Q3KR: IIf([Q3]<4,"0","1")
Q4KR: IIf([Q4]<4,"0","1")

Then I want to create a new field that totals those...

Total: [Q1KR]+[Q2KR]+[Q3KR]+[Q4KR]

Instead of 3, I'm getting 1 0 1 1

What is going on?

Incidently if I change the total to: [Q1]+[Q2KR]+[Q3KR]+[Q4KR], it
sums
just fine.
 
J

John W. Vinson

You're getting an error here: Nz([Q2]," ")

Nz takes one parameter, you're giving it two - [Q2] and " ".

Well, Nz() actually takes two parameters, the second one optional. If the
second parameter is supplied, Nz() returns it if the first is NULL; if only
one parameter is supplied it returns a number 0.

Julie is getting a text value returned because she's using a Text value - " "
- as the (optional) argument. If she wants a number she needs to use a number
(either a 0 without quotes, or she can take the default action of NZ to return
a numeric 0):

Total: NZ([Q1KR])+NZ([Q2KR])+NZ([Q3KR])+NZ([Q4KR])
 
Ad

Advertisements

P

Petr Danes

Okay, learn something every day. I've only ever used it it with one, didn't
even know it would accept two.

Stop learning, stop breathing.

Thanks, John.



John W. Vinson said:
You're getting an error here: Nz([Q2]," ")

Nz takes one parameter, you're giving it two - [Q2] and " ".

Well, Nz() actually takes two parameters, the second one optional. If the
second parameter is supplied, Nz() returns it if the first is NULL; if
only
one parameter is supplied it returns a number 0.

Julie is getting a text value returned because she's using a Text value -
" "
- as the (optional) argument. If she wants a number she needs to use a
number
(either a 0 without quotes, or she can take the default action of NZ to
return
a numeric 0):

Total: NZ([Q1KR])+NZ([Q2KR])+NZ([Q3KR])+NZ([Q4KR])
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top