query variable doesn't work in report

[betachristopher]

i have this variable in a query:

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?

 

[dhookom]

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.

Physician: Staff.firstName & " " & Staff.lastName & IIf(IsNull(Staff.degree),Null,", " & Staff.degree)

You might also try:

Physician: Staff.firstName & " " & Staff.lastName & ", " + Staff.degree

Duane
Hook'D on Access
MS Access MVP

 

[betachristopher]

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.