?
Solved

Concatenate Query with AutoNumber in Access 2007

Posted on 2010-01-09
8
Medium Priority
?
360 Views
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?
0
Comment
Question by:dp_gaboi
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 75
ID: 26273618
Where does the number for Rank exis ?

mx
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26274037
Name is a reserved word.  Use something else for your alias, like RankAndName
0
 
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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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.
0
 

Author Comment

by:dp_gaboi
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).  
0
 
LVL 31

Expert Comment

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

Author Comment

by:dp_gaboi
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")
0
 
LVL 31

Accepted Solution

by:
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Implementing simple internal controls in the Microsoft Access application.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 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