convert text to number in query

[kimpal]

I am importing a tab delimited file saved from an SAP BI report.
Since I must leave the revenue and cost fields as text in order to import (they come with 'USD' at the end)
I need to convert these values from text to numbers while in the temp table and before I append to the final import table.

I am importing the data into tblTempImport.
I created this query calledl 'qryRemoveUSD'

[UPDATE tblTempImport SET tblTempImport.Revenue = Left([revenue],(Len([revenue])-4));]

its pretty simple yet when I run it I get this error:
Microsoft Access didn't update 2617 field(s) due to type conversion failure, ...........

Why would removing the 4 most right characters of a field create a conversion error ?

Please help.

 

[dhookom]

Removing characters doesn't convert a string into a number. Try:

UPDATE tblTempImport SET Revenue = Val([revenue]);

This should work as long as there are no letters to the left of the numbers.

Duane
Hook'D on Access
MS Access MVP

 

[kimpal]

Thank you Duane! This worked however I also was able to find out how to remove the USD from the SAP Business Warehouse report which I will say here for anyone else looking fof this info: Righth-click on BW report data, properties and check the box for "Display scaling factors for key figures". This moves the USD to the column header.

Now I have one more problem - there are some fields which for some reason are converting to tab delimeted file with quotes around them and these are showing as blank in the database. How can I remove these quotes and still import directly into a table with field as long integer?

 

[dhookom]

I hate that SAP does that. I typically import into a temporary table and then append/update my production tables. I have several solutions where I open the SAP report in Excel and then have code that pushes the data to SQL Server tables.

Duane
Hook'D on Access
MS Access MVP

 

[kimpal]

Yes, I have determined that it is happening on the rows where a blank appears in a column before the revenue column and SAP/BW puts a pound (#) sign. I think this is causing the quotes. Any idea how to suppress the pound sign ? If not I will have to go the temp table route.

 

[dhookom]

I'm not anywhere near proficient with SAP/BW so I can't help :-(

Duane
Hook'D on Access
MS Access MVP