Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


convert text to number in query

convert text to number in query

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.

RE: convert text to number in query

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.

Hook'D on Access
MS Access MVP

RE: convert text to number in query

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?

RE: convert text to number in query

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.

Hook'D on Access
MS Access MVP

RE: convert text to number in query

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close