Alternative for MS Excel VLOOKUP function in MS Access2003

[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

 

[Jerry Whittle]

Check out DLookup in Help.

 

[CBender]

Jerry,

I tried working with the DLookup function but cannot get the desired results
I am looking for.


The problem I am running into is this.....


If the new “Config No†being entered has a unique “Concatenated Config No_4â€
data the “Verify Config†field should have “Good†recorded.

However, if there already exists a “Config No†record containing identical
“Concatenated Config No_4†data, the “Verify Config†field should show the
assotiated “Config No†of the existing “Concatenated Config No_4†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 (all of the concatenated fields are blank), “No
Data†should be shown in the “Verify Config†field; in the MS Excel VLookup
function “#N/A†is shown in column “A†when this happens.

I cannot code the query properly to search through the existing
“Concatenated Config No_4†for a matching record and show the associated
“Config No†if the data matches an existing record, “Good†if the new
“Concatenated Config No_4†is unique, or “No Data†if there is no new
“Concatenated Config No_4†data to perform a search on.

 

[De Jager]

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

 

[joelgeraldine]

l!l