International Number Problems

[Kardan]

Hi,

I am creating an application for use within Europe which has forms allowing
users to paste data from external sources using the following code, this is
to prevent the user trying to paste many records into a single field.

Dim rst As Recordset

Me.SubFrm.SetFocus

Set rst = Me.SubFrm.Form.RecordsetClone

With rst
If Not .BOF And Not .EOF Then DoCmd.GoToRecord , , acNew
End With

rst.Close

Set rst = Nothing

DoCmd.RunCommand acCmdSelectRecord

DoCmd.SetWarnings False

DoCmd.RunCommand acCmdPaste

DoCmd.SetWarnings True

Me.SourceSelect.SetFocus

I do not have the option of retrieving the data with queries, as the sources
come from external locations where we have been unable to have the format
fixed completely with referenced data ranges.

The application was built on my machine with the region set to UK (comma as
thousands seperator and period as decimal seperator) and has no problems
pasting numbers. However, if this form is used by a client with European
number settings (period as thousands seperator and comma as decimal seperator)
Access mis-interprets the numbers and places decimals in the wrong place.

Example with 5,000.00 (Five thousand)
My Machine, shown in Excel as 5,000.00 interpreted in Access as 5,000.00
Client Machine, shown in Excel as 5.000,00 interpreted in Access as 5.00000

I do not have the option of fixing anything one way or the other due to
different machines being used with different settings.

Any answers on how to overcome this problem would be greatly appreciated.
Code can be used as the pasting is currently done with code, although the
actaul machine settings cannot be changed.

 

[miciomao]

Dim valore As String
valore = "5,000.00"
valore = Replace(valore, ".", ",")
valore = Replace(valore, ",", ".", , 1)
MsgBox valore

 

[Kardan via AccessMonster.com]

Hi miciomao

Thanks for your response.

I do not think you have read my OP correctly on two points;

1. The data is being pasted in, so I cannot intervene and manipulate the data

2. I cannot guarantee the format that is coming in (UK or Euro). If I used
your suggestion and the user had Euro settings this would increase the
problem.

What I need is a way to temporarily change the number setting to my choice
whilst the data is being pasted.

Regards

Richard