Results 1 to 3 of 3
  1. #1
    zoomzoomsd is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    1

    Vlookup to DLookup Help

    First post to the forums.

    I recently built a "database" per say using Excel but I have decided to use Access instead. I have some very long Vlookup formula's in the Excel document that I still need to use in the Access database.

    Here's what I'm working with:

    I have part #'s that can be input in many different formats in column A but column B does a Vlookup and translates column A to a updated and correctly formatted part #. Then column C refers to column B and returns a series for that part #.

    A part # could be entered as A1-AA1-1111-A1, AA1-1111, AA1-1111-A1 or 12-345678. Of course the A's and #'s are just place holders. That's why these VLookups are so long, because they have to go through any of those possibilities to find the correct corresponding part number. In the end (column B) the part number should be in the 12-345678 format, if the reference table has a corresponding part #.

    I attached screenshots of the Vlookup's that are currently being used. The longer one is what would be in column B and the shorter would be in Column C.

    Is there a easy way to transfer these to a format for Access? I'm using a Table in Access, similar to Excel, so if a user inputs a part number in one column, the next column over needs to display the correct part #/format.



    Thanks in advanced!
    Attached Thumbnails Attached Thumbnails vlookup1.jpg   vlookup2.PNG  

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Sounds like bad db design.

    Use domain aggregate functions (DLookup, DCount, DAvg, etc) to search directly on tables. Access Help has guidelines.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,018
    Quote Originally Posted by June7 View Post

    ...Sounds like bad db design...
    Actually, it doesn't sound like a database design, at all! Rather it sounds like what John Vinson calls 'committing spreadsheet by database!'

    Quote Originally Posted by zoomzoomsd View Post

    ...I have part #'s that can be input in many different formats in column A but column B does a Vlookup and translates column A to a updated and correctly formatted part #. Then column C refers to column B and returns a series for that part #...
    To be perfectly honest about it, this is an approach that is almost certainly bound to result in many, many errors! Rather than allowing "input in many different formats," you would be far better off having:

    • A 'parts number' Table holding all valid part numbers. Having this data in a Table will easily allow for the addition of new parts. Taking the stated approach, if a new part number cannot be formatted correctly, using the current rules, you'd have to go back into the program and re-write your code. This could conceivably be necessary every time you add a new part!

    • On your Form, a Combobox populated with all valid part numbers, based on the 'parts number' Table. Then, instead of trying to guess what the user means, you could know what the mean.


    Depending on the type of data you're referring to when you say "returns a series for that part #," you could either use the AfterUpdate event of the Combobox to do a DLookup to retrieve this data, or, if it is appropriate, have this data in the same part numbers Table and include it in the RowSource of the Combobox, once again using the AfterUpdate event of the Combobox to assign the various items to Textboxes on the Form.

    Quote Originally Posted by zoomzoomsd View Post
    ...Of course the A's and #'s are just place holders...
    This is quite possibly the strangest thing about your post! If 'the A's and #'s are just place holders,' as you state, the only thing that remains, for the part numbers, are the dashes!

    You have a real challenge, here, in that you need to look at what you have and what you need, and basically forget about everything you know about Excel! Excel and Access both use VBA, but it's not the same VBA! Many functions that share common names, in VBA forExcel and VBA for Access, perform different tasks!

    Linq ;0)>

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VLookup
    By chethan1333 in forum Access
    Replies: 1
    Last Post: 02-23-2013, 10:14 AM
  2. VLookup? in Access 2010
    By Lazor78 in forum Database Design
    Replies: 1
    Last Post: 07-25-2012, 03:34 AM
  3. Vlookup in Access with SQL
    By jogunjobi in forum Queries
    Replies: 1
    Last Post: 01-30-2012, 07:25 PM
  4. working as a vlookup function
    By cleon in forum Queries
    Replies: 3
    Last Post: 12-16-2011, 02:51 PM
  5. Vlookup Question?
    By jjm3066 in forum Access
    Replies: 3
    Last Post: 11-15-2011, 11:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums