Spliting a field in a Query

[JACKSON]

Does anyone know how I could split a field in a query. I have one field that is something like this "code:567.90". I want to create another field in the same query that splits that field and only returns the value to the right of the colon (i.e. "567.90"). I have looked at the right, mid, and left functions, but can't find anything that can just return the value to the right of the delimiter ":".

Thanks Jackson

 

[flixx]

To JACKSON:
Did you try this formula in the query in the field: exp1: Right([code_nm],5),
the [code_nm] is the name I called the field in the table. If you wish to
append this to your current table then select query - update and then
in the update use this formula Right([code_nm],5). To break this down
Right means to only use the characters 5 to the right of the character
string. example code:567.90, the word code: has a character length
of 5. So move over 5 characters then read field data or whats left,
which is 567.90. Hopes this helps, Flixx.

 

[WayneRyan]

Jackson,

Indented for readability ...

NewField: Mid(1, Instr(1, ThatField, ":"), Instr[1, ThatField, ":') - 1) _ 
          Mid(1, Instr(1, ThatField, ":",  + 1)
Or 

NewField: Mid(Instr[1, ThatField, ":"), + 1)

WAyne

 

[JACKSON]

Thanks for the replys.

I can't use the right function, because the field length to the right of the delimiter ":" is not always the same length. But I will try Waynes example, which looks like it is what I need. If worse comes to worse I can do it on the form using a VBA split function, but I was really trying to get the exact output in the query itself. I think Waynes example should work.

Thanks Again!

 

[Mile-O]

NewField: Mid([CurrentField], InStr([CurrentField],":") + 1)

 

[JACKSON]

Thank you SJ, That worked perfectly !