Forum home » Delegate support and help forum » Microsoft Access Training and help » Word order within a field in a query...
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Word order within a field in a query...
Resolved · High Priority · Version 2003
Reply Tue 4 Nov 2008, 16:15Delegate Simon said...
Simon has attended:
Access Intermediate course
Word order within a field in a query...
Is it possible to change the word order when creating a new field within a query, eg:
Field content (not seperate columns in the table): "Simon Gardner"
I want it to say "Gardner, Simon" (the table hasn't been normalised). If there were to different fields i'd concatenate them but as it's only one i need to know if it's possible to manipulate the word order within the one field.
Cheers!
For upcoming training course dates see: Pricing & availability
Reply Tue 11 Nov 2008, 20:21Delegate Mark said...
RE: Word order within a field in a query...
Hi Simon,
I just thought that while you are waiting for a tutor to answer, with hopefully a simpler solution, you may be interested to know that it at least possible. At least using VBA you can use the funtion ParseWord() to return a word (first, last, n-th) from a phrase, e.g. the last word from "Simon Gardner", and then the first. You will be able to display them as you wish by expressing the output variables with & ", "& between them to get the comma and space. As you may know there is more lines of code to be written than that so you have to really know it for it to work. It might be easier to adjust the SQL in a query that gets as close as possible, as similar funtion(s) exist in SQL as you will find by web search on SQL Parse Access word or similar.
Regards, Mark (delegate)
Reply Wed 12 Nov 2008, 08:43Delegate Simon said...
RE: Word order within a field in a query...
Thanks Mark, that'll certainly help in the meantime! Appreciated.
Kind Regards, Simon
RE: Word order within a field in a query...
Hi Simon,
Thank you for your question.
You can create a query and add a calculated field that will use text/string functions such as Left, Right, Mid and Instr.
It would be a very complicated formula and beyond the scope of the forum.
If you require any further assistance on this post, please contact the enquiries team at info@microsoftraining.net.
Regards
Simon
Incorrect enquiries email address
Hi Simon,
In regards to the last email I sent, the correct email address for our enquiries team is info
Sorry for the mistype.
Regards
Simon
Wed 14 Jan 2009: Automatically marked as resolved.
|