Concatenate Values using a lookup field

[Sunflower]

I am using Duane Hookom's generic concatenate function... with a twist

I placed the module, and put the following code in the text box on my
main form:


=Concatenate("SELECT APPROVERNAME FROM tblAPPROVALS WHERE WOID =" &
[WOID])

What get is a string of the ID numbers and not the names,


So instead of returning ...


Mike; Cleave; Carol; Kelly


I get ...


196; 275; 24; 348



I understand where the ID numbers are coming from...

My [APPROVERNAME] is a combobox linked to [tblCONTACTS]

ROW SOURCE:
SELECT tblCONTACTS.ContactID, tblCONTACTS.Name FROM tblCONTACTS ORDER
BY [Name];


I just dont know how to code it, to pull the [Name]


Thanks for your help

 

[Chegu Tom]

Hi

Look at your table design?

Are you using lookup fields in your table (for field approvername)? A
lookup field will show the name while storing the ID
Tom

 

[Chegu Tom]

So nobody had an answer to this. Did you find a solution? I could not
find a way. the column() property in combo boxes does not seem to work for
the table. I hope one of the really experienced guys comes back with an
trick to do this.

It is better to avoid this

Try to not use lookup fields they can cause headaches.!!!

Let your table tblApprovals contain the ID number

Use a form with a combo box (calling tblContacts) to do the data entry
functions to that table.

Use a select query that joins tblApprovals with tblContacts to show the
Name when you need it

This is just the first headache you experienced using lookup fields. There
will be more. Redisign before you get too deep

Hi

Look at your table design?

Are you using lookup fields in your table (for field approvername)? A
lookup field will show the name while storing the ID
Tom

I am using Duane Hookom's generic concatenate function... with a twist

I placed the module, and put the following code in the text box on my
main form:


=Concatenate("SELECT APPROVERNAME FROM tblAPPROVALS WHERE WOID =" &
[WOID])

What get is a string of the ID numbers and not the names,


So instead of returning ...


Mike; Cleave; Carol; Kelly


I get ...


196; 275; 24; 348



I understand where the ID numbers are coming from...

My [APPROVERNAME] is a combobox linked to [tblCONTACTS]

ROW SOURCE:
SELECT tblCONTACTS.ContactID, tblCONTACTS.Name FROM tblCONTACTS ORDER
BY [Name];


I just dont know how to code it, to pull the [Name]


Thanks for your help

 

[Chegu Tom]

check this link from a previolus post this week

http://www.mvps.org/access/lookupfields.htm

Hi

Look at your table design?

Are you using lookup fields in your table (for field approvername)? A
lookup field will show the name while storing the ID
Tom

I am using Duane Hookom's generic concatenate function... with a twist

I placed the module, and put the following code in the text box on my
main form:


=Concatenate("SELECT APPROVERNAME FROM tblAPPROVALS WHERE WOID =" &
[WOID])

What get is a string of the ID numbers and not the names,


So instead of returning ...


Mike; Cleave; Carol; Kelly


I get ...


196; 275; 24; 348



I understand where the ID numbers are coming from...

My [APPROVERNAME] is a combobox linked to [tblCONTACTS]

ROW SOURCE:
SELECT tblCONTACTS.ContactID, tblCONTACTS.Name FROM tblCONTACTS ORDER
BY [Name];


I just dont know how to code it, to pull the [Name]


Thanks for your help

 

[Douglas J. Steele]

Assuming APPROVERNAME in tblAPPROVALS is linked to tblAPPROVERS (and that
what's in APPROVERNAME is what's in APPROVERID in tblAPPROVERS), try
something like:

=Concatenate("SELECT tblAPPROVERS.APPROVERNAME FROM tblAPPROVALS " & _
"INNER JOIN tblAPPROVERS " & _
"ON tblAPPROVALS.APPROVERNAME = tblAPPROVERS.APPROVERID " & _
"WHERE WOID =" & [WOID])

 

[Chegu Tom]

I think that tblApprovals is linke to tblContacts (which has the name)

ROW SOURCE:
SELECT tblCONTACTS.ContactID, tblCONTACTS.Name FROM tblCONTACTS ORDER
BY [Name];

Will this code go back and lookup the name from unside the table or should
the inner join be to tblContacts?

Assuming APPROVERNAME in tblAPPROVALS is linked to tblAPPROVERS (and that
what's in APPROVERNAME is what's in APPROVERID in tblAPPROVERS), try
something like:

=Concatenate("SELECT tblAPPROVERS.APPROVERNAME FROM tblAPPROVALS " & _
"INNER JOIN tblAPPROVERS " & _
"ON tblAPPROVALS.APPROVERNAME = tblAPPROVERS.APPROVERID " & _
"WHERE WOID =" & [WOID])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am using Duane Hookom's generic concatenate function... with a twist

I placed the module, and put the following code in the text box on my
main form:


=Concatenate("SELECT APPROVERNAME FROM tblAPPROVALS WHERE WOID =" &
[WOID])

What get is a string of the ID numbers and not the names,


So instead of returning ...


Mike; Cleave; Carol; Kelly


I get ...


196; 275; 24; 348



I understand where the ID numbers are coming from...

My [APPROVERNAME] is a combobox linked to [tblCONTACTS]

ROW SOURCE:
SELECT tblCONTACTS.ContactID, tblCONTACTS.Name FROM tblCONTACTS ORDER
BY [Name];


I just dont know how to code it, to pull the [Name]


Thanks for your help

 

[Douglas J. Steele]

The inner join would be to tblContacts.

=Concatenate("SELECT tblCONTACTS.Name FROM tblAPPROVALS " & _
"INNER JOIN tblCONTACTS " & _
"ON tblAPPROVALS.APPROVERNAME = tblCONTACTS.ContactID " & _
"WHERE WOID =" & [WOID])

Incidentally, Name isn't a good choice for a field name: it's a reserved
word. You should change it.

For a comprehensive list of names to avoid (as well as a link to a free
utility to check your applications for compliance), check what Allen Browne
has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I think that tblApprovals is linke to tblContacts (which has the name)

ROW SOURCE:
SELECT tblCONTACTS.ContactID, tblCONTACTS.Name FROM tblCONTACTS ORDER
BY [Name];

Will this code go back and lookup the name from unside the table or should
the inner join be to tblContacts?

Assuming APPROVERNAME in tblAPPROVALS is linked to tblAPPROVERS (and that
what's in APPROVERNAME is what's in APPROVERID in tblAPPROVERS), try
something like:

=Concatenate("SELECT tblAPPROVERS.APPROVERNAME FROM tblAPPROVALS " & _
"INNER JOIN tblAPPROVERS " & _
"ON tblAPPROVALS.APPROVERNAME = tblAPPROVERS.APPROVERID " & _
"WHERE WOID =" & [WOID])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am using Duane Hookom's generic concatenate function... with a twist

I placed the module, and put the following code in the text box on my
main form:


=Concatenate("SELECT APPROVERNAME FROM tblAPPROVALS WHERE WOID =" &
[WOID])

What get is a string of the ID numbers and not the names,


So instead of returning ...


Mike; Cleave; Carol; Kelly


I get ...


196; 275; 24; 348



I understand where the ID numbers are coming from...

My [APPROVERNAME] is a combobox linked to [tblCONTACTS]

ROW SOURCE:
SELECT tblCONTACTS.ContactID, tblCONTACTS.Name FROM tblCONTACTS ORDER
BY [Name];


I just dont know how to code it, to pull the [Name]


Thanks for your help

 

[Sunflower]

The inner join would be to tblContacts.

=Concatenate("SELECT tblCONTACTS.Name FROM tblAPPROVALS " & _
 "INNER JOIN tblCONTACTS " & _
 "ON tblAPPROVALS.APPROVERNAME = tblCONTACTS.ContactID " & _
 "WHERE WOID =" & [WOID])

Incidentally, Name isn't a good choice for a field name: it's a reserved
word. You should change it.

For a comprehensive list of names to avoid (as well as a link to a free
utility to check your applications for compliance), check what Allen Browne
has athttp://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

I think that tblApprovals is linke to tblContacts (which has the name)

ROW SOURCE:
SELECT tblCONTACTS.ContactID, tblCONTACTS.Name FROM tblCONTACTS ORDER
BY [Name];

Will this code go back and lookup the name from unside the table or should
the inner join be to tblContacts?

Assuming APPROVERNAME in tblAPPROVALS is linked to tblAPPROVERS (and that
what's in APPROVERNAME is what's in APPROVERID in tblAPPROVERS), try
something like:
=Concatenate("SELECT tblAPPROVERS.APPROVERNAME FROM tblAPPROVALS " &_
 "INNER JOIN tblAPPROVERS " & _
 "ON tblAPPROVALS.APPROVERNAME = tblAPPROVERS.APPROVERID " & _
 "WHERE WOID =" & [WOID])
--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)
I am using Duane Hookom's generic concatenate function... with a twist
I placed the module, and put the following code in the text box on my
main form:
=Concatenate("SELECT APPROVERNAME FROM tblAPPROVALS WHERE WOID ="&
[WOID])
What get is a string of the ID numbers and not the names,
So instead of returning ...
Mike; Cleave; Carol; Kelly
I get ...
196; 275; 24; 348
I understand where the ID numbers are coming from...
My [APPROVERNAME] is a combobox linked to [tblCONTACTS]
ROW SOURCE:
SELECT tblCONTACTS.ContactID, tblCONTACTS.Name FROM tblCONTACTS ORDER
BY [Name];
I just dont know how to code it, to pull the [Name]
Thanks for your help- Hide quoted text -

- Show quoted text -

WORKS BEAUTIFULLY!!!
Thanks so much for your help!
I will however change a few things in my naming conventions as you
suggested