Moving onto a different line by a comma

[CP611]

Hello,


I'm a beginner. Have set up a query to convert one set of data into another. I have an address field which is called Street Address in the input file and an example in here is 6 station road, London. I want to write a query that will cause the output to use a comma to move whatever is after it onto a different line, remove the leading space and ensure all in leading capitals so it would show as the below in the output file to export into Excel.

6 Station Road
London

Thank you!

[Minty]

I would look at the replace function and StrConv functions.

You want to replace ", "
with Chr(10) & Chr(13) - These represent a carriage return.

Replace([YourField],", ", Chr(10) & Chr(13) )
StrConv([YourField],3) - This will convert to Proper Case

You can combine them once they are giving you the correct results.

[CP611]

Hello, thats great thank you. How would you combine the two together?

[Minty]

Do this in a straight forward select query before committing to changing anything.

Select [YourField] , StrConv(Replace([YourField],", ", Chr(10) & Chr(13) )) as NewFieldValue
 from [YourTable]

That will show you before and after values.

[moke123]

You should consider breaking up addresses into their component parts, StreetNumber, StreetName, City, State, Zipcode, etc. Whatever works for a UK address.
You can then use the Law of Propagating Nulls (Anything Plus(+) a null is null) to format the address anyway you want.
something like - (StreetNumber + " ") & (StreetAddress + vbNewLine) & (City + vbNewLIne) - note the uses of + and &.

[June7]

Agree address components should be in separate fields to facilitate filtering and sorting.

[Micron]

Notwithstanding other suggestions, I think the correct order for chr function is chr(13) & chr(10).
Fairly certain that it matters.

[Minty]

Notwithstanding other suggestions, I think the correct order for chr function is chr(13) & chr(10).
Fairly certain that it matters.

Yup, you are dead right, I would edit my posts but I'm too late...