Mid function not working properly anymore

[BarryVeeke]

Hi,
New to this forum here so forgive me if this has been posted or if i am posting this in the wrong place. I am not a huge access wizard but i am using it quite often. one of my databases which i made and have been using for two years now suddenly gave me wrong results. I was investigating and came to the conclusion that a MID function which i use in a query did not work properly anymore. I made a new DB with just this a mid function in a query and it did not give the (in my opinion) right results, where it did work before.

Here is the problem:
I have this formula in a query:

test: mid("hellohello", 4, 2)

Result should be “lo”. Starting on the 4^th character and returning the next 2. However the result is “lohello”. So it just returns all characters after the 4^th. Also what I noticed now is that when I put a space between the comma and the 4 it gives an error saying:
The expression you entered contains an invalid syntax
You may have entered an operand without an operator

Any thoughts on what causes this?

Thanks!

[alansidman]

test: mid([FieldName],4,2) is the correct syntax for use in a query in a new Field.

[BarryVeeke]

Yes thanks, however this does not give me the correct result. it gives not just the two characters but all characters..

[CJ_London]

the result you are getting implies either the 2 has not been supplied or it is not 2 but for example 20

noticed now is that when I put a space between the comma and the 4 it gives an error saying:
The expression you entered contains an invalid syntax

I presume you mean the comma after the 4 - the querybuilder should just remove it. So the implication is you are not using a hardcoded 2 value but a calculated value of some sort

[BarryVeeke]

Thanks but i do not think that is the problem. I made a totally new databse, with just one query. In the the query I built one field saying test: mid("hellohello",4,2). This results in "lohello". Also if i type it like test: mid("hellohello", 4, 2) (so with spaces) it gives me this error.

[CJ_London]

it works for me as expected. Suggest check your references for any missing ones. You could also zip your test db and upload for others to test.

[BarryVeeke]

I am not sure how to check for missing references. I attached the test database here. My result in this one is "19051" where I expect "19".

Thanks!

[CJ_London]

always better to copy/paste your code rather than free typing it. - Can you see the problem? you have used a dot, not a comma

test: Mid([Table1]![Field1],3.2)

You also don't need [Table1]! - and if you do then better to use a dot

[BarryVeeke]

Thanks, but I certainly did use a comma. Code copied from example: test: Mid([Table1]![Field1],3,2). Or adjusted without the [Table1]!: test: Mid([Field1],3,2)

Still the same result.....

Retyped it in the database explicitly checked if I used commas, still the same. And if I put it like this: test: Mid([Field1], 3, 2) it gives me an error. If I type it like this: test: Mid([Field1], 3,2) it does not give me the error but still the wrong results. So it looks like Access is seeing my last comma as decimal seperator, like it would if was a dot.....

[BarryVeeke]

OK. I changed the list seperator to";" instead of ",". Access changed the first comma to";" but the second one not. So there was an issue. Changed it to ";" and now it works.....

[JoeM]

It is dependent on your Reginal Settings, what it is using as argument separators (delimiters).
Here is the States, the comma is the default. But in other areas of the world, sometimes the semi-colon is used instead.

[CJ_London]

Thanks, but I certainly did use a comma

probably lost in translation if you need to use ; but the code I posted was a copy/paste from the database you provided

[alansidman]

If I change your expression to test2: Mid([Field1],3,2) it works as you expect. As indicated above, you have a period as the second separator. It helps us if you identify where you are in the Location Field of the forum.

[roaftech]

I have found a similar problem with bilingual databases - code developed on an English computer doesn't run properly on a computer with different regional settings. Certain regions (primarily Francophone areas) use the comma as the decimal separator and the full stop (period) as the thousands separator - the opposite of most Anglophone regions. In those regions the semi-colon is used in programming Access rather than the comma. (I cannot recall offhand if it is the same in LibreOffice). Check the regional settings on your computers and adjust your coding as necessary.
Another observation is that some older coding may require certain references to be available. References are found by typing Alt-F11 > Tools > References whilst in an Access window. You may need to have "Microsoft ActiveX Data Objects (Miulti-dimensional) 2.8 Library" activated.