Compare to TEXT fields

[macdonald007]

Hi I have a record that looks like this:

555-55555 TEXT TEXGH

I want a simple funtion that will look at TEXT and tell me that it is not equal to TEXGH

The field type is text. Can I do this with a query using the IIF function?

 

[Norie]

Just use <>"TEXGH" as the criteria for the field.

 

[macdonald007]

I agree that that should work, but instead of specifying the specific code, I want to do something like this that will pick up if the field does not match.

This WORKS fine if the field type is numeric, but isn't working if it is TEXT.

This is driving me nuts.

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

 

[Norie]

Can you explain yourself further?

The Iif statement you quoted would compare a field in a record with itself, surely.

What are you actually trying to do?

 

[macdonald007]

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

Each record has data 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 in a query, 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 fine for numeric and yes/no fields, but does not work for text fields...