David,
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 unique “Concatenated Config No_4”
data the “Verify Config” field should display “Good”.
However, if there already exists a “Config No” record that contains identical
“Concatenated Config No_4” data, then the “Verify Config” field should show
the
assotiated “Config No” of the existing “Concatenated Config No_4” data
record 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” field data 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.
Would you be able to offer me any programming assistance (even generalized
if not specific) other than simply referring me to the DLookup() function?
--
Chip
"David C. Holley" wrote:
> DLookup()
>
> "CBender" <(E-Mail Removed)> wrote in message
> news:3CEEBDA2-392F-4569-9E08-(E-Mail Removed)...
> >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
>
>
> .
>
|
Similar Threads
