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

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
0
Utf
4/6/2010 6:10:07 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

4 Replies
876 Views

Similar Articles

[PageSpeed] 43

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.?

"CBender" <CBender@discussions.microsoft.com> wrote in message 
news:5E059BB9-EB80-4D21-8141-532DBE0DF24A@microsoft.com...
> 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 


0
David
4/6/2010 10:23:57 PM
You know, it would be easier to help if you showed what
you had tried, and explained what problem you've had.

(david)

"CBender" <CBender@discussions.microsoft.com> wrote in message 
news:5E059BB9-EB80-4D21-8141-532DBE0DF24A@microsoft.com...
> 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 


0
david
4/7/2010 11:01:47 AM
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


"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
0
Utf
4/8/2010 2:54:01 AM
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

"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
0
Utf
4/8/2010 3:11:01 AM
Reply:

Similar Artilces:

vlookup with if statement?
Hiring tab: Column K: Band (either 5,6,7 or 8); Column D: Source (either ITSO or GMT), Column M: Country (Buenos Aires, etc) List tab: Column I: Band (either 5,6,7 or 8); Column M: GMT Salaries; Column N: ITSO salaries; Column O: Buenos Aires Salaries In Column X on the Hiring tab, I want to put the salary that matches the criteria that is in the columns above. Basically, Look up whats in column K and column D, and IF Buenos Aires is NOT in column M, then lookup the salary in the list tab that corresponds with the criteria on the Hiring tab. However, if column M does = B...

Import Adobe Form into Access for Report
I am trying to import a PDF into Access to use as a report and populate the fields with a query I have created. I can not find any help in getting this to work. Can someone help me? -- Terry Foster Tax Trilogy ...

I'm using an if statemnt and the result if true points to one cell
I'm using an if statemnt and the result if true points to one cell with special formats and another cell if false with other formats. The result shows the correct value, but I'm more interested in the font because I'm using different symbols from various fonts. ...

Database maintenance -- Checkdb, dbreindex, stats use sequence?
I have some specific questions about checkdb that I would appreciate if you can give me some guidelines. Thank you for your time. a) Is dbcc checkdb to be used before or after that backup is made? If it is before, should the backup be called off if there are any errors in Checkdb? How does SQL agent handles this if called as a seperate step? b) What errors can you repair safely and what not? When would you say restore is the best way to go? Is that always do a repair before going for a restore? c) When do you do dbrendiex? After checkdb, backup, dbreindex? Is stats update nec...

Saving documents to MS CRM from eg Word?
Hi, Is there any solution out there (market ready) that lets you save eg Word document direct to MS CRM ?. Not save to file system and then from MS CRM make a note and attach. To long way ! Regards, SH Hi Siggi, have a look at "RelatedDocuments" at www.mscrm-addons.com "Siggi" <sh@nyherji.is> schrieb im Newsbeitrag news:64f91fda.0501190239.1156de03@posting.google.com... > Hi, > Is there any solution out there (market ready) that lets you save eg > Word document direct to MS CRM ?. Not save to file system and then > from MS CRM make a note and attach. ...

Watch that important update from MS
--xflbwxeum Content-Type: multipart/related; boundary="edcyayzhmxwehhm"; type="multipart/alternative" --edcyayzhmxwehhm Content-Type: multipart/alternative; boundary="zqoktpxnciql" --zqoktpxnciql Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS User this is the latest version of security update, the "September 2009, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to maintain the security of your computer from these vulnerabil...

Recreate Chart of accounts
We created the chart of accounts for a new company and there are a few transactions went through and posted. Then later we designed to change the segments of the GL accounts. That means a new set of COA is needed. The transactions posted is not a concern for us and they can be reentered. But GP won't allow us to delete if posted transactions exist. Is there any way to delete the old set of COA? Thanks! In the same company you would need to purge the history and reconcile to clear the account summaries. Alternatively, you could create a new company and copy the setup to it, if there'...

