Update error (3144) on French Access version

[Euler]

Hello,

I'm developing an Access database for international use and during testing a French user got a 3144 (Update syntax) error message that has never appeared in the same database in the U. S. after doing exactly the same steps. And, to make it stranger, there are three update statements in a row (using db.execute) and the debugger only stops on the third one. They are all virtually identical. There are no dates involved.



Unfortunately, at this point I can't load a French version of Access and track it down myself so I'm hoping someone who reads this might have some experience or insight. Thanks.

[Missinglinq]

The only thing that I can think of is that it could be a timing problem that is encountered on the box running the French version but not on the ones stateside, possibly a matter of the speed the particular PC. Is the third Query dependent on the previous Queries? Access is asynchronous, which is to say, if given a series of commands, it starts to execute one, moves on to the next one and starts executing it, and so forth. It doesn't wait for the first command to be completed before starting the second one, and this can cause timing problems.

An example would be a button that runs a series of Queries where all but the first Query is dependent upon the previous Query being completed before it starts to execute. The following VBA code

DoCmd.OpenQuery "QueryA"
DoCmd.OpenQuery "QueryB"
DoCmd.OpenQuery "QueryC"

will immediately run all three, not waiting for one to finish executing before starting the next one. The answer to halting the code in this type of situation is to use DoEvents.

DoCmd.OpenQuery "QueryA"
DoEvents
DoCmd.OpenQuery "QueryB"
DoEvents
DoCmd.OpenQuery "QueryC"

DoEvents returns control to Windows, allowing QueryA to complete running before starting to run QueryB. It then allows QueryB to finish running before starting QueryC.

DoEvents is an easy, safe bet when encountering what seems to be timing issues.

Linq ;0)>

[Euler]

Thanks for replying Missinglinq but each of the Update statements is independent and has nothing to do with the previous one.

[orange]

Is it possible that the french version is using "," as decimal separator and not "."?

Since it relates to syntax, you may want to do a debug.print on the statement and see what you get in the immediate window.
You could then try to copy that output and see if you can run it in the query grid.

Just a thought.

[Missinglinq]

Thanks for replying Missinglinq but each of the Update statements is independent and has nothing to do with the previous one.

Sorry, but I have no other idea, unless orange has hit upon the answer; Access, even in foreign versions, is still very USA-centric, and that might be messing up the SQL statement, if decimals are involved!

All I know about French is that my high school teacher had an incredible pair of legs! Oops! Guess that's not considered PC, today! Sorry!

Linq ;0)>

[Euler]

That's a good thought because within the Update statement is a function call that returns a value that I have formatted using "#,##0.00". But why would the preceding two Update statements with the same embedded function call work and not the third? Does the Format function in French Access not recognize "#,##0.00"?

Anyway, thanks for responding Orange. Your ideas are appreciated.

[orange]

I don't know about Does the Format function in French Access not recognize "#,##0.00"?

I was looking at some sites and I recall high school french and numbers with the different syntax.

http://ux.stackexchange.com/question...currency-codes
and
http://ux.stackexchange.com/question...cultural-thing

[Euler]

I also just did a little research and found out that French Access has no problem with custom formats such as "#,##0.00". It automatically converts to the local format and replaces the decimal with a comma.

I think I'm getting closer to my answer, however. I went in to the Control Panel and changed the format to French and then tried to run my code. I got the error message but only when it tried to update with a decimal. In other words, when the statement reads:

UPDATE SET_Order_Items_Services SET SET_Order_Items_Services.Price_AMP = 270 WHERE (((SET_Order_Items_Services.PK)=6));

It has no problem. It's only when the statement reads something like this:

UPDATE SET_Order_Items_Services SET SET_Order_Items_Services.Price_AMP = 270,9 WHERE (((SET_Order_Items_Services.PK)=6));

that it has an issue. (Notice the comma for the decimal point.) I've tried changing the field's datatype to Single, Double and Decimal and it doesn't update for any of them. I wonder what the French use as a datatype for this type of field and whether or not it could take a number with a decimal point in it. If not, I might have to change it to a text field and convert the text to a number when I need to perform arithmetic operations with it.

[orange]

Have you tried creating a small test proc and Dim a few variables as double, single, long,...
and populate them and see if it rejects or adjusts them? I was thinking just a few variables, perhaps some arithmetic, and a debug.print.

Seems you are getting loser to the issue... good luck. Let us know if you solve it.

[John_G]

Just a thought, because I don't know the answer:

You are writing the statement in SQL, so a question for me is:

Does SQL even recognize the comma as a delimiter, regardless of where it is being used? Does it have a "standard" that dictates that the separator has to be a period?

Could that be the problem?

[Euler]

I solved it by surrounding the function call in the Update statement with the Replace function. It replaces any commas with decimals so the Update statement can execute. Then if I have Windows set to Format = French the number is still stored with a comma in it. It sounds like a flaw in the Update statement to me but, at least, it works now.

Thanks for your interest in my problem.

[John_G]

...the number is still stored with a comma in it.

No, it isn't. The comma separators are how the number is displayed, not how it is stored. The internal storage format is (thankfully) the same no matter where Access is used.

[Euler]

My mistake. Now I know a little bit more about Access. Thank you.

[orange]

Glad you have it resolved. We don't see many posts with issues related to "language specific Access".
I'm sure there are plenty of users in Europe, MidEast and Asia who have a lot of smarts in the area of formatting, deciphering messages in various languages.....