Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Syntax error - IIF and Dlookup

Steel Contributor

Hello Experts,

 

I have a union query and I am trying to add a dlookup to it.  I am getting a syntax but I dont see where it is?  think its on the dlookup part and I have been adding parenthesis but getting nowhere. 

 

below is only the first column of the union query.

 

UNION ALL SELECT IIf([GroupDate] Like "Coming Due" & "*",IIf(Nz([Amount],0)<0,[Amount]*-1,[Amount]), dlookup("Balance Euro","tblFXParent","IDFXParent =" & IDParentfk) as TEST,

 

thank you.

 

5 Replies
best response confirmed by Tony2021 (Steel Contributor)
Solution

Hi,

 

From what you posted it looks as if the first IIf is missing its closing parenthesis. So, maybe another ) before " as TEST" does the trick.

 

Servus
Karl
Access News
Access DevCon

 

@Tony2021

I guess the space in Balance Euro is the problem. It is better not to have spaces in fieldnames. If you do use [.... ] around your fieldname.

So "[Balance Euro]". 

 

> I guess the space in Balance Euro is the problem.

 

Spaces in field names are not good, of course, but written as a (stand-alone) text parameter inside quotes, they do not require square brackets. So that can't be the problem.

 

Servus
Karl
Access News
Access DevCon

thank you Karl. that was it.
also you do not need to Concatenate, just use:

IIf([GroupDate] Like "Coming Due*", ...
1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution

Hi,

 

From what you posted it looks as if the first IIf is missing its closing parenthesis. So, maybe another ) before " as TEST" does the trick.

 

Servus
Karl
Access News
Access DevCon

 

View solution in original post