0

I have installed windows 10 64 bit with french localization, and installed Microsoft Office, including ms access.

I have this problem when inserting values to Ms Access 2002 table into Currency field, as the value am trying to insert is a decimal value represented through string, with "." operator as the Decimal Separator, as am getting the error message "datatype mismatch in criteria expression".

Is there a way to tell Ms Access to use the "." as the decimal separator instead of "," and the "," as the Thousands Group Separator instead of " ", as replacing the decimal string value of "." character occurrences to "," characters isn't an option as i have thousands of strings that will need accordingly to have the replace function called upon,

What have tried so far, and didn't bring any results, is: 1. Change the decimal symbol, from control panel, regional and language settings from "," to ".". 2. Switching from OleDB to ODBC, and specifying "Locale Identifier" value to 2057, indicating en-GB, in the connection string.

Please Advise,

Thanks in advance.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Yazan
  • 179
  • 2
  • 4
  • 10
  • Import column as text, then do a replacements, then convert to data in your locale. And that will be three different steps. BTW you didn't show us what have you tried so far. You say that "..as the value am trying to insert..". How exactly do you insert those values? – user14063792468 Mar 17 '20 at 15:01
  • Dear @Ярослав Машко, Please notice having mentioned what i tried so far via two steps, also am inserting the values using ODBCCommand by passing parameters to the sql statement as follows: "INSERT INTO Vou (VouAmt1) " & "VALUES (?);" cmdGetIdentity.Parameters.AddWithValue(parameter.Name, strDecimal) Where strDecimal is a string value of Ex: "5800.693" – Yazan Mar 18 '20 at 06:19
  • You should be more specific on your question. All code that you've provided in comments, should'had been moved to the answer. That will make things more clear to those who will try to help you. I've no access to the MS products right now, but what you ask can be done in many other ways. Please be more specific and explain what you have tried in the form of code. – user14063792468 Mar 18 '20 at 09:55

3 Answers3

0
  1. First change the comma to a character other than a period ("@")
  2. Then change any remaining periods to a comma.
  3. Lastly change "@" to a period.
dblValue = Replace(Replace(Replace(OriginalValue, ",", "@"), ".", ","), "@", ".")
Vlado
  • 839
  • 6
  • 16
  • Dear @Vlado, replacing "." to "," in this string prior passing the value to the SQL is not an option as i have thousands of fields to have the "." changed to ",", so am looking for a solution to allow access accept inserting numeric formats with "." rather than "," – Yazan Mar 18 '20 at 07:55
0

Use Str, it will never fail:

Str(AnyDecimalValue)

For a generic solution, use my function CSql.

Edit:

The other way around - to convert a text expression to a decimal - use Val:

Val(TextNumberWithDecimalDot)
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Dear @Gustav, please note that the value am passing to the SQL query is already a string, it's a string representation of a decimal, such as Ex: "5800.693", the problem is that the ms access is installed on french windows and it expects a "," as the decimal separator rather than ".", this string is coming from android application, and converting this string to number prior passing the value to the SQL is not an option as i have thousands of fields to be converted to number, so am looking for a solution to allow access accept inserting numeric formats with "." rather than ",", regards – Yazan Mar 18 '20 at 07:53
  • That makes no sense. An SQL string _does_ expect decimal expressions to have a dot as the decimal separator. So, use your text numbers as they are. Or post your secret code. – Gustav Mar 18 '20 at 13:50
0

You mentioned that the values you are passing to access are strings. Why can't you format those strings in a way you wish them to be, and pass the data to MS access?

You did not mention anything specific, so I assume you work in the notepad.exe. In the above program, format the strings you wish to the values you need by replacing appropriate locale symbols to ones you need. After that pass the strings in some way you "better know how" to the MS access.

If you have a millions or billions of rows(columns?!) in your data, you can do a mass replace in the program of your choise(notepad.exe). This is as simple as that.

user14063792468
  • 839
  • 12
  • 28