Concatenate Values using a lookup field

  • Thread starter Thread starter Sunflower
  • Start date Start date
S

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


Duane Hookom said:
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


Sunflower said:
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
Click to expand...
Click to expand...
 
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


Lynn Trapp said:
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


Duane Hookom said:
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


Sunflower said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Duane Hookom said:
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


Lynn Trapp said:
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


Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
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



Duane Hookom said:
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.
Click to expand...
If the related table and field names would have been provided, I might have
made the same suggestion as you.
Click to expand...
(Hope all is well with you and your family. You need to contact me if you
get up north to the Mpls area :-)
Click to expand...
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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

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
 
Back
Top