InStr function

[LaraC]

Hello everybody,

I can't get the InStr function to work in Microsoft Access.
It reports that I have entered an operand without an operator whenever I use it in Tables and Queries.

Your advice will be most welcome.

 

[swift412]

What is the name of the field you're using InStr on?

IIRC, Access is really finicky about punctuation and other non-alphanumeric characters.

 

[rustypup]

It's Access.

Have you tried sacrificing your first born to Satehen?

 

[Pineapple Smurf]

Yeah like Excel won't accept a . in our currency

ie R14.50 doesn't work
but R14,50 does work

Frigging American Logic

 

[LaraC]

What is the name of the field you're using InStr on?

IIRC, Access is really finicky about punctuation and other non-alphanumeric characters.

It is a product ID.

It's formatted with numbers, a forward slash, number for the month, another forward slash and the year.
e.g. 12346789/2/2021

I would like to extract the number before the first slash.

 

[rustypup]

Yeah like Excel won't accept a . in our currency

ie R14.50 doesn't work
but R14,50 does work

Frigging American Logic

Regional settings

 

[Pineapple Smurf]

Regional settings

Please explain

 

[LaraC]

Please explain

Windows 10 default for list separators differ from region to region.

 

[Pineapple Smurf]

Windows 10 default for list separators differ from region to region.

Ah cool, Im on MAC

 

[rustypup]

It is a product ID.

It's formatted with numbers, a forward slash, number for the month, another forward slash and the year.
e.g. 12346789/2/2021

I would like to extract the number before the first slash.

instr returns a position... not a text string?

instr(1,myField,"/") is the position

 

[LaraC]

instr returns a position... not a text string?

instr(1,myField,"/") is the position

I know. I want to find the position of the slash and combine it with the Left function.

Left([MyField],(InStr(1,"MyField","/",1))

 

[rustypup]

I know. I want to find the position of the slash and combine it with the Left function.

Maybe post the code segment?

 

[swift412]

TestInstr: Left([ProductID], InStr([ProductID], "/") - 1)

 

[LaraC]

TestInstr: Left([ProductID], InStr([ProductID], "/") - 1)

Did it work?

Which version of access are you using?

 

[swift412]

Did it work?

Which version of access are you using?

Yep, works fine on my side. Access 2016. Double check that the brackets line up in your formula, you might have missed a closing bracket

 

[LaraC]

Yep, works fine on my side. Access 2016. Double check that the brackets line up in your formula, you might have missed a closing bracket

I don't believe it. I copied your formula and now it works.

Thank you very much.

 

[Nod]

I would suggest, if possible, to change your Access backend to MariaDB.
You still use the Access as frontend, but backend is then much stabler and provide a nice performance boost.

 

[LaraC]

I would suggest, if possible, to change your Access backend to MariaDB.
You still use the Access as frontend, but backend is then much stabler and provide a nice performance boost.

Thank you for the suggestion. However, the wizards in Access makes everything so easy to use.

 

[Nod]

Thank you for the suggestion. However, the wizards in Access makes everything so easy to use.

I completely understand.
Maybe an option if more people start using this.
If you are the sole user, then its not an issue. Had a lot corruption when many people use the same access DB, especially remotely over unstable connections.