MID/Find function

[GCLIFTON]

I could use your help. I am trying to pull out the following data in a table in Access. The data below is an exact example need data retrieve by Key place values # and *. Each different way will go into its own separate column. I will copy and paste the different scenarios. Your help will be appreciated.

213977#PlumbingBathroom-Tub-Drain Kit*U04/4100

I need the Mid/ Find to state the following:

1. Find # and give me every to the Left (213977)

2. Find the # and find the * and give me everything in between (PlumbingBathroom-Tub-Drain Kit)



3. Find the * and give me the next 3 characters (U04)

4. Find the / and give me the next 4 characters (4100) Confirm if this is correct. Right([Field1],4) = 4100

[CJ_London]

you need to use the instr function

e.g.

1 - left(myfield,instr(myfield,"#")-1)
2 - mid(myfield,instr(myfield,"#")+1,instr(myfield,"*" )-instr(myfield,"#"))
3 - mid(myfield,instr(myfield,"*")+1,3)
4 - right(myfield,4)

[GCLIFTON]

i tried 1 and 2 and 3 and they are sending me Syntaz error in expression

Here is my conversion of

2. Mid([Field2],InStr(field2,"#")+1,InStr(field2,"*" )-InStr([Field2],"#"))

[CJ_London]

in what context are you using this? SQL? VBA? controlsource for a control on form or report?

depending on which, you might or might not need the square brackets

and is it really called 'Field2' or something else - in which case what?

[GCLIFTON]

I am actually using this in a table under Expression Builder which should be VBA. Correct?

So should square brackets be used

And yes it is really called "Field2" for now. I haven't named it yet.

[CJ_London]

I am actually using this in a table under Expression Builder which should be VBA

No - although using the same functions, it is sql. And if you are using the expression builder then it would have supplied the square brackets automatically. So if you have freetyped your post and done so inaccurately, for future reference be aware that you will be wasting yours and peoples time trying to resolve the wrong problem.

Although maybe you can (although I suspect your syntax error is saying you can't), you shouldn't be using this in a table. If you do/can, be prepared for plenty of problems later - primary one being that the fields cannot be indexed and I don't believe you can use them in relationships either. You will also get weird failures and errors.

Instead use an update query to update your 4 fields using the calculations above - and yes, use square brackets

[GCLIFTON]

Perfect i will do what you have recommend that makes perfect sense. Thank you!