Multiple expressions in one query returning errors

[liznewhome]

Greetings,

I am an Access Neophyte, self-teaching as I set up a client database
for my company. I am trying to run a query that will return info from
4 expressions; each using a different field. Expression 2 returns the
expected data. All the others return "#Error"

What am I doing wrong?

Expr2: IIf([Pregnant]="Yes","1","0")
Expr3: IIf([IV_Drug_User]="Yes","1","0")
Expr4: IIf([SACPA_Probation]="Yes","1","0")
Expr5: IIf([SACPA_Parole]="Yes","1","0")

Thanks so much1
Liz

 

[Duane Hookom]

With this type of question, you should provide the data types of all fields
and tell us what you are attempting to do. "return info" doesn't tell us much.

If your fields are yes/no type, then compare the fields with True. Also, I
expect you want to return a number and not a string("1" and "0" as strings).

SACPA_Prob: IIf([SACPA_Probation]="Yes","1","0")
maybe should be
SACPA_Prob: IIf([SACPA_Probation]=True,1,0)
or
SACPA_Prob: Abs([SACPA_Probation]=True)

 

[AccessNeophyte]

With this type of question, you should provide the data types of all fields
and tell us what you are attempting to do. "return info" doesn't tell us much.

If your fields are yes/no type, then compare the fields with True. Also, I
expect you want to return a number and not a string("1" and "0" as strings).

SACPA_Prob: IIf([SACPA_Probation]="Yes","1","0")
maybe should be
SACPA_Prob: IIf([SACPA_Probation]=True,1,0)
or
SACPA_Prob: Abs([SACPA_Probation]=True)

--
Duane Hookom
Microsoft Access MVP

Greetings,

I am an Access Neophyte, self-teaching as I set up a client database
for my company. I am trying to run a query that will return info from
4 expressions; each using a different field. Expression 2 returns the
expected data. All the others return "#Error"

What am I doing wrong?

Expr2: IIf([Pregnant]="Yes","1","0")
Expr3: IIf([IV_Drug_User]="Yes","1","0")
Expr4: IIf([SACPA_Probation]="Yes","1","0")
Expr5: IIf([SACPA_Parole]="Yes","1","0")

Thanks so much1
Liz- Hide quoted text -

- Show quoted text -

Thank you Duane! You are correct to sumise that the fields are yes/
no. I was using Yes rather than True. Those fields now have a "1"
where True.

For the report I'm trying to create, I need to have the total number
of people for whom each of the fields (Pregnant, IV_Drug_User,
SACPA_Probation, SACPA_Parole) is True. I also need to know how many
"Pregnant=True" are also "IV_Drug_User=True."

How do I get there from here? I know I do some things in the query,
and others in the report. I don't want the records themselves to show
on the report - just the counts (totals?).

Blessings upon your house unto the nth generation!
Access Neophyte