IIf Function Access Query

[dgermano]

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 Name	Account Number	Cust #	Expr1
OHIO VALLEY TESLA	30-006778	30-006778	Bad

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? 

[#2]

@dgermano Please show the full query.

[#3]

@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. 

[#4]

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

[#5]

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

[#6]

Hi,

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?

Servus
Karl
************
Access News
Access DevCon