Split one field in to two - How?

[RichardP1978]

I have a table that holds 250 records, and dont really want to split manually as the saving grace is that each record, has a hyphen that marks the point where the split needs to take place.

For example, the field currently holds

'Movicol sachets - 1 or 2 sachets dissolved in water at 21.30'

That is stored in 1 field, I now need to be stored in to 2 fields, whilst removing the hyphen, and spacing immediately before and after so it works out like this.

Field 1 = Movicol sachets
Field 2 = 1 or 2 sachets dissolved in water at 21.30

I know it can be done using either a query or VBA, but I am not sure which function/procedure/command I need to use to carry this out. Any assistance would be gratefully recieved.

Thanks.

 

[Moniker]

There are a ton of ways to do this with InStr, Split, etc.

Directly in a query, add your existing field that you want to split. Then make two expressions based on that field, like this:

Field1: Your_Field_Name
Field2: =Trim(Left(Your_Field_Name,InStr(Your_Field_Name,"-")-1))
Field3: =Trim(Right(Your_Field_Name,Len(Your_Field_Name)-InStr(Your_Field_Name,"-")-1))

Field2 is left half of the string, and Field3 is the right half.

 

[RichardP1978]

Cool, I will give that a go.

Thanks.

(On a backup copy of the table first! Been there done that........ )

 

[RichardP1978]

OK hitting a brick wall here.

Where do I put the code in for the two fields I want to create?

I have tried having the code in the name of the field so it reads like Expr1:=trim(left........... and I have tried creating the fields and running an update query using the new fields. But I just keep getting blank data or 'invalid procedure call'

Do I need it to be a specific query type? Update, New Table etc?

I just dont understand.

Probably me being stupid!

 

[Moniker]

Just a SELECT query statement should do. I just recreated it and it works fine. See the attached.

 

[RichardP1978]

Got it, dont know why it screwed up there, perhaps I saw a comma instead of a full stop etc. You know what it can be like when you go bosseyed.

I just realised that there is a couple of records which hold co-dydramol, so it removes it there, but manually editing 9 records is better than editing 250

I can now just create the fields in another copied table, copy and paste the records over and voila!

Just need to rejig my frontend to take account for two fields displaying the information that one did, but thats simple enough.

Thanks Moniker!

 

[Moniker]

Glad it worked out.

FYI, you've probably manually fixed the nine records already, but you could've changed the InStr to handle that. Since your splitting dash always had spaces before and after it, as in " - ", but the hyphenated word had no spaces, this would've worked around that:

Field2: =Trim(Left(Your_Field_Name,InStr(Your_Field_Name,"- ")-1))
Field3: =Trim(Right(Your_Field_Name,Len(Your_Field_Name)-InStr(Your_Field_Name,"- ")-1))

All I did was add a space to what is being searched. It's now "- " instead of "-". The way InStr works is that it's looking to match the whole string. In your example, the "-d" from co-dydramol doesn't match the "- " search string, so it would be skipped.

 

[RichardP1978]

I havent done it yet, I am working on 'dead' data at the moment so I dont screw anything up. I am working on the front end now, but will be calling it a night, but thanks, that will be helpful.