Convert text into a number

[Guest]

I have downloaded data into a system data into a text file. I have imported
this data into access using a link table.

Included in the data from the text file is data in units of measure ie
1,234.567 kg. I have set the data type as text

How do I convert 1,234.567 kg to a numeric (ie 1234.567)

 

[Guest]

Val(Replace("1,234.567 kg",",","")) returns 1234.567

Hope this helps.

 

[Tom Ellison]

Dear JL:

It would work to remove the comma(s) then convert to the numeric type you
desire.

Use Instr() to find the position of commas and concatenate the two parts
before and after the comma. Do this repeatedly till there are no commas
remaining. A function would be a good place to do this.

Tom Ellison

 

[Guest]

Thanks for this. I am relatively new to Access, so I probably did not give
all the details. I am trying to set up a query. This data is held in a table
in a column called "Unit of Measure"

What expression would I need to use in the query to convert this text value
into a numeric value

 

[Guest]

You can use the Val function in conjunction with the Replace function. Here
is an example from the Immediate Window:

?Val (Replace("1,234.567 xx",",",""))
1234.567

The Replace function is needed to remove the comma, since the Val function
recognizes only the period (.) as a valid decimal separator.

In a SELECT query, you can use the following in the Field expression:
NumericPart: Val(Replace([TextField],",",""))


where [TextField] is the name of the field that contains your imported text
data. Make the appropriate substitution in the name of the field. You can use
this in the Update To: row of an Update Query:

Val(Replace([TextField],",",""))

Make sure that the numeric field that you are updating is defined as a
Single. If it is the default Long Integer, you will only get the integer
portion (1234) of the string. If the units of measure (Kg, for instance) are
different in various records, then you'll likely want to use a different
update query to update a UnitsOfMeasure field with this information.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thanks for this Tom

I have tried using this in my version of Access 2000, however I get the
following error message

Undefined function 'Replace' in expression

You can use the Val function in conjunction with the Replace function. Here
is an example from the Immediate Window:

?Val (Replace("1,234.567 xx",",",""))
1234.567

The Replace function is needed to remove the comma, since the Val function
recognizes only the period (.) as a valid decimal separator.

In a SELECT query, you can use the following in the Field expression:
NumericPart: Val(Replace([TextField],",",""))


where [TextField] is the name of the field that contains your imported text
data. Make the appropriate substitution in the name of the field. You can use
this in the Update To: row of an Update Query:

Val(Replace([TextField],",",""))

Make sure that the numeric field that you are updating is defined as a
Single. If it is the default Long Integer, you will only get the integer
portion (1234) of the string. If the units of measure (Kg, for instance) are
different in various records, then you'll likely want to use a different
update query to update a UnitsOfMeasure field with this information.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I have downloaded data into a system data into a text file. I have imported
this data into access using a link table.

Included in the data from the text file is data in units of measure ie
1,234.567 kg. I have set the data type as text

How do I convert 1,234.567 kg to a numeric (ie 1234.567)

 

[Douglas J Steele]

You doing this in a query, or just in VBA?

If it's in a query, it sounds as though you haven't applied all of the
service packs: Office 2000 had this problem when it first came out. The
usual work-around was to develop your own wrapper function for the Replace
function:

Function MyReplace(ToSearch As String, _
FindString As String, _
ReplaceString As String) As String

MyReplace = Replace(ToSearch, FindString, ReplaceString)

End Function

and then use MyReplace instead of Replace.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for this Tom

I have tried using this in my version of Access 2000, however I get the
following error message

Undefined function 'Replace' in expression

You can use the Val function in conjunction with the Replace function. Here
is an example from the Immediate Window:

?Val (Replace("1,234.567 xx",",",""))
1234.567

The Replace function is needed to remove the comma, since the Val function
recognizes only the period (.) as a valid decimal separator.

In a SELECT query, you can use the following in the Field expression:
NumericPart: Val(Replace([TextField],",",""))


where [TextField] is the name of the field that contains your imported text
data. Make the appropriate substitution in the name of the field. You can use
this in the Update To: row of an Update Query:

Val(Replace([TextField],",",""))

Make sure that the numeric field that you are updating is defined as a
Single. If it is the default Long Integer, you will only get the integer
portion (1234) of the string. If the units of measure (Kg, for instance) are
different in various records, then you'll likely want to use a different
update query to update a UnitsOfMeasure field with this information.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I have downloaded data into a system data into a text file. I have imported
this data into access using a link table.

Included in the data from the text file is data in units of measure ie
1,234.567 kg. I have set the data type as text

How do I convert 1,234.567 kg to a numeric (ie 1234.567)