Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Using a 'Lookup Table' to check data

Using a 'Lookup Table' to check data

Using a 'Lookup Table' to check data

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

RE: Using a 'Lookup Table' to check data

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.


RE: Using a 'Lookup Table' to check data

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.

RE: Using a 'Lookup Table' to check data

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.


RE: Using a 'Lookup Table' to check data

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

RE: Using a 'Lookup Table' to check data

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close