Splitting on " " delimiter

[nutritiouspig]

I have this setup of text string:

0123 456

I want to split into two fields using the " " as delimiter, how can i do this?

Thanks
S.

 

[Joe4]

Are you talking about importing a text file into Access? If so, when you go through the Import Wizard, simply select the "Space" radio button as the delimiter.

 

[nutritiouspig]

well, the data is already in access. i know i could export to a text and re-import and delimit .... however .... i need to make this something simple that those who are not so technical don't have to think about it...and its already done on the backend

 

[Joe4]

If it is already in a table, you can use calculated fields in query to pull it apart. The following will split it after the first space it finds:

LeftPart: Left([Table1]![Field1],InStr([Table1]![Field1]," ")-1)

RightPart: Right([Table1]![Field1],Len([Table1]![Field1])-InStr([Table1]![Field1]," "))

Obviously, you will need to substitute in the correct table/field names.