Separate Field into Two 2

[wonderwhy]

Hello,

I need to separate a field in a database (text) into two fields. The field (SedativeDose)currently holds values such as:

30 mg
.21 mg
3 mcg
10 mg

I believe the only way to do this would be to separate the numeric from the text. Does anyone know a good way to do this so I can create two new fields from this one (dose and unit)?

Thank you in advance for any help. I'm not real good at code or sql...

Julia

 

[GoDawgs]

Use the InStr function:

left([thefield], Instr([thefield]," &quot)

Something like that...and use the Right function to pull the rest...fool around with that and check out help (only because I know my syntax is wrong). Hope that helps.

Kevin

 

[wonderwhy]

Kevin,

I thought of using the InStr function but I think this will only work if I can count a certain number of characters from either the left or the right. Since it varies, I don't know if this will work. I will try.

Thank you,

Julia

 

[Tranman]

Julia,
Create a new numeric column named DOSE, and a new text column named UNIT, in your table, then create an update query that updates the DOSE column to:

Val([SEDATIVEDOSE])

and updates the UNIT column to:

Right([SEDATIVEDOSE],Len([SEDATIVEDOSE])-InStr([SEDATIVEDOSE]," &quot)

Let me know if you have problems, or need a bit more guidance.

Tranman

 

[GoDawgs]

Instr gives you the position that the blank space occurs...say 3...so if you use it with the left function then you'll only pull the left 3 characters...which I think is what you want.

 

[wonderwhy]

'Worked like a charm! Thank you so much GoDawgs & Tranman.

Julia