Recreate VLookup in MS Access 2003 Form Using DLookup.....

[CBender]

PLEASE!!! Don’t reply back and tell me to use DLookup() unless you also
provide the coding I need as well. I have been trying for weeks to use
DLookup() and cannot code the query properly and I am VERY frustrated.


The MS Excel VLOOKUP query I am trying to recreate in MS Access 2003 is as
follows:

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


Example "Config No": MTU0301-0010

Example "Concatenated Config No_4" data
00200001000500010001000100010002000500010001000100030002000300010002000300010001000400040002000100010001000100020001


The way this is SUPPOSED to work…………

If a new "Config No" being entered contains a unique "Concatenated Config
No_4"

Then the "Verify Config" field should store "Good" in that field.

However……

If there is an existing "Config No" record containing a matching
"Concatenated Config No_4" the "Verify Config" field should
record the existing "Config No" instead of "Good" in the form.

AND……

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; the VLOOKUP function in MS Excel lists "#N/A" in Column "A" when this
happens.


I hope this was not too complicated to understand. It had to be explained to
me a few times for me to understand how the User's particular VLOOKUP
function was supposed to work.


Any assistance would be GREATLY appreciated!!!


Thanks,

 

[David C. Holley]

An evaluation like you mentioned below should not be stored in the database.
The commonly accepted design standard is to only provide this information in
a calculated control on a form or a report.

Also, keep in mind that VLookup and DLookup are not interchangable functions
in terms of how they operate and their use which is why one is an Excel
function and the other an Access function. The question is not, 'How do I
recreate a VLookup function?' but rather 'How do I get the information out
of my database?'.

Can you post information on how you have your tables structured - table
names, field names, etc.?

 

[david]

You know, it would be easier to help if you showed what
you had tried, and explained what problem you've had.

(david)

 

[tbs]

I have translate whatever you have done in excel into access. Note that you
have to replace the excel column name into access eqavilant of the table name
and field name.

dim sRetValue as string
if dcount("1", "<EH:EI>", "<EH> = '" & <EG2> & "'") > 0 then
sRetValue = dlookup("<EI>", "<EH:EI>", "<EH> = '" & <EG2> & "'")
else
sRetValue = "Good"
end if

 

[tbs]

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

ops, sorry ignore my previous post. IE hang while I was replying just now.

dim sRetValue as string

dim sRetValue as string
if dcount("1", "<EH:EI>", "<EH> = '" & <EG2> & "'") > 0 then
sRetValue = dlookup("<EI>", "<EH:EI>", "<EH> = '" & <EG2> & "'")
if sRetValue = "<B2>" then
sRetValue = "Good"
end if
else
sRetValue = "No Data"
end if