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

 

[Duane Hookom]

Create a query with tblAPPROVALS and the table containing the actual name
field. Then use the query in place of tblAPPROVALS:

=Concatenate("SELECT [TrueNameField] FROM qselYourQuery WHERE WOID =" &
[WOID])

 

[Lynn Trapp]

Hi Duane,

Wouldn't it work simply enough to do this (I'm admitting I don't know the
code in your concatenate function):

=Concatenate("SELECT C.Name FROM tblContacts AS C Join tblApprovals AS A ON
C.ContactID = A.ApproverName WHERE WOID =" & [WOID])

--
Lynn Trapp

Create a query with tblAPPROVALS and the table containing the actual name
field. Then use the query in place of tblAPPROVALS:

=Concatenate("SELECT [TrueNameField] FROM qselYourQuery WHERE WOID =" &
[WOID])


--
Duane Hookom
Microsoft Access MVP

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

 

[Duane Hookom]

Hi Lynn,
Yes, you could avoid creating a saved query but I didn't know any of the
extra field or table names so I thought it might be less confusing to just
create a saved query and use it in the function.

If the related table and field names would have been provided, I might have
made the same suggestion as you.

(Hope all is well with you and your family. You need to contact me if you
get up north to the Mpls area


--
Duane Hookom
Microsoft Access MVP

Hi Duane,

Wouldn't it work simply enough to do this (I'm admitting I don't know the
code in your concatenate function):

=Concatenate("SELECT C.Name FROM tblContacts AS C Join tblApprovals AS A ON
C.ContactID = A.ApproverName WHERE WOID =" & [WOID])

--
Lynn Trapp

Create a query with tblAPPROVALS and the table containing the actual name
field. Then use the query in place of tblAPPROVALS:

=Concatenate("SELECT [TrueNameField] FROM qselYourQuery WHERE WOID =" &
[WOID])


--
Duane Hookom
Microsoft Access MVP

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

 

[Lynn Trapp]

Hi Duane,

All is going well with us. Actually, now, I will have to contact you if I
get out west. My wife and I have moved to the Pittsburgh, PA area. She's the
new head of the English Department at Waynesburg University in Waynesburg, PA.

Take care.

--
Lynn Trapp

Hi Lynn,
Yes, you could avoid creating a saved query but I didn't know any of the
extra field or table names so I thought it might be less confusing to just
create a saved query and use it in the function.

If the related table and field names would have been provided, I might have
made the same suggestion as you.

(Hope all is well with you and your family. You need to contact me if you
get up north to the Mpls area


--
Duane Hookom
Microsoft Access MVP

Hi Duane,

Wouldn't it work simply enough to do this (I'm admitting I don't know the
code in your concatenate function):

=Concatenate("SELECT C.Name FROM tblContacts AS C Join tblApprovals AS A ON
C.ContactID = A.ApproverName WHERE WOID =" & [WOID])

--
Lynn Trapp

Create a query with tblAPPROVALS and the table containing the actual name
field. Then use the query in place of tblAPPROVALS:

=Concatenate("SELECT [TrueNameField] FROM qselYourQuery WHERE WOID =" &
[WOID])


--
Duane Hookom
Microsoft Access MVP


:

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]

Hi Duane,

All is going well with us. Actually, now, I will have to contact you if I
get out west. My wife and I have moved to the Pittsburgh, PA area. She's the
new head of the English Department at Waynesburg University in Waynesburg, PA.

Take care.

--
Lynn Trapp

Hi Lynn,
Yes, you could avoid creating a saved query but I didn't know any of the
extra field or table names so I thought it might be less confusing to just
create a saved query and use it in the function.

If the related table and field names would have been provided, I might have
made the same suggestion as you.

(Hope all is well with you and your family. You need to contact me if you
get up north to the Mpls area

Hi Duane,
Wouldn't it work simply enough to do this (I'm admitting I don't knowthe
code in your concatenate function):
=Concatenate("SELECT C.Name FROM tblContacts AS C Join tblApprovalsAS A ON
C.ContactID = A.ApproverName WHERE WOID =" & [WOID])
--
Lynn Trapp
:
Create a query with tblAPPROVALS and the table containing the actual name
field. Then use the query in place of tblAPPROVALS:
=Concatenate("SELECT [TrueNameField] FROM qselYourQuery WHERE WOID =" &
 [WOID])
--
Duane Hookom
Microsoft Access MVP
:
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 -

Douglas J. Steele suggested

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


I posted this question in microsoft.public.access.forms as well,
wasnt sure which group was the best to answer my question.

Thanks for your help