1

I have a MS Access 2007 database. I have run into an issue (and I'm sure more will pop up) with some clients in Columbia and Ecuador where their numbering format is different than the U.S. They use a "." decimal point (or period) character for their Millions and Thousands separator and use a "," comma character for the decimal place. This either causes a type mismatch error, or my own validation catches it as an error, although I'm only using the IsNumeric() function to check the field contents.

I would assume MS Access would recognize whichever regional keyboard format is set up in the users Windows settings.

Can anyone shed some light on this for me? As of right now, the only fix is to change the users keyboard settings to use a "," for the millions and thousands separator and a "." for a decimal.

Any help would be greatly appreciated! :-)

DBS
  • 191
  • 2
  • 14
MultiGuy
  • 912
  • 2
  • 17
  • 34

3 Answers3

1

One solution might be to run a replace() on all relevant fields that translate commas into periods if present.

IAmBatman
  • 687
  • 1
  • 10
  • 20
  • That's a thought... May have to do that. I was sort of hoping for an Access setting I was missing that would actually deal with numbers based on what is set up for region settings in Windows. I mean, Microsoft working with Microsoft? ;-) – MultiGuy Jul 19 '12 at 21:41
1

This should be controlled by the locale settings on the local PC. You can set the decimal separator and number grouping separator, and then Access interacts with 1.000,21 in the same way as 1,000.21 in other locales.

?Format(1000,"currency")
€1.000,00
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Well, that's how I'm dealing with it, but it's seems a shame to have to do that, especially when the users will most likely need to change it back for every other app they're running. Thanks though. – MultiGuy Jul 19 '12 at 21:38
  • No, they should not. The PC should be set up to suit their locale. Access honours the locale. – Fionnuala Jul 19 '12 at 21:39
  • 1
    Well Access does NOT honor the locale otherwise this wouldn't be an issue. Changing their region settings (decimal, grouping separator, etc.) changes them for all programs, does it not? When they send an email to their friend and mention your One Thousand Euros, they want it to read 1.000,00 not 1,000.00. Aren't those global settings for all of Windows? Or am I missing something? – MultiGuy Jul 19 '12 at 21:45
  • I do not quite see your point, I tested by changing my locale to Spain and Access said I had €1.000,00. If I was in Spain, that is what I would want in all applications, including Access. What are you getting? – Fionnuala Jul 19 '12 at 21:53
  • You are correct. If I make the region changes to windows, Access will correctly use those settings. However, so will Excel and any other software the user is running. I'd rather not make a global change to the users computer just for my database. That's all I'm saying. I was wondering if there were another way to deal with this issue in Access only. I do appreciate your input with this. As it is right now, I'm using your suggestion since it was the only way I could think do deal with it. :-) – MultiGuy Jul 19 '12 at 21:58
  • Oh, I see what you're asking... for some reason, in my database, if a user in Spain enters 1.000,00 for a number, it causes a type mismatch error. – MultiGuy Jul 19 '12 at 21:59
  • But their settings cause an error in the database... Access is not accepting a 1.000,00 as a number. It gives a type mismatch error. – MultiGuy Jul 19 '12 at 22:01
  • I have just double-checked and it works for me. If you are testing, it is not sufficient to set the locale to Spain, you need to look at Additional Settings (Windows 7) and set the Decimal Symbol (,) and Digit Group Separator (.) – Fionnuala Jul 19 '12 at 22:09
  • Please read my answer above. I realize what I was doing wrong with the use of an EVAL() function during field validation. You were right! So sorry. ;-) – MultiGuy Jul 19 '12 at 22:47
0

Ok, here is what I found:

First of all, I apologize for saying it was a 'type mismatch'. It wasn't. It was an error #2432: "The expression you entered contains invalid syntax, or you need to enclose your text data in quotes". I realize the error was happening on an EVAL() function, which must be done on a string. I was converting the number to a string first, which was treating the "," as a comma instead of a decimal for those with different regional settings for decimals and group separators. In my case the code was:

If Eval(CStr(cCtl.Value) & ">+" & strMinVal = True Then

So I used IAmBatman's suggestion and did a REPLACE() on the field. Since this is just during the field validation process, it's not changing the actual data, like this:

If Eval(CStr(Replace(cCtl.Value),",",".")) & ">+" & strMinVal = True Then

Remou, you were right all along. My humble apologies. :-)

MultiGuy
  • 912
  • 2
  • 17
  • 34
  • If you are using Eval, you may not be taking the shortest route from A to B. It is rarely a good idea. – Fionnuala Jul 19 '12 at 22:51
  • You're probably right there too. However, my field validation checks for instructions in the .Tag property of the field. It might read: "EVAL >0", or "BETWEEN 10 AND 20". I use the EVAL function to turn the string into an equation. – MultiGuy Jul 19 '12 at 22:58