Multiple expressions in one query returning errors

L

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
 
D

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

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
 

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