Syntax error in query

[anchamal]

i'm trying to build a sellect query to remove part of a text field named "imported"

the original text field contains the following " c:\jukebox\REM\LOSSING MY RELIGION.MP3"

how can create the expression in my query to remove the part with the path and leave just the "lossing my religion"

the path c:\jukebox\" is fixed.
so i'm trying to use this expression: Expr: Right(Trim([imported]),11)

but i'm getting invalid syntax error
using ms access 2013

 

[Mihail]

Try to use Mid() function

 

[David R]

Or lookup the custom function InStrRev()

 

[anchamal]

and how will the syntax be?

 

[Mihail]

A bit of Google.
and...
Expr: Mid([Imported], 16, Len([Imported]) - 19)

Not sure that will work in a query.
The best is to define your own function to use here.

 

[anchamal]

when i paste your code i still get the syntax error on the ","

 

[Mihail]

when i paste your code i still get the syntax error on the ","

Use " ; " instead

 

[anchamal]

found the solution
i should use the ";" instead of the ","
because of my computer's settings

thanks

 

[anchamal]

i now need to remove the ".mp3" which is not fixed
it's always at the end but the possition depends on the name of the song
so it changes every time

 

[pr2-eugin]

Would there be any possibility that the File name could have an full stop? Maybe something like?
Will_And_Grace.4x01.the_Third_Wheel_Gets_The_Grace.avi
Or would it always be..
Will_And_Grace_4x01.avi

 

[anchamal]

the file will always finish with ".mp3" i don't place the "." anywhere else in the file

 

[pr2-eugin]

Then try the following code..

Mid(fileStr, InStrRev(fileStr, "."))

Where fileStr is the path of the file..

 

[anchamal]

now i get the result ".mp3"
but i need the oposite.
i need to get only the name of the song without the "c:\jukebox\" and the ".mp3"

 

[anchamal]

with this exp: Mid([Imported],12) i can get rid of the "c:\jukebox\" part which is fixed.
but the .mp3 at the end is not fixed so this is what i trying to get rid of

 

[pr2-eugin]

How about..

Mid(fileName, 1, InStrRev(fileName, ".") - 1)

 

[anchamal]

now i got rid of the ".mp3" but not the "c:\jukebox\"

 

[pr2-eugin]

Okay you need to pass the FileName not the path name.. Try again..

FileName: Mid(pathStr, InStrRev(pathStr, "\") + 1)
PlainFile: Mid(FileName, 1, InStrRev(FileName, ".") - 1)

The above will print something like.. For a path like.. "E:\AWF Work\01 Couch to 5K - Week 1.mp3"

01 Couch to 5K - Week 1.mp3
01 Couch to 5K - Week 1

 

[Mihail]

I think that an external function is the best choice here.
And such a function is more flexible. And you can reuse it in other projects.
I don't know if this will decrease the speed. But I think that the speed is not a requirement for your DB. You can wait more .5 seconds when you run this query.