By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,192 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,192 IT Pros & Developers. It's quick & easy.

concatenating problem when hyphenated first name

P: n/a
I have a student who has a hyphenated first name. If I concatenate the
name like this: StudentName:([StudentLastName] & ", " &
[StudentFirstName]), it works as expected. If, however, I try to get
the first name first by concatenating like this: StudentName:
([StudentFirstName] & " " & [StudentLastName]), only the first name
appears.

I sure would appreciate any help!

Mary

Mar 13 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
It should work either way.

Do you have any Criteria in this query? There are known problems with
criteria that contains a dash:
http://support.microsoft.com/kb/271661/en-us

What language are you using?
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mary" <mm**********@msn.comwrote in message
news:11**********************@v33g2000cwv.googlegr oups.com...
>I have a student who has a hyphenated first name. If I concatenate the
name like this: StudentName:([StudentLastName] & ", " &
[StudentFirstName]), it works as expected. If, however, I try to get
the first name first by concatenating like this: StudentName:
([StudentFirstName] & " " & [StudentLastName]), only the first name
appears.

I sure would appreciate any help!

Mary
Mar 13 '07 #2

P: n/a
On Mar 13, 10:11 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
It should work either way.

Do you have any Criteria in this query? There are known problems with
criteria that contains a dash:
http://support.microsoft.com/kb/271661/en-us

What language are you using?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mary" <mmcgilliv...@msn.comwrote in message

news:11**********************@v33g2000cwv.googlegr oups.com...
I have a student who has a hyphenated first name. If I concatenate the
name like this: StudentName:([StudentLastName] & ", " &
[StudentFirstName]), it works as expected. If, however, I try to get
the first name first by concatenating like this: StudentName:
([StudentFirstName] & " " & [StudentLastName]), only the first name
appears.
I sure would appreciate any help!
Mary- Hide quoted text -

- Show quoted text -
No, I have no criteria in the query. I get the same results if I
create a query that only lists these fileds.

I am using Access 2003.

Mar 13 '07 #3

P: n/a
Switch the query to SQL View (View menu, from query design.)

Paste the SQL statement here.

I can't understand what's wrong, because I do this kind of thing all the
time.

What language setting do you have under:
Tools | Options | General | New database sort order

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Mary" <mm**********@msn.comwrote in message
news:11**********************@c51g2000cwc.googlegr oups.com...
On Mar 13, 10:11 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
>It should work either way.

Do you have any Criteria in this query? There are known problems with
criteria that contains a dash:
http://support.microsoft.com/kb/271661/en-us

What language are you using?

"Mary" <mmcgilliv...@msn.comwrote in message

news:11**********************@v33g2000cwv.googleg roups.com...
>I have a student who has a hyphenated first name. If I concatenate the
name like this: StudentName:([StudentLastName] & ", " &
[StudentFirstName]), it works as expected. If, however, I try to get
the first name first by concatenating like this: StudentName:
([StudentFirstName] & " " & [StudentLastName]), only the first name
appears.
I sure would appreciate any help!
Mary- Hide quoted text -

- Show quoted text -

No, I have no criteria in the query. I get the same results if I
create a query that only lists these fileds.

I am using Access 2003.
Mar 13 '07 #4

P: n/a
On Mar 13, 11:10 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
Switch the query to SQL View (View menu, from query design.)

Paste the SQL statement here.

I can't understand what's wrong, because I do this kind of thing all the
time.

What language setting do you have under:
Tools | Options | General | New database sort order

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org."Mary" <mmcgilliv...@msn.comwrote in message

news:11**********************@c51g2000cwc.googlegr oups.com...
On Mar 13, 10:11 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
It should work either way.
Do you have any Criteria in this query? There are known problems with
criteria that contains a dash:
http://support.microsoft.com/kb/271661/en-us
What language are you using?
"Mary" <mmcgilliv...@msn.comwrote in message
>news:11**********************@v33g2000cwv.googleg roups.com...
I have a student who has a hyphenated first name. If I concatenate the
name like this: StudentName:([StudentLastName] & ", " &
[StudentFirstName]), it works as expected. If, however, I try to get
the first name first by concatenating like this: StudentName:
([StudentFirstName] & " " & [StudentLastName]), only the first name
appears.
I sure would appreciate any help!
Mary- Hide quoted text -
- Show quoted text -
No, I have no criteria in the query. I get the same results if I
create a query that only lists these fileds.
I am using Access 2003.- Hide quoted text -

