IIf Function Access Query

Copper Contributor

New Query, Comparing 2 tables. 

Table 1 System Accounts

  • Joined by: Company Name (Short Text)

Table 2 Database Accounts

  • Joined by: Company Name (Short Text)

I would like to make sure the Account Numbers in both tables are equal. 


Field 1 

Field: Company Name

Table: Database Accounts


Field 2 Account #

Table: System Accounts (short text)


Field 3 Account Number

Table: Database Accounts (short text)


Field 4 If Function

IIf([System Accounts]![Account #]=[Database Accounts]![Account Number],"Good","Bad")


Example after I run Data:

Company NameAccount NumberCust #Expr1
OHIO VALLEY TESLA30-00677830-006778Bad


Account Number and Cust# both equal 30-006778, however the equation is showing "Bad".


I checked both table and both fields are short text. I don't think its a formatting issue. 


Does anyone know what I am doing wrong? 


5 Replies

@dgermano Please show the full query.

@XPS35 SELECT [SSRS- AX Accounts].Site, [Master List].Site, [Master List].[Upload to AX], [Master List].[Account Number], [SSRS- AX Accounts].[Cust #], [SSRS- AX Accounts].[Address type],

IIf([Master List]![Account Number]=[SSRS- AX Accounts]![Account # ],"Good","Bad") AS Expr1

FROM [SSRS- AX Accounts] INNER JOIN [Master List] ON [SSRS- AX Accounts].Name = [Master List].[Upload to AX]
WHERE ((([SSRS- AX Accounts].Site)="3001") AND (([Master List].Site)="3001") AND (([SSRS- AX Accounts].[Address type]) Like "*delivery*"));


The Fields in my previous post do not reflect this SQL. Please let me know if I need to update those for less confusion. 

All I think about are the exclamation marks (!) you use. Try replacing it with dots (.).

@XPS35  thank you for your help, however that did not work. 



Maybe there is a difference you don't see easily, like trailing spaces or hidden special characters. This often happens e.g. when an import from a different system than Access is involved.


There are several methods to check for this. A simple test could be a criteria: "30-006778"

which you type in (not copy from one of the fields) and use in the query first for one AccountNr column then for the other. Does your example line/record always get returned?

Access News
Access DevCon