decimal separator - how to substitute a "," for a "." to allow correct SQL update?

[chrisbwy]

decimal separator - how to substitute a "," for a "." to allow correct SQL update?

Hi All,

This is driving me crazy please help!!!

My app saves numeric data to an Excel sheet using DAO and the following SQL command.

UPDATE [excelsheetname$cell:cell] SET F1 = data

When the decimal separator in Windows Regional Settings is a "." then all is ok. However if it is a "," then the problems begin.

The data variable in the SQL command above contans the "," character and the UPDATE command throws an error.

Is there a VB function that I can use to change the "," to a "." and still keep the data as numeric? ( I cannot save the data as a string ).

Surely there must be way of working in US / UK default of "."
I've been playing around with double.parse() but not getting anywhere.

Any ideas gratefully received.

Chris

I've posted this on a number of other forums so sorry if you've seen it elsewhere.

 

[WellsCarrie]

UPDATE [excelsheetname$cell] SET F1 = cType(Replace(cType(data, String) ",", "."), proper data type)

Where properdatatype is the nueric type the spreadsheet is expecting (long, integer, float, ect).

Note in the interface I have the requires this type of replacement I also have to first take out any extra "." and "," the user puts into the dataentry field and do a IsNumeric() check before loading the data to the spreadsheet.

Let me know If this doesn't solve your problem.

 

[chrisbwy]

Many thanks WellsCarrie for your suggestions.

Unfortunately it doesn't work.

Whatever I try using cType, Replace etc. the Windows Regional Setting for the decimal separator always overrides the period "."

So when trying to UPDATE with data = a double then the double contains a comma "," instead of a period "." and an error results.

I think I somehow need to temporarily change the Regional Setting decimal separator to a period "." then do the UPDATE then change back.

Any ideas on how to do this?

Chris

 

[WellsCarrie]

Try this....
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrconGlobalizingLocalizingOfficeSolutions.asp

Note that I am probably getting away with my solution because the regional settings change when we open the excel document sent by our
French head quarters to us. To maintain the version of the French Spread sheet I first open the one they sent to the server and then force the user to saveAs the spread sheet (allowing for change in security and ownership of the file) to thier local machine before allowing any processing. My assumption is that I probably don't even need the replace I'm doing in reality since this forced change of "document ownership" probably does the conversion for me before I even read any data.

Of course there is nothing that can be done about forced translations (where the user has unchecked the "use system settings" box in Tools/Options/International/Numbers Handeling) that I know of. Unless the MUI pack (spoken about in the above link) will capture that setting also.

Sorry that I wasn't more help!
Carrie

 

[chrisbwy]

decimal separator - how to substitute a "," for a "." to allow correct SQL update?

Dear Carrie,

Thanks again for your further comments - much appreciated.

Between posts I had the thought to try and change the Regional Settings back to US prior to the SQL Excel UPDATE command that was casuing me all the problems, then revert back to the original users Regional Settings.

That worked ok.

One of the suggested solutions in the link you gave me was exactly that!

So problem not exactly solved but worked around ok.

Regards

Chris