Tab order after using pop up calendar on date field
After selecting a value in a date field by using the pop up calendar, the tab order on the screen sometimes goes to the first dollar sign ($) on the screen and tabs through each dollar sign until the last one is reached and then starts tabbing through the fields starting with the first one instead of just tabbing to the next field. This happens if the cursor was in a text or number field and the value in the field was not selected (highlighted) when the calendar button was pushed. If the date was typed in to the date field (as apposed to selected using the pop up calendar) then the su...

2003 -RPC over HTTP option not available on Outlook 2003 client
I have the full office system 2003 install, however in the connections setting on profile, I do not have the option displayed to connect to exchange over http. Thoughts? Seb 1) you need exchange 2003 in the profile 2) you need the hotfix for winxp. it will not work on win2k http://support.microsoft.com/?kbid=331320 -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Outlook Tips: http://www.outlook-tips.net/ http://www.poremsky.com - http://www.cdolive.com Expert Zone http://www.microsoft.com/windowsxp/expertzone Search for answers: http://groups.google....

import data from access table into a existing table
I need to map data from an existing access database into a existing staging database. The access database only has the data I need, so the mapping is not a one to one, The SQL table many more columns so I need to do some manual mapping. Is there a way to do this throiugh the import wizard or do I need to write some type of query to manual insert the data? Thanks in advance. -- Jay Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201005/1 ...

Modifying ACC2007 forms
How can I modify the width of a single field without all of the fields being modified? This link should help. http://www.btabdevelopment.com/main/QuickTutorials/A2K7Howtostopallcontrolsmovingtogether/tabid/84/Default.aspx kenrav wrote: >How can I modify the width of a single field without all of the fields being >modified? -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1 Hi, ...

