Multiple expressions in one query returning errors

  • Thread starter Thread starter liznewhome
  • Start date Start date
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
 
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)
 
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



[email protected] said:
Greetings,
Click to expand...
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"
Click to expand...
What am I doing wrong?
Click to expand...
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")
Click to expand...
Thanks so much1
Liz- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 
Back
Top