Solved Query Error

[broberts]

Hello, I have been having an issue with a query that returns an error:
The expression you entered has an invalid . (dot) or ! operator or invalid parentheses.
You may have entered an invalid identifier or typed parentheses following the Null constant.

The query is meant to create a calculated field named Payer that concatenates FirstName, a space, LastName, and (student) if the BillingLastName field is null. Otherwise, the calculated field should concatenate BillingFirstName, a space, BillingLastName, and (billing).

Here is my code:
Payer: IIF(IsNull(BillingLastname), Firstname & " " & Lastname & ", " & (Student), BillingFirstname & " " & BillingLastname & ", " & (Billing))

 

[adhoustonj]

Are (Student) and (Billing) fields that are in the sources you are building your query from, or text that you want to concatenate as an indicator?

If text, try

Payer: IIF(IsNull(BillingLastname), Firstname & " " & Lastname & ", (Student)", BillingFirstname & " " & BillingLastname & ", (Billing)")

 

[broberts]

Yes, (Student) and (Billing) are meant to be concatenated depending on the value of the expression as text at the end of the field.

Code still gets the same error referencing . (dot) or ! operators or invalid parentheses.

 

[bob fitz]

Try:
Payer: IIF(IsNull(BillingLastname), Firstname & " " & Lastname & ", '" & (Student) & "'", BillingFirstname & " " & BillingLastname & ", '" & (Billing) & "'")

 

[broberts]

Try:
Payer: IIF(IsNull(BillingLastname), Firstname & " " & Lastname & ", '" & (Student) & "'", BillingFirstname & " " & BillingLastname & ", '" & (Billing) & "'")

I made sure to type this exactly as you have sent it. The query still returns an error.

 

[bob fitz]

Then perhaps:
Payer: IIF(IsNull(BillingLastname), Firstname & " " & Lastname & " (Student)", BillingFirstname & " " & BillingLastname & " (Billing)")

 

[broberts]

Then perhaps:
Payer: IIF(IsNull(BillingLastname), Firstname & " " & Lastname & " (Student)", BillingFirstname & " " & BillingLastname & " (Billing)")

This returns the same error. I do not believe that the database has any other problems with relationships between these tables, so the issue is isolated to just this expression.

 

[bob fitz]

This returns the same error. I do not believe that the database has any other problems with relationships between these tables, so the issue is isolated to just this expression.

Can you post a copy of the db with just a couple of fictitious records.

 

[plog]

Divide, isolate and conquer. You've got so many pieces that may be the culprit you need to simplify to identify which piece is the offender.

First, Remove the field entirely and run the query without it. I'm not convinced its that field throwing off your query.

If no error with that then add these 3 fields:

A: IsNull(BillingLastname)

B: Firstname & " " & Lastname & ", " & (Student)

C: BillingFirstname & " " & BillingLastname & ", " & (Billing)

One of those fields is throwing the error. Dig into the appropriate fields to find out why. Once you have A, B & C working you can then reconstruct Payer appropriately.

 

[broberts]

Divide, isolate and conquer. You've got so many pieces that may be the culprit you need to simplify to identify which piece is the offender.

First, Remove the field entirely and run the query without it. I'm not convinced its that field throwing off your query.

If no error with that then add these 3 fields:

A: IsNull(BillingLastname)

B: Firstname & " " & Lastname & ", " & (Student)

C: BillingFirstname & " " & BillingLastname & ", " & (Billing)

One of those fields is throwing the error. Dig into the appropriate fields to find out why. Once you have A, B & C working you can then reconstruct Payer appropriately.

Thanks, will try this!

 

[ebs17]

Question of style with little improvement in performance: SQL is preferred in a query.

IsNull(BillingLastname)
BillingLastname Is Null

 

[broberts]

Payer: IIf(IsNull(BillingLastName), FirstName & " " & LastName & " (student)", BillingFirstName & " " & BillingLastName & " (billing)")

This is my final version of the expression I've tried...
I see no syntactical errors but I still have one apparently.

 

[bob fitz]

If you post a copy of the db one of us may be able to find the problem. The last code that I posted worked when I tested it.

 

[broberts]

Here is a copy. It is a textbook assignment so none of the info is real/sensitive.

 

[broberts]

Here is a copy. It is a textbook assignment so none of the info is real/sensitive.

Query is not included because I am not able to save the object with the error. The query includes the calculated field as well as InstanceID, TotalCost, BalanceDue, Phone, and Email.

 

[MajP]

Expr1: IIf(IsNull([BillingLastname]),[Firstname] & " " & [Lastname] & ", (Student)",[BillingFirstname] & " " & [BillingLastname] & ", (Billing)")
qryAllPaymentPlanStudents qryAllPaymentPlanStudents

Expr1
Steven Anderson, (Student)
Peggy Bahr, (Student)
Anna Barbee, (Student)
James Bennett, (Student)
Henry Brown, (Student)
Jake Campbell, (Student)
Michael Clark, (Student)
Lauren Conley, (Student)
Loren Couture, (Student)
David Davis, (Student)
Angelina Dressler, (Student)
Sarina Fan, (Student)
Anna Fisher, (Student)
David Innes, (Student)
Hubert Johnson, (Student)
Sharon Main, (Student)
Sarah Meany, (Student)
Alice Mize, (Student)
Silvia Monk, (Billing)
John Montgomery, (Student)
Arthur Morris, (Student)
Yvonne Oconnell, (Student)
Debbie Pass, (Student)
Sandra Petersen, (Student)
Heather Six, (Student)
Robert Smith, (Student)
Rodney Smith, (Student)
Henrique Paul, (Billing)
Kenneth Steele, (Student)
Carmen Volpe, (Student)
Julie Walsh, (Student)
America Ward, (Student)
Byron Washington, (Student)
Ellen Weidner, (Student)

 

[bob fitz]

Kind of amazed that no one pointed out that field names in expressions MUST have square brackets.

Not sure that "MUST" is quite right. I thought that expressions without them would still work if the field name had NO spaces in it. I would agree that they are advisable though.

 

[broberts]

I got it....
I was putting the expression in the criteria box instead of the field box. Silly mistake. Thank you all for the input!

 

[bob fitz]

I got it....
I was putting the expression in the criteria box instead of the field box. Silly mistake. Thank you all for the input!

Glad you have it working

 

[MajP]

You can simplify it a little to help avoiding errors, since it is always first and last.

 [FirstName] & " " & [lastName] & IIf(IsNull([BillingLastname]),"(Student)","(Billing)")