Alternative for MS Excel VLOOKUP function in MS Access 2003 Form..

[CBender]

I am converting several MS Excel spreadsheets into a single MS Access 2003
database for a User.


The User’s original MS Excel VLOOKUP query is coded as follows:

=IF(VLOOKUP(EG2,EH:EI,2,FALSE)=B2,"Good",VLOOKUP(EG2,EH:EI,2,FALSE))


New MS Access Table Name:

0301_ElectricitySupply_FeatureLine-up


Field names for MS Excel and MS Access are as follows:

MS Excel Cell: MS Access Field:
A2 Verify Config

“A2†is VLOOKUP function listed above.



MS Excel Cell: MS Access Field:
B2 Config No

Example: B2 / Config No:
MTU0301-0010

Note: This number is a manually created unique number based on specific
groupings.



MS Excel field: MS Access Field:
EG2 Concatenated Config No_4

Example: EG2 / Concatenated Config No_4 cell data
00200001000500010001000100010002000500010001000100030002000300010002000300010001000400040002000100010001000100020001


MS Excel field: MS Access Field:
EH Concatenated Config No_4_2

Note: Column “EH†was created as a copy of column “EGâ€. Subsequently, the
“Concatenated Config No_4_2†field is a copy of the “Concatenated Config
No_4†field.


MS Excel field: MS Access Field:
EI Config No_2

Note: Column “EI†was created as a copy of the column “Config Noâ€.
Subsequently, the “Config No_2†field is a copy of the “Config No†field.


The way this is SUPPOSED to work…………

If new “Config No†has unique “Concatenated Config No_4†data the “Verify
Config†field should have “Good†recorded.

However, if there is an existing “Config No†record that contains matching
“Concatenated Config No_4†data, the resulting “Verify Config†field should
record the existing “Config No†instead of “Good†in the working form.

If there is no “Concatenated Config No_4†data to compare for the newly
entered “Config No†record, “No Data†should be recorded in the “Verify
Config†field; in its VLOOKUP function, MS Excel lists “#N/A†in column “Aâ€
when this happens.


Side Note: If either the “Config No_2†or the “Concatenated Config No_4_2â€
are not necessary for the lookup and compare functions in MS Access I would
like to delete them if possible to clean up a lot of unnecessary data in the
table.


I hope this was not too complicated to understand. It had to be explained to
me several times for me to understand how the VLOOKUP function was supposed
to work.

Any assistance would be GREATLY appreciated!!!


Thanks,

Chip