- Show quoted text -
Thanks so much for your help, Allen.

I had an index on the field. I removed the index and retyped the first
name in the table. Now it works. This is so strange. I just realized
that there were other hyphenated first names that concatenated
correctly.

Here is my SQL:

SELECT ([StudentFirstName] & " " & [StudentLastName]) AS StudentName
FROM tblEAStudentInformation;

The language setting under:
Tools | Options | General | New database sort order
is "General"
Mary


Mar 13 '07 #5

P: n/a
I can't see anything wrong with that, Mary.

In the next column in query design, enter:
CharCount: Len([StudentFirstName] & " " & [StudentLastName])

Does it return the expected number of characters (including the space)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mary" <mm**********@msn.comwrote in message
news:11**********************@h3g2000cwc.googlegro ups.com...
On Mar 13, 11:10 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
>Switch the query to SQL View (View menu, from query design.)

Paste the SQL statement here.

I can't understand what's wrong, because I do this kind of thing all the
time.

What language setting do you have under:
Tools | Options | General | New database sort order
"Mary" <mmcgilliv...@msn.comwrote in message
>>
news:11**********************@c51g2000cwc.googleg roups.com...
On Mar 13, 10:11 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
It should work either way.
>Do you have any Criteria in this query? There are known problems with
criteria that contains a dash:
http://support.microsoft.com/kb/271661/en-us
>What language are you using?
>"Mary" <mmcgilliv...@msn.comwrote in message
>>news:11**********************@v33g2000cwv.google groups.com...
>I have a student who has a hyphenated first name. If I concatenate
the
name like this: StudentName:([StudentLastName] & ", " &
[StudentFirstName]), it works as expected. If, however, I try to get
the first name first by concatenating like this: StudentName:
([StudentFirstName] & " " & [StudentLastName]), only the first name
appears.
I sure would appreciate any help!
Mary- Hide quoted text -
>- Show quoted text -
No, I have no criteria in the query. I get the same results if I
create a query that only lists these fileds.
I am using Access 2003.- Hide quoted text -

- Show quoted text -

Thanks so much for your help, Allen.

I had an index on the field. I removed the index and retyped the first
name in the table. Now it works. This is so strange. I just realized
that there were other hyphenated first names that concatenated
correctly.

Here is my SQL:

SELECT ([StudentFirstName] & " " & [StudentLastName]) AS StudentName
FROM tblEAStudentInformation;

The language setting under:
Tools | Options | General | New database sort order
is "General"
Mary
Mar 13 '07 #6

P: n/a
On Mar 13, 1:31 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
I can't see anything wrong with that, Mary.

In the next column in query design, enter:
CharCount: Len([StudentFirstName] & " " & [StudentLastName])

Does it return the expected number of characters (including the space)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mary" <mmcgilliv...@msn.comwrote in message

news:11**********************@h3g2000cwc.googlegro ups.com...
On Mar 13, 11:10 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
Switch the query to SQL View (View menu, from query design.)
Paste the SQL statement here.
I can't understand what's wrong, because I do this kind of thing all the
time.
What language setting do you have under:
Tools | Options | General | New database sort order

"Mary" <mmcgilliv...@msn.comwrote in message
>news:11**********************@c51g2000cwc.googleg roups.com...
On Mar 13, 10:11 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
It should work either way.
Do you have any Criteria in this query? There are known problems with
criteria that contains a dash:
http://support.microsoft.com/kb/271661/en-us
What language are you using?
"Mary" <mmcgilliv...@msn.comwrote in message
>news:11**********************@v33g2000cwv.googleg roups.com...
I have a student who has a hyphenated first name. If I concatenate
the
name like this: StudentName:([StudentLastName] & ", " &
[StudentFirstName]), it works as expected. If, however, I try to get
the first name first by concatenating like this: StudentName:
([StudentFirstName] & " " & [StudentLastName]), only the first name
appears.
I sure would appreciate any help!
Mary- Hide quoted text -
- Show quoted text -
No, I have no criteria in the query. I get the same results if I
create a query that only lists these fileds.
I am using Access 2003.- Hide quoted text -
- Show quoted text -
Thanks so much for your help, Allen.
I had an index on the field. I removed the index and retyped the first
name in the table. Now it works. This is so strange. I just realized
that there were other hyphenated first names that concatenated
correctly.
Here is my SQL:
SELECT ([StudentFirstName] & " " & [StudentLastName]) AS StudentName
FROM tblEAStudentInformation;
The language setting under:
Tools | Options | General | New database sort order
is "General"
Mary- Hide quoted text -

