Solved

convert text field to number

Posted on 2006-11-30
4
270 Views
Last Modified: 2008-01-09
I have an access linked table to a a sql table and it lists an order qty field as text, even though it is decimal in the sql table.  I am trying to round down (or int) in access but it won't because it is a text field.  I cannot find the right command to convert the text field in the query to number.

Thanks,
dave
0
Comment
Question by:barleyfreak
  • 2
4 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18050049
If IsNumber(TextField) then
    Value = cInt(TextField)
End If

Like that?

AW
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18050053
maybe the function is

IsNumeric(TextField)

AW
0
 
LVL 16

Accepted Solution

by:
GreymanMSC earned 250 total points
ID: 18050535
Use:

  Iif(IsNumeric(TextField), CInt(TextField), 0)

Or:

    CInt(Val(TextField))

Val() will inspect the start of a text field for numeric characters and return a legible number. IE: "10.4A" will return 10.4 .  If no number is found, it will return 0.
0
 

Author Comment

by:barleyfreak
ID: 18050604
thank you,

  Iif(IsNumeric(TextField), CInt(TextField), 0)

did the trick.

cheers,
dave
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now