Column/Cell format for Vlookup
I have a lookup formula that worked intermittently =vlookup(K4,'WBS!$A2:$G$330,6,0) Here is the problem: If I change the lookup value (say from 411120 to 431130), I get an NA although the numbers are there. If I go to the lookup sheet and copy and paste the value into my worksheet, it worked. Puzzling??? After making sure that the formats were the same -- I got the same result On the advice of a friend, I used Data, Text to Columns... and noticed that the setting was set to General. When I set the setting to Text everthing worked. Why? Why does Format, Cells, Text NOT work and D...

mailbox move-Exchange 2003
I cannot successfully move a mailbox from one storage group into another. I keep getting the error message "mailbox cannot be moved until cleanup operations complete". I have run cleanup operations several times and it still does not work. What else would cause this issue? Is the a mailbox already present in the target store for the same user that you might need to purge? -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "John" <John@discussions.microsoft.com> wrote in message news:79574F77-FCF6-4813-9D21-DBA92DA41727@...

Outlook 2003 Adding several Emails to Block Sender list at One Tim
I just upgraded to Windows 2003 SP 2. In the older version of Outlook, I could highlight several emails and add them to the junk senders list by selecting: Action/Junk Email/Ad to junk senders list With 2003, the option to “Add Sender to blocked senders list” is grayed out and I am not able to select it. What can I do to be able to use this? It is available when I select an individual email. ...

Vlookup cells in combination of two columns (both in the lookup value AND table array)
I am trying to match information in multiple columns (lookup value) to the same multiple columns in the table array. If the combinations don't match then I'd like the return to be #N/A. I think an example can better clarify what I'm trying to accomplish because I haven't a clue whether I can do it... On the lookup worksheet I have... A1 B1 12345 Closeout 12345 First Fill 22233 First Fill 22233 Closeout On table array worksheet I only have one entry. A1 B1 12345 Closeout 22233 F...

Outlook 2003 Synch Log Errors
One of my users has brought to my attention that in your Outlook 2003 client there is a folder "Synchronization Log" that populates with error information multiple times a day. It is as follows: Offline Address Book, Error: 0X8004010F. I have her Outlook 2003 running in Caching mode and It is pulling from an Exchange 2000 server. We are in the process of upgrading that to 2003. Is it safe to assume that the cause of these errors are Exchange 2000 not playing well with Outlook 2003? Is there a way to turn off this reporting? If not, I've already informed her to ignore ...

Cannot connect with Exchange 2003 over VPN?
User is able to open Outlook 2003 via VPN from a network at a client's site (LAN), but when opening Outlook via VPN from home Outlook does not connect to server. Any ideas? Is there something we can check on the users home Internet connection or router? Thanks, -- Ed Wlodarczyk Ennis, Pellum & Associates Has the user checked the firewall settings? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head s...

Deleting subform record causes form record to be deleted
I have two tables in a one to one relationship with Enforce Referential Integrity NOT selected. I want to delete the subform record but not the form record. When I attempt to delete the subform record, I get the following message? Relationships that specify cascading deletes are about to cause 1 record(s) in this table and in related tables to be deleted. Are you sure you want to delete these records? I delete the subform record and my form record is deleted also. ...

MS CRM integration with SAP?
Hello, Is there any integration of MS CRM with SAP planned anywhere in the roadmap? Has anybody done anything in this direction? Hope to hear any products, ideas, references, tips, advices in this regard. regards Anoop ...

MS Money 2006 & IE7 error
Since installing IE7 b1, when I close Money 2006, it closes down but then does the dialog that an error occurred. And no matter if choose to send or not send, it reopens Money again. Luckily if you close from the logon screen, it doesn't do this cycle again. Hopefully the Money team knows about this issue. Known IE7 error. That's why you are beta testing - to find out what applications IE7 scr*ws. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.micros...

Switchboard issue
Anyone know how I can keep the whole access program from appearing to the user. I only want the user to see the switchboard when they click on the shortcut/icon. Any help is appreciated! Thanks! Star On Wed, 11 Apr 2007 08:36:04 -0700, Star Taylor wrote: > Anyone know how I can keep the whole access program from appearing to the > user. I only want the user to see the switchboard when they click on the > shortcut/icon. > > Any help is appreciated! Thanks! > > Star Click on Tools + StartUp Set the Display Form/Page drop*down to the Switchboard. Uncheck the Displa...

recreate a window
a CComboBox and CQControl derived control shall recreate itself, if the styles given in resource not match the style required for toe control (here: CBS_OWNERDRAWFIXED must be set). As CComboBox is unable to correctly modify this style with ModifyStyle(), the recreation is called and works. ____________________________________________ //----------------------------------------------------------------------- // recreate the underlying hwnd to match required styles bool CQControl::QRecreateWindow(DWORD dwStyle, DWORD dwStyleEx) { CWnd* pWnd = CWnd::FromHandlePermanent(m_hwnd); ASSERT(pWnd != ...

Vlookup #26
I have a field in sheet "NewData" with this formula: =IF(VLOOKUP(A1,Analysis!$A$3:$A$310,1,0)>0,"","NEW") Problem: When I add rows to sheet "Analysis", it adds numbers to $A$3 - ie if I add 10 rows to "Analysis", the formula becomes ...$A$13... Thus the first rows of any new data don't get interogated since the lookup starts at row 13 instead of 3 I need the lookup always to start at $A$3 How can I keep it from incrementing? Glen Insert your new rows after row 3 in the Analysis sheet Pete Hi Glen It sounds as though the followi...

outlook 2003 #76
Outlook will not automatically dial up my internet connection ...

i need remove security or restore access to folder in windows server 2003
hi, I need access to folder in windows server 2003 nothing access with administrator thks __________ Informaci�n de ESET NOD32 Antivirus, versi�n de la base de firmas de virus 5220 (20100623) __________ ESET NOD32 Antivirus ha comprobado este mensaje. http://www.eset.com ESTEBAN OCONITRILLO RAMIREZ wrote: > hi, > I need access to folder in windows server 2003 nothing access with > administrator > http://www.computing.net/forum/windows2003/1.html ...