IIF returning text value

  • Thread starter Thread starter Julie
  • Start date Start date
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.
 
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
 
All of your IIf()s are returning string/text values, not numbers. Try
Q1KR: IIf([Q1]<4,0,1)
or
Q1KR: Abs([Q1]>=4)
 
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.
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
You're getting an error here: Nz([Q2]," ")

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

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])
 
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 " ".
Click to expand...

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])
Click to expand...
 
Back
Top