MID/FIND fuction in access

[Gregorys05]

Hi All,
How would i change this statement in have in excel to be used in Access

MID(Y3,FIND("-",Y3)+1,FIND(".",Y3)-FIND("-",Y3)-1)

Replacing Y3 with, Fname

I have tried just replacing the Y3 Value with fname but access dosen't recognise the Find Function.

 

[Joe4]

access dosen't recognise the Find Function

Check out the INSTR function in Access.
See if you can figure it out using that. If not, post back to this thread.

 

[Gregorys05]

Thanks Joe, I have managed to get

Mid([Files].[Fname],InStr([Files].[Fname],"-")+1,InStr([Files].[Fname],".")-InStr([Files].[Fname],"-")-1)

But now i get an error to say invalid procedure call? i can see the results i want for about three seconds then the message comes up and all the results come back as NAME#.

Any ideas??

 

[Joe4]

Can you provide an example what the value in your field is currently is, and what your expected result should be?

Is the data in an inherent Access table, or is it being linked in from elsewhere?

Are you doing this calculation in a Select Query, or an Action Query?

 

[Gregorys05]

Hi Joe,
The field currently have the value of
"6508005-BATH.JPG"
"6508005-KITCHEN.JPG"
"6508005-ROOFS.JPG"

The values i am after are:
BATH
KITCHEN
ROOFS

The data is from a standard access table and i am just using a select query.

 

[Joe4]

I re-created your situation, and your formula works perfectly fine for me.

I am not sure why it doesn't work for you. Strangely, you said it works for a few seconds before you get that error. Not sure what to make of that.

I would suggest breaking your formula down into bits, and try to mind out exactly when the error is showing up, i.e. start off just with one INSTR function, then add the MID function, etc.

 

[Gregorys05]

Hi i managed to do it in the end using

Mid(Files.Fname,IIf(InStr(Files.Fname,"-")=0,1,InStr(Files.Fname,"-"))+1,IIf(InStr(Files.Fname,".")=0,1,InStr(Files.Fname,"."))-IIf(InStr(Files.Fname,"-")=0,1,InStr(Files.Fname,"-"))-1)

The reason it didnt work was that some of the data didnt have either "-" or "." in them and the Mid fucntion didnt like the zero value it was returing, so i replaced it with 1.

Thanks

 

[Joe4]

The reason it didnt work was that some of the data didnt have either "-" or "." in them

Ah, yes. That would have been helpful to know.

Glad you got it figured out.