Text Box Control Source Query Woes

  • Thread starter Thread starter Han
  • Start date Start date
H

Han

I have a query-based report. The query contains several numeric IDs, which
reference a table containing contacts.

Exp.

PlumberID = 159
PainterID = 135
CarpenterID = 145

For each text box, I would like to display the contact's first and last name
instead of the ugly numeric ID. I have attempted to add the following query
to the text box's Control Source:

=(SELECT [Contacts].[FirstName] & " " + [Contacts].[LastName] AS PlumberName
FROM Contacts WHERE ContactID=PlumberID)

This works as expected when used directly, but displays a #Name? in the text
box.

Can someone clue me in on the problem?

Thanks in advance,
Han
 
I tried adding ContactID to the SELECT data items, but unfortunately the
same problem persists.

=(SELECT [Contacts].[ContactID], [Contacts].[FirstName] & " " +
[Contacts].[LastName] AS PlumberName FROM Contacts WHERE
ContactID=PlumberID)

Still looking for a solution...
 
For each text box, I would like to display the contact's first and last name
instead of the ugly numeric ID. I have attempted to add the following query
to the text box's Control Source:

=(SELECT [Contacts].[FirstName] & " " + [Contacts].[LastName] AS PlumberName
FROM Contacts WHERE ContactID=PlumberID)

This works as expected when used directly, but displays a #Name? in the text
box.
Click to expand...

Try using the "DLookup()" function, instead (watch for line wrap - it's all on
one line):

=DLookup("[FirstName] & ' ' & [LastName]","Contacts","ContactID=" & [PlumberID])
 
Bruce, there is a god! That's it--works great. Thank you!

Ya know, it'd sure be easier (and more intuitive) if the control source
supported queries. It's kind of annoying that Access accepts the query, but
fails at runtime.

In any event, I'm happy there's another option in DLookup. I sincerely
appreciate the help.

Regards,
Han

Bruce M. Thompson said:
For each text box, I would like to display the contact's first and last name
instead of the ugly numeric ID. I have attempted to add the following query
to the text box's Control Source:

=(SELECT [Contacts].[FirstName] & " " + [Contacts].[LastName] AS PlumberName
FROM Contacts WHERE ContactID=PlumberID)

This works as expected when used directly, but displays a #Name? in the text
box.
Click to expand...

Try using the "DLookup()" function, instead (watch for line wrap - it's all on
one line):

=DLookup("[FirstName] & ' ' & [LastName]","Contacts","ContactID=" & [PlumberID])
Click to expand...
 
Back
Top