Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

query variable doesn't work in report

Status
Not open for further replies.

betachristopher

Programmer
Oct 25, 2006
54
US
i have this variable in a query:

Code:
Physician: (Staff.firstName & " " & Staff.lastName & IIf(Nz(Staff.degree),", " & Staff.degree,""))

so if firstName = John and lastName = Doe and degree = MD, the doctor name should be listed as "John Doe, MD".

if the degree is null, the doctor name should be listed as "John Doe".

simple enough...but...

i have two queries that do this and they work fine. i have a third query that joins these two queries because i only want the results that exist in both queries. again, the doctor names are listed correctly in this third query.

but when i create a report using this third query, i get names with no degree displayed as "John Doe, ". i have tried several things like using completely different table and field names between the two main queries, i have tried several different ways of saying null like IIf(Nz(Staff.degree), IIf(IsNull(Staff.degree), IIf(Staff.degree Is Null,.

what can i do differently?
 
You can start by providing another argument for Nz(). IMO, you should always have two arguments in Nz().

The first argument of IIf() should evaluate to either true or false.
Code:
Physician: Staff.firstName & " " & Staff.lastName & IIf(IsNull(Staff.degree),Null,", " & Staff.degree)
You might also try:
Code:
Physician: Staff.firstName & " " & Staff.lastName & ", " + Staff.degree

Duane
Hook'D on Access
MS Access MVP
 
thank you for helping. unfortunately that didn't work either. so then i changed it so that in the two main queries would have two fields, DocName and DocDegree. then i created a variable in the third query to concatenate them. but it still did the same thing on the report, John Doe, .

so, i just left them as two fields in the third query and concatenated them directly in the report instead...now everything works as it should.

thanks again.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top