IIF returning text value

[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.

 

[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

 

[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)

 

[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.

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

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.

 

[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

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.

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

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.

 

[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])

 

[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.

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])