Solved question: query with multiple parameters from the same table

[Vasha77]

The following example is more or less the task that I'm actually doing, but with some irrelevancies removed. Suppose I have a table of surnames (the table is named Surnames) where each name is represented by a numeric code.

Surname ID
Jones 18573
Hall 27555

I want to use parameters to create coded descriptions for hyphenated names in this form: Jones-Hall is 18573 1 27555 2. The user should type in "Jones" as [Enter First Part] and "Hall" as [Enter Second Part] and have the computer look up the codes for those names and create the description. Obviously, this would be different from the description for Hall-Jones. So how would I write this query?

If I was choosing just one value from the table, to make "Jones" output to 18573 1, I think I would write

SELECT Surnames.[ID] & " 1" AS Output FROM [Surnames] WHERE (([Surname] Like [Enter Name]));

but I don't see how to choose multiple values in a fixed order.

Thanks for help!

 

[theDBguy]

Hi. Welcome to AWF! Maybe it's possible to do what you want using a query, but I would probably recommend using a custom function instead. Just a thought...

 

[Vasha77]

Thanks! Not quite what I wanted to hear when I've never looked at a line of VBA in my life but I'll check into it.

 

[theDBguy]

Thanks! Not quite what I wanted to hear when I've never looked at a line of VBA in my life but I'll check into it.

How many parameters will you ever need? And, will it always be the same number or sometimes less?

 

[Vasha77]

Always exactly two parameters.

 

[theDBguy]

Always exactly two parameters.

Then, maybe you can just use DLookup(). For example:

SELECT DLookup("[ID]","Surnames","[SurName]='" & [Enter Surname 1] & "'") & " 1" AS Col1,
    DLookup("[ID]","Surnames","[SurName]='" & [Enter Surname 2] & "'") & " 2" AS Col2
FROM Surnames

(untested)
Hope that helps...

 

[Vasha77]

That works great, although it also needs a DISTINCT. DLookup criteria are powerful... Thanks again!

 

[theDBguy]

That works great, although it also needs a DISTINCT. DLookup criteria are powerful... Thanks again!

Hi. Glad to hear you got it to work. Good luck with your project.

 

[The_Doc_Man]

although it also needs a DISTINCT

Suddenly you just walked into Never-Never Land. If this lookup table of last names isn't based on uniqueness, then it shouldn't exist at all. If the table only lists non-unique last names, why bother with it? What is the value of the table, long-term? What else, if anything, is there? And when going after that ID number, if the table is non-unique, what do you use to differentiate between two records with the same last name but different ID numbers?