Replace period with comma in Access
trps
Posted messages
6
Status
Membre
-
MBIROT -
MBIROT -
Hello,
Hello,
I'm having a problem with Access, a software about which I know very little! (and that’s putting it mildly).
So, I need to import an Excel file into Access.
The problem is that my amounts use a point instead of a comma.
Is there a query to replace them all?
Thank you for your help
Hello,
I'm having a problem with Access, a software about which I know very little! (and that’s putting it mildly).
So, I need to import an Excel file into Access.
The problem is that my amounts use a point instead of a comma.
Is there a query to replace them all?
Thank you for your help
Configuration: Windows XP Internet Explorer 6.0
4 réponses
Hello,
There are several ways to do it:
The simplest would be to do a "replace all" in Excel
But if you cannot modify the source Excel file, import this column as Text into an Access table
Just create an update query on your imported table
In visual mode:
Field: YourColumnName
Table: YourTableName
Replace with: Replace([YourTableName]![YourColumnName];".";"" )
There are several ways to do it:
The simplest would be to do a "replace all" in Excel
But if you cannot modify the source Excel file, import this column as Text into an Access table
Just create an update query on your imported table
In visual mode:
Field: YourColumnName
Table: YourTableName
Replace with: Replace([YourTableName]![YourColumnName];".";"" )
Go to the regional options of Windows (start/control panel/region and language in seven)
go to advanced settings in the format tab.
Replace the defined decimal symbol, which is the comma, with a point.
AND validate.
Make a copy of the concerned table.
Go to properties and in data type, replace text with numeric, remembering to set real in the format type (at the bottom of the window, in the field property section). Validate. After 2 warning messages, the data has become numeric.
Return to the regional options to revert to the initial settings.
When opening the access table, the data is numeric with a decimal point.
go to advanced settings in the format tab.
Replace the defined decimal symbol, which is the comma, with a point.
AND validate.
Make a copy of the concerned table.
Go to properties and in data type, replace text with numeric, remembering to set real in the format type (at the bottom of the window, in the field property section). Validate. After 2 warning messages, the data has become numeric.
Return to the regional options to revert to the initial settings.
When opening the access table, the data is numeric with a decimal point.
Still no small idea?
I was advised to do this:
You need to create a module.
In this module, paste this:
Public Function Replace(ByVal Text As String) As String
Replace = Replace(Text, ".", ",")
End Function
Then you create an update query:
UPDATE Your_Table SET [Your_Table].[Your_Field] = Replace([Your_Table].[Your_Field]);
And it doesn't work.
I'm not very good with queries; I must be doing something wrong.
Could someone explain the steps to me?
Please
I was advised to do this:
You need to create a module.
In this module, paste this:
Public Function Replace(ByVal Text As String) As String
Replace = Replace(Text, ".", ",")
End Function
Then you create an update query:
UPDATE Your_Table SET [Your_Table].[Your_Field] = Replace([Your_Table].[Your_Field]);
And it doesn't work.
I'm not very good with queries; I must be doing something wrong.
Could someone explain the steps to me?
Please