IIf Function Access Query

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

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