Seperating data in records

[Furnurgler]

I'm currently running a query from several linked tables. One of the columns in a table has data in the following format A11A-11-11. I want to split the data into two new coloumns in the query. The first being A11A and the second being 11-11. How can I do this?

Cheers
Georg

 

[WayneRyan]

Georg,

Add two new fields to your query:

NewField1: Mid([SomeString], 1, InStr(1, [SomeString], '-') - 1)

NewField2: Mid([SomeString], InStr(1, [SomeString], '-') + 1)

If your data is inconsistent (some fields don't have the "-"), then
you'll have to add in the IIf statement to handle it.

Wayne

 

[john471]

Assuming the field always has data, and it is always in the stated format (four characters, dash, five characters)

In your query definition grid, insert two new columns where you want the split data.

Column 1:
Field = Part1: Left([YourField],4)

Column 2:
Field = Part2: Right([YourField],5)

To the left of the colon you can use (almost) any name you like, I've used Part1 and Part2.

HTH

Regards

John

 

[john471]

!

WayneRyan beats me to the post (punn fully intended) yet again !

Oh, well, I'll leave my solution posted anyway.

No offence intended to Wayne.

 

[Furnurgler]

Thanks fir the help. I had been putting that statement in the wrong fiel and format. Works a treat.

Cheers
Georg