Using a 'Lookup Table' to check data

[0sprey]

Characters 30 and 31 in the {tbl_alpha.address} field should only contain valid state abbreviations.
In order to be able to check and flag any potential errors in state abbreviations within the “{tbl_alpha.address} field, I am considering using the formula below.

//@State Abbreviation Check
if
Mid ({tbl_alpha.address},30 ,2) = "AL" or //Alabama AL
Mid ({tbl_alpha.address},30 ,2) = "AZ" or //Arizona AZ
Mid ({tbl_alpha.address},30 ,2) = "AR" or //Arkansas AR
Mid ({tbl_alpha.address},30 ,2) = "CA" //California CA
.
.
.
Mid ({tbl_alpha.address},30 ,2) = "WY" // Wyoming WY
then 1 else 0

However, is there a way I could do the same check by using a look up table that would contain one only field, that being the correct state abbreviations… {tbl_state.abbv}?
I actually added {tbl_state} to the report but of course I cannot link the {tbl_state.abbv} field to {tbl_alpha.address} field as they are not equivalent.

Any techniques that might allow the use of a look up table in this situation ? Thanks

 

[lbass]

There are various ways you can do this--you could link a substring to the abbreviation in a command, use a subreport with the state table that alerts you to an incorrect abbreviation, or you could generate the contents of the formula in a separate report and then paste it into the formula editor of your current report--but it depends on what you want to happen when you find an invalid result, so please explain.

-LB

 

[0sprey]

At this point, if Mid({tbl_alpha.address},30 ,2) equals an invalid State abbreviation then that record would be flagged using Conditional Formatting.

Later, there may be a need to select these in invalid State abbreviation records by way of Selection Criteria.

 

[lbass]

It might be easiest just to create a formula like this:

If mid({tbl_alpha.address},30,2) in ['AL','AR','AZ',...'WY'] then 1

Then you can use it for formatting or for a selection formula. If speed is an issue and you want to use it for selection, I would create the entire report using a command, and using the substring function in the where clause or in the From clause.

-LB

 

[0sprey]

LB... I am going to use your "If mid({tbl_alpha.address},30,2) in ['AL','AR','AZ',...'WY'] then 1" method....
(Speed is not an issue.)

Thanks for this advice.. !

 

[DjangMan]

As an aside (because I didn't see them in your example) - watch out for the US military 'state' codes, e.g. AA, AE and AP.