Re: concatenating problem when hyphenated first name
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Wed, 14 Mar 2007 09:16:25 +0900
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" <mmcgillivray@xxxxxxx> wrote in message news:1173809551.298814.161800@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Mar 13, 1:31 pm, "Allen Browne" <AllenBro...@xxxxxxxxxxxxxx> wrote: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...@xxxxxxx> wrote in message
news:1173801954.589933.132460@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> On Mar 13, 11:10 am, "Allen Browne" <AllenBro...@xxxxxxxxxxxxxx>
> 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...@xxxxxxx> wrote in message
>>news:1173797640.397853.216740@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>> > On Mar 13, 10:11 am, "Allen Browne" <AllenBro...@xxxxxxxxxxxxxx>
>> > 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...@xxxxxxx> wrote in message
>> >>news:1173794727.550494.265760@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>> >> >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
.
- References:
- concatenating problem when hyphenated first name
- From: Mary
- Re: concatenating problem when hyphenated first name
- From: Allen Browne
- Re: concatenating problem when hyphenated first name
- From: Mary
- Re: concatenating problem when hyphenated first name
- From: Allen Browne
- Re: concatenating problem when hyphenated first name
- From: Mary
- Re: concatenating problem when hyphenated first name
- From: Allen Browne
- Re: concatenating problem when hyphenated first name
- From: Mary
- concatenating problem when hyphenated first name
- Prev by Date: Double Metaphone
- Next by Date: Totally Off Topic: Dell and Vista
- Previous by thread: Re: concatenating problem when hyphenated first name
- Next by thread: new error message
- Index(es):
Relevant Pages
|
