IIF Statements and TEXT fields HELP

[macdonald007]

Hi,

I am using a query to look for data entry errors in a table. What I am doing is using the same table twice, linking the common field on a one for one basis and then simply using an IIF statement to determine if the data entry field equals itself. It works great if the field is numeric or yes/no etc, but if the field is text this isnt working.

My equation is:

IRSDESC1: IIf([AC_TB.IRS_DESC1]<>[AC_TB_1.IRS_DESC1],-1,1)

Any ideas??

 

[Tanis]

Have a look at the StrComp function in the help files.

 

[mdmilner]

I'm a little confused. If you're linking an exact copy of a table to a table and then comparing the values of a single field to itself, how would you ever get anything other than a precise match?

This common field (your key field). Is it unique or does it show up multiple times in the table?

If the latter (duplicates allowed) you should probably try this.
Use the "Find Duplicates Query Wizard" selectable after hitting the "new' button under the query tab.

Mike

 

[macdonald007]

Sorry, I should have been more specific.

The table in question has the structure:
55555555.1
55555555.2
55555555.3
55555556.1
55555556.2

Each record has items that must be the same on the account level even though we carry the portfolio level detail in the table. However, I also have on each record the base account number (55555555 etc.)....

So, by opening the table twice, setting the account numbers one for one and then comparing the items that must be the same using IIF I can identify the mismatches...this works for numeric and yes/no fields, but does not work for text fields...