Vlookup to DLookup Help

[zoomzoomsd]

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!

[June7]

Sounds like bad db design.

Use domain aggregate functions (DLookup, DCount, DAvg, etc) to search directly on tables. Access Help has guidelines.

[Missinglinq]

...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!'

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

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