Converting number to text with Access SQL
I have an *.csv file with data. I'm trying to transfer the data to an Access db.
My data look like this
I'm using an sql "INSERT INTO ... SELECT ... FROM"
The construction itself works ok for Access.
The problem comes when I'm trying to convert a value like "99.48%" to a number 99.48
I use REPLACE([Featured Offer (Buy Box) Percentage],'%','') and this returns 99.48, but I get a warning - "data type mismatch" if a destination field has a numerical type. When I change field data type to text then it's ok. But I need numerical, finally like 0.9948
CDbl(REPLACE([Featured Offer (Buy Box) Percentage],'%','')) doesn't works - same warning "data type mismatch"
I will appreciate any help
CSV imports are notorious for localization issues. If the csv has . as decimal separator, but your Access/Windows locale expects ,, it cannot be imported as number.
Your best bet is probably to define an import specification where you can specify the decimal separator . and field separator ,.
- Moving Files in MSOffice Access 2010
- How to format a general date to a distinct month in a combobox dropdown list in Access VBA?
- Date/Time Format VBA Access Query
- Use SQL to combine multiple Crosstab query results into individual Bins (ranges)
- Referring to a subform as recommended produces an error
- Building <select> in Access form
- Method 'columns' of object '_Global' failed error - VBA Access
- how can i extract the form information from a .mdb file?
- Dlookup syntax with multiple criteria including "Like"
- MSCAL.OCX not working on MS Access 2010(64-bit) on Windows 10(64-bit)
- MS Access Listbox click will not register a listbox selection
- Access VBA: How do I make the VBA editor stop auto-filling values?
- VBA Replace “Start” attribute deletes text
- Search by specific date in Access database with VB.net
- Sum Time Field in Access
- What are the Pros and Cons of VBA Code vs Macros in MS Access?
- How to reduce ms access database file size(*.accdb) after deleting data in it (MS Office 2007)
- How do I open an old Microsoft Access adp file with invalid connection strings?
- I need to split a text column in Access that stores dates in MM/YYYY and MM/DD/YYYY format to remove the day format
- Query to Find Adjacent Records in Access 2016
- Access VBA create a new table in an email when the field changes
- Microsoft office Access `LIKE` VS `RegEx`
- What is wrong with the variable?
- Error in odbcConnectAccess2007("BD-Embalses.mdb"). R could not find the function "odbcConnectAccess2007" after installing and loading RODBC
- SQL Learner -- MS Access 2013 ON UPDATE CASCADE and ON DELETE CASCADE
- Why does Access-Vba stops even if AllowSpecialKeys is turned off?
- How to disable access security notice "A Potential security concern has been identified"
- How to speed up the tranfer of information between Excel and Access?
- Access VBA MSHTML document with scripts loads slowly
- Populating subform fields from main form field