UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Convert Text to Number in Access Query?    
 
   
edecke
post Mar 10 2009, 11:50 AM
Post #1

New Member
Posts: 6



Help!
I have a rather convoluted process that provides me with data that is then imported from an Excel 2003 file to a table in Access 2003.
On the Excel sheet, data like "EXC-1" or "EXC-2" (up to "EXC-5") appears in Col H, and some of those cells are blank. These cells are text formatted, and the numeric value is important as it is the number of "EXC" that exist - and must be summed up.
Within another column on the same tab, a formula provides the numeric value. The column with this formula (Col B) has a number format with no decimal places. On a summary tab in Excel, a SUM function adds up these numbers correctly. Here is the Excel formula for populating number into Col B
IF(H2="EXC-1",1,IF(H2="EXC-2",2,IF(H2="EXC-3",3,IF(H2="EXC-4",4,IF(H2="EXC-5",5,"")))))
At this point I am given the spreadsheet to import into Access to do reporting on various things.
When Col B is imported into Access, it imports as Text Format. [If I change the format in the Access table to Number, and try importing again, I get an error message that the file did not import.]
Now to Access 2003
How can I write an expression to convert these text formatted values to numbers so I can get a sum in an Access Query?
Otried an expression with:
CInt (name of field)
But get Type Mismatch.
Help?
Thanks!
Ed
Go to the top of the page
 
+
Godders
post Mar 10 2009, 11:57 AM
Post #2

UtterAccess VIP
Posts: 1,048
From: South Glos, UK



Have you tried the Val() function?
Go to the top of the page
 
+
fkegley
post Mar 10 2009, 12:31 PM
Post #3

UtterAccess VIP
Posts: 23,630
From: Mississippi



You're going to need to isolate the numeric portion of each of the EXC values.
On a query, do this:
Iif(Not IsNull([FieldName]),CInt(Mid$([FieldName], 5)), 0) ' this will convert the trailing digit to an Integer.
Then you can develop another query that uses the first one as its "table" to do the sums.
Null values in the field will be treated as 0
Go to the top of the page
 
+
edecke
post Mar 10 2009, 05:26 PM
Post #4

New Member
Posts: 6



The VAL() function did exactly what I needed, so thank you, Godders!
And fkegley, thank you for your input. I will keep that in mind as well.
Thanks much, both of you!
Ed
Go to the top of the page
 
+
ninedoors
post Mar 26 2009, 09:17 AM
Post #5

New Member
Posts: 3
From: Ontario, Canada



I am having a similar problem but I am not that versed with VB code. I'll try to explain my problem.
download data from our main company server via access. For some reason when the database was designed they made our 'quantity' column as text and not an int. So when I get the data my 'quantity' column is stored as text. I need to sum these values and can not because I have no idea how to convert the column form text to a number.
Oknow how to do it manually but I need to do it thru a query or something in access that I can run in a macro. Right now I have about 4 querys that will run inside my macro and the first is the download and the next one needs to convert the column from text to number then I will be able to run the others.
Does this all make sense?
Thanks in advance
Nick
Go to the top of the page
 
+
Godders
post Mar 26 2009, 09:51 AM
Post #6

UtterAccess VIP
Posts: 1,048
From: South Glos, UK



welcome2UA.gif

Val() should do it for you too. Try this SQL (copy and paste into a query in SQL view)

CODE
SELECT Sum(Val([YourTextField])) AS TxtFieldTotal
FROM YourTable


Change the field and table names to reflect the actual names in your database. You can base the others off this query, or modify existing queries with the Val() function.
Go to the top of the page
 
+
ninedoors
post Mar 26 2009, 09:58 AM
Post #7

New Member
Posts: 3
From: Ontario, Canada



Thanks Godders worked like a charm.
ick
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 30th October 2014 - 10:56 PM

Tag cloud: