Prev: Remove sort and filters programmatically on form
Next: Unbound combobox generating data type error
From: CBender on 6 Apr 2010 14:10
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,

--
Chip
From: Dennis on 6 Apr 2010 14:35
Well, what *I* would do is write a VBA routine in the OnExit event for the
field. When the user attempts to tab out, the VBA code is executed. It's
easier (for me anyway) to write in VBA than in those IIF statements.

Here's the DLookup code that you need:

SomeVariableName = Nz(DLookup("[FieldThatIWantReturned]",
"TheLookupTableName", "[KeyFieldName] = '" & TheNameOfTheFormField & "'"))

If you replace my descriptive terms above with the names of the objects in
YOUR application, it should work just fine.



"CBender" wrote:

> 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,
>
> --
> Chip
 | 
Pages: 1
Prev: Remove sort and filters programmatically on form
Next: Unbound combobox generating data type error