Nov 16 2022 11:35 AM
New Query, Comparing 2 tables.
Table 1 System Accounts
Table 2 Database Accounts
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?
Nov 16 2022 01:56 PM
@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.
Nov 17 2022 04:50 AM
Nov 17 2022 02:34 PM
@XPS35 thank you for your help, however that did not work.
Nov 18 2022 12:40 AM - edited Nov 18 2022 12:43 AM
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