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!
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)
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@stl-training.co.uk
Sorry for the mistype.
Regards
Simon
