Split a string into two columns in the table

[DBL]

Morning - I hope you are all well. Not getting as much of a chance to develop in Access any more and a bit rusty.

I use a browse function to let us capture a file name and location and save it into a table. The name is made up of two parts, the job number, and the file name. Any spaces have been replaced with underscores prior to this process so a file name will look like : 8315_Dawn_Lawson_interview (Job ID_File name) I want to be able to split the job number from the rest of the file name and save them into two separate fields and then join them back together when I need to for reporting purposes.

Can anyone point me in the right direction?

Many thanks

 

[gemma-the-husky]

if the number is always at the start, then use instr to find the position of the first space

the number is the left of the instr position (if the value is always at the start, you can use val instead, but you still need instr for the rest of the string.

the rest of the string is the right (or more easily the mid) of the string

 

[DBL]

That's a great starting point, thank you. Yes, the number is always at the start /left of the first space.