- Show quoted text -
Allen,

Ok, I finally figured out what happened and now it all makes sense.
And I feel kind of stupid because it has nothing to do with a
hyphenated field at all; that was a poor assumption on my part.

I went to a backup file and looked at the table with this student's
first name. His name is Kevin-Michael. When I placed my cursor at the
end of his name and hit "delete", the name "Michael" appears. The user
apparently entered Michael, changed their mind and entered "Kevin
Michael" without erasing the first Michael. So the concatenating
appeared not to be working since his first name was so long. When I
deleted the field and retyped the name, it obviously fixed the
problem. What I don't get, though, is why all I saw in the table, even
when I expanded the field, was "Kevin-Michael". The field size is only
25 characters.The extra 'Michael" only showed up when I hit the delete
key.

Anyway, thank you for your help. I read these posts often and you are
always so kind.I have learned so much from you and your website.

Mary


Mar 13 '07 #7

P: n/a
Great! Solved.

It's often sometime that makes sense in the end. And it's neat that you can
enlist Access to help you understand what's happening, like the calculated
query field giving the character count, or using TypeName() or the Immediate
Window (Ctrl+G.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mary" <mm**********@msn.comwrote in message
news:11**********************@h3g2000cwc.googlegro ups.com...
On Mar 13, 1:31 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
>I can't see anything wrong with that, Mary.

In the next column in query design, enter:
CharCount: Len([StudentFirstName] & " " & [StudentLastName])

Does it return the expected number of characters (including the space)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mary" <mmcgilliv...@msn.comwrote in message

news:11**********************@h3g2000cwc.googlegr oups.com...
On Mar 13, 11:10 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
Switch the query to SQL View (View menu, from query design.)
>Paste the SQL statement here.
>I can't understand what's wrong, because I do this kind of thing all
the
time.
>What language setting do you have under:
Tools | Options | General | New database sort order

"Mary" <mmcgilliv...@msn.comwrote in message
>>news:11**********************@c51g2000cwc.google groups.com...
On Mar 13, 10:11 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
It should work either way.
>Do you have any Criteria in this query? There are known problems
with
criteria that contains a dash:
http://support.microsoft.com/kb/271661/en-us
>What language are you using?
>"Mary" <mmcgilliv...@msn.comwrote in message
>>news:11**********************@v33g2000cwv.google groups.com...
>I have a student who has a hyphenated first name. If I concatenate
the
name like this: StudentName:([StudentLastName] & ", " &
[StudentFirstName]), it works as expected. If, however, I try to
get
the first name first by concatenating like this: StudentName:
([StudentFirstName] & " " & [StudentLastName]), only the first
name
appears.
I sure would appreciate any help!
Mary- Hide quoted text -
>- Show quoted text -
No, I have no criteria in the query. I get the same results if I
create a query that only lists these fileds.
I am using Access 2003.- Hide quoted text -
>- Show quoted text -
Thanks so much for your help, Allen.
I had an index on the field. I removed the index and retyped the first
name in the table. Now it works. This is so strange. I just realized
that there were other hyphenated first names that concatenated
correctly.
Here is my SQL:
SELECT ([StudentFirstName] & " " & [StudentLastName]) AS StudentName
FROM tblEAStudentInformation;
The language setting under:
Tools | Options | General | New database sort order
is "General"
Mary- Hide quoted text -

- Show quoted text -

Allen,

Ok, I finally figured out what happened and now it all makes sense.
And I feel kind of stupid because it has nothing to do with a
hyphenated field at all; that was a poor assumption on my part.

I went to a backup file and looked at the table with this student's
first name. His name is Kevin-Michael. When I placed my cursor at the
end of his name and hit "delete", the name "Michael" appears. The user
apparently entered Michael, changed their mind and entered "Kevin
Michael" without erasing the first Michael. So the concatenating
appeared not to be working since his first name was so long. When I
deleted the field and retyped the name, it obviously fixed the
problem. What I don't get, though, is why all I saw in the table, even
when I expanded the field, was "Kevin-Michael". The field size is only
25 characters.The extra 'Michael" only showed up when I hit the delete
key.

Anyway, thank you for your help. I read these posts often and you are
always so kind.I have learned so much from you and your website.

Mary

Mar 14 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.