Concatenate Query with AutoNumber in Access 2007

Posted on 2010-01-09
Medium Priority
Last Modified: 2013-11-28
I am creating a phonebook/email address list.  I have the user select a rank from a combo box, then enter first name in a text box, and last name in a text box.

In a query I set the following for the name:  Name: Rank & " " &LastName&", "&FirstName

The query returns the following:  PK_Rank_ID Last, First

"Rank" is a FK from tblRank, so I guess, technically it's returning exactly what I'm asking it to.  But I see the actual rank (i.e. "CPT") in the combo box, not a number, so how do I get the query to look at the right value?
Question by:dp_gaboi
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 75
ID: 26273618
Where does the number for Rank exis ?

LVL 31

Expert Comment

by:Helen Feddema
ID: 26274037
Name is a reserved word.  Use something else for your alias, like RankAndName
LVL 31

Expert Comment

by:Helen Feddema
ID: 26274050
Apart from that, your syntax is peculiar.  If the last name and first name are picked up from textboxes, use this syntax instead:
RankAndName: [Rank] & " " & Forms![frmContacts]![txtLastNameFirst]

Open in new window

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 31

Expert Comment

by:Helen Feddema
ID: 26274056
Or better still, just get the values from a table or query, or saved database properties, which does not require a form to be open.

Author Comment

ID: 26276670
The number for "Rank" is the PK_Rank_ID in tblRank, so, for example, instead of returning "CPT Smith, John" it returns "20 Smith, John" with 20 being the PK Autonumber value associated with "CPT" in tblRank.

Well, the form uses text boxes where the user enters first name and last name (in separate text boxes), then saves it to the database, then, for the actual report that I'll publish as the unit phonebook, I wanted to concatenate the two along with their selected rank based on selected branch of service (cascading combo boxes).  
LVL 30

Expert Comment

ID: 26278776
Give 3 records as the input.
Show the required output.

Author Comment

ID: 26281107
I think you're saying you want me to give an example of the input and what my desired output is.....

(combo box)Service:  Army
(cascading combo box from cboService)Rank: 1SG
(text box) First Name: John
(text box) Last Name:  Smith

Output:  1SG Smith, John

What I'm getting instead is:
Output:  10 Smith, John  (10 is the PK Autonumber assigned to the text field entry "1SG")
LVL 30

Accepted Solution

hnasr earned 1000 total points
ID: 26281796
Check thr Rank Combo box.
Number of columns, and bound colomn.
Try Rank.Column(0) or (1) if Column count is 2

Featured Post

Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question