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

C

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

John W. Vinson

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.
Click to expand...

In what way does the string MTU0301-0010 "contain" the string
00200001000500010001000100010002000500010001000100030002000300010002000300010001000400040002000100010001000100020001?

I simply don't understand the logic. Should the long string be parsed out into
four-byte segments, or does the existance of a substring "0010" anywhere
within the long string qualify, or what????
 
J

John Spencer

It is hard to understand what you want.

Do you have three fields in one table named as follows?
Config No
Concatenated Config No_4
Verify Config

Do you want to check when entering a new record that the value in concatenated
Config No_4 does not exist in any other record in the database. If it does
not then enter "Good" in Verify Config. If it does then enter the Config no
of the record that does exist already (and presumably is marked Good in the
verify config field.


Assuming you are doing this in a form. You could add code like the following
to run in the after Insert event of the form.

UNTESTED UNTESTED UNTESTED
Private Sub Form_AfterInsert()

Dim vResult as Variant

If IsNull(Me.[Concatenated Config No-4]) THEN
vResult = "No Data"
ELSE

VResult = Nz(DLookup("[Config No]","[The Name of the
Table]","[Concatenated Config No_4] = '" & Me.[Concatenated Config No-4] &
"'"),"Good")
END IF

Me.[Verify Config] = vResult
End Sub


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
C

CBender

Thank you for your reply and interest in helping me with this problem.

The "Config No" field contains previously entered Config Numbers.

The record has another field called "Concatenated Config No_4" which is a
concatenation of the last four (4) characters of several other fields.

The "Verify Config" field should check to see if any previous "Config No"
record has the same "Concatenated Config No_4" data that the current record
contains. If the check shows another record with the same data this field
should show the Config Number of the record with the duplicate "Concatenated
Config No_4" data. If the "Concatenated Config No_4" is unique, then "Good"
should be listed in the "Verify Config" field.

However, if there is NO "Concatenated Config No_4" to match against in the
current record, then "No Data" should be recorded in the "Verify Config"
field.

Does this help any in explaining what I am trying to work with?
 
C

CBender

John,

Thank you VERy much for your assistance. When I tried working with your
suggested coding I received a compile error message of: "Else Without If"

I am REALLY an idiot when it comes to coding; I am more of a GUI coder more
than anything. Any suggestions on how I can fix this problem?

Here is what I coded based on your previous message:

Private Sub Form_AfterUpdate()

Dim vResult As Variant

If IsNull(Me.[Concatenated Config No_4]) Then vResult = "No Data"

Else

vResult = Nz(DLookup("[Config No]",
"[0301_ElectricitySupply_FeatureLine-up]", "[Concatenated Config No_4] = '" &
Me.[Concatenated Config No_4] & "'"), "Good")

End If

Me.[Verify Config] = vResult

End Sub


--
Chip


John Spencer said:
It is hard to understand what you want.

Do you have three fields in one table named as follows?
Config No
Concatenated Config No_4
Verify Config

Do you want to check when entering a new record that the value in concatenated
Config No_4 does not exist in any other record in the database. If it does
not then enter "Good" in Verify Config. If it does then enter the Config no
of the record that does exist already (and presumably is marked Good in the
verify config field.


Assuming you are doing this in a form. You could add code like the following
to run in the after Insert event of the form.

UNTESTED UNTESTED UNTESTED
Private Sub Form_AfterInsert()

Dim vResult as Variant

If IsNull(Me.[Concatenated Config No-4]) THEN
vResult = "No Data"
ELSE

VResult = Nz(DLookup("[Config No]","[The Name of the
Table]","[Concatenated Config No_4] = '" & Me.[Concatenated Config No-4] &
"'"),"Good")
END IF

Me.[Verify Config] = vResult
End Sub


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
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,
Click to expand...
.
Click to expand...
 
C

Clif McIrvin

Take another look at John's original post ... and note that your post is
If IsNull(Me.[Concatenated Config No_4]) Then vResult = "No Data"

Else
Click to expand...

but John's code is on *three* lines:
If IsNull(Me.[Concatenated Config No-4]) THEN
vResult = "No Data"
ELSE
Click to expand...
Click to expand...

That really does make a difference. "THEN" has to be the last word on
the line for the VBA complier to allow use of an "ELSE".

Clif



CBender said:
John,

Thank you VERy much for your assistance. When I tried working with
your
suggested coding I received a compile error message of: "Else Without
If"

I am REALLY an idiot when it comes to coding; I am more of a GUI coder
more
than anything. Any suggestions on how I can fix this problem?

Here is what I coded based on your previous message:

Private Sub Form_AfterUpdate()

Dim vResult As Variant

If IsNull(Me.[Concatenated Config No_4]) Then vResult = "No Data"

Else

vResult = Nz(DLookup("[Config No]",
"[0301_ElectricitySupply_FeatureLine-up]", "[Concatenated Config No_4]
= '" &
Me.[Concatenated Config No_4] & "'"), "Good")

End If

Me.[Verify Config] = vResult

End Sub


--
Chip


John Spencer said:
It is hard to understand what you want.

Do you have three fields in one table named as follows?
Config No
Concatenated Config No_4
Verify Config

Do you want to check when entering a new record that the value in
concatenated
Config No_4 does not exist in any other record in the database. If
it does
not then enter "Good" in Verify Config. If it does then enter the
Config no
of the record that does exist already (and presumably is marked Good
in the
verify config field.


Assuming you are doing this in a form. You could add code like the
following
to run in the after Insert event of the form.

UNTESTED UNTESTED UNTESTED
Private Sub Form_AfterInsert()

Dim vResult as Variant

If IsNull(Me.[Concatenated Config No-4]) THEN
vResult = "No Data"
ELSE

VResult = Nz(DLookup("[Config No]","[The Name of the
Table]","[Concatenated Config No_4] = '" & Me.[Concatenated Config
No-4] &
"'"),"Good")
END IF

Me.[Verify Config] = vResult
End Sub


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
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,
Click to expand...
.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Alternative for MS Excel VLOOKUP function in MS Access2003 4
Alternative for MS Excel VLOOKUP function in MS Access 2003 Form.. 0
Alternative for MS Excel VLOOKUP function in MS Access 2003 2
Dlookup issue with Access 2003! PLEASE HELP!!! 1
using dlookup in a query 2
pop up form to open one record with dlookup 1
Cannot uninstall MS Office Standard 2
Open second form based on field value in first form 3

Top