convert a number in "12345" to 12345

[Rumy]

I have a table imported from some other programme which I have no access to.
The data is in text format and has strings. Example "123456789". I need to
convert this to plain number format ie - 123456789. I have read tons of
srticles but no one has given a clear cut answer. Please help and exapling
exactly how to do this in a query. Thanks.

 

[Gummball]

If you simply change the data type from text to number (Table --> Design
view), it will convert everything that is a number to a number and delete
anything that isn't in that field.

 

[KARL DEWEY]

Try this --
New_Number: Val(Replace([Old_Number], "["]", ""))

 

[Rumy]

Thank you Sir. But I am not sure where to enter this formula. Could you be a
little more specific please. This is my data field - Claim_Number -
"01234567899"

Try this --
New_Number: Val(Replace([Old_Number], "["]", ""))

--
Build a little, test a little.

I have a table imported from some other programme which I have no access to.
The data is in text format and has strings. Example "123456789". I need to
convert this to plain number format ie - 123456789. I have read tons of
srticles but no one has given a clear cut answer. Please help and exapling
exactly how to do this in a query. Thanks.

 

[Rumy]

Ahhhhhh i tried that and all the data got deleted. Just to let yo know the
data has 11 digits and all start with a 0.

 

[Duane Hookom]

Karl was assuming based on your initial posting that the quotes were saved in
the field. His suggestion is to create a new column in your query with the
expression he suggested only replacing [Old_Number] with your actual field
name.
--
Duane Hookom
Microsoft Access MVP

Thank you Sir. But I am not sure where to enter this formula. Could you be a
little more specific please. This is my data field - Claim_Number -
"01234567899"

Try this --
New_Number: Val(Replace([Old_Number], "["]", ""))

--
Build a little, test a little.

I have a table imported from some other programme which I have no access to.
The data is in text format and has strings. Example "123456789". I need to
convert this to plain number format ie - 123456789. I have read tons of
srticles but no one has given a clear cut answer. Please help and exapling
exactly how to do this in a query. Thanks.

 

[Rumy]

Thank You Duane. Well that helped me alot. Now i have a query but all the new
fields show a 0. why is that ? any thoughts ,plz ?
Rumy

Karl was assuming based on your initial posting that the quotes were saved in
the field. His suggestion is to create a new column in your query with the
expression he suggested only replacing [Old_Number] with your actual field
name.
--
Duane Hookom
Microsoft Access MVP

Thank you Sir. But I am not sure where to enter this formula. Could you be a
little more specific please. This is my data field - Claim_Number -
"01234567899"

Try this --
New_Number: Val(Replace([Old_Number], "["]", ""))

--
Build a little, test a little.


:

I have a table imported from some other programme which I have no access to.
The data is in text format and has strings. Example "123456789". I need to
convert this to plain number format ie - 123456789. I have read tons of
srticles but no one has given a clear cut answer. Please help and exapling
exactly how to do this in a query. Thanks.

 

[John Spencer]

In a query use the expression
Field: Val([Claim_Number])

that will return the numeric value of the string. It will drop the leading
zero(s) since a number is a number and numbers do not have leading (or
trailing) zeros.

If you want to permanently store the new number.
== Add a new field to the table
== Set its field type to number and choose the appropriate field type
== Use an update query to enter the value in the new field using the
expression above in the UPDATE to row.

NEW query
== Add your table
== Add the new field to the list of fields
== SELECT Query: Update from the menu
== Enter the following in the UPDATE to row under the new field
Val(Claim_Number])
== Select Query: Run from the menu

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you Sir. But I am not sure where to enter this formula. Could you be a
little more specific please. This is my data field - Claim_Number -
"01234567899"

Try this --
New_Number: Val(Replace([Old_Number], "["]", ""))

--
Build a little, test a little.

I have a table imported from some other programme which I have no access to.
The data is in text format and has strings. Example "123456789". I need to
convert this to plain number format ie - 123456789. I have read tons of
srticles but no one has given a clear cut answer. Please help and exapling
exactly how to do this in a query. Thanks.

 

[KARL DEWEY]

Post the SQL of the query by opening in design view, click VIEW - SQL View,
highlight all, copy, and paste in a post.
--
Build a little, test a little.

Thank You Duane. Well that helped me alot. Now i have a query but all the new
fields show a 0. why is that ? any thoughts ,plz ?
Rumy

Karl was assuming based on your initial posting that the quotes were saved in
the field. His suggestion is to create a new column in your query with the
expression he suggested only replacing [Old_Number] with your actual field
name.
--
Duane Hookom
Microsoft Access MVP

Thank you Sir. But I am not sure where to enter this formula. Could you be a
little more specific please. This is my data field - Claim_Number -
"01234567899"


:

Try this --
New_Number: Val(Replace([Old_Number], "["]", ""))

--
Build a little, test a little.


:

I have a table imported from some other programme which I have no access to.
The data is in text format and has strings. Example "123456789". I need to
convert this to plain number format ie - 123456789. I have read tons of
srticles but no one has given a clear cut answer. Please help and exapling
exactly how to do this in a query. Thanks.

 

[Rumy]

Here u go Karl.


SELECT [B5046-5047].CLAIM_NUMBER
FROM [B5046-5047];

Post the SQL of the query by opening in design view, click VIEW - SQL View,
highlight all, copy, and paste in a post.
--
Build a little, test a little.

Thank You Duane. Well that helped me alot. Now i have a query but all the new
fields show a 0. why is that ? any thoughts ,plz ?
Rumy

Karl was assuming based on your initial posting that the quotes were saved in
the field. His suggestion is to create a new column in your query with the
expression he suggested only replacing [Old_Number] with your actual field
name.
--
Duane Hookom
Microsoft Access MVP


:

Thank you Sir. But I am not sure where to enter this formula. Could you be a
little more specific please. This is my data field - Claim_Number -
"01234567899"


:

Try this --
New_Number: Val(Replace([Old_Number], "["]", ""))

--
Build a little, test a little.


:

I have a table imported from some other programme which I have no access to.
The data is in text format and has strings. Example "123456789". I need to
convert this to plain number format ie - 123456789. I have read tons of
srticles but no one has given a clear cut answer. Please help and exapling
exactly how to do this in a query. Thanks.

 

[Rumy]

Wow dat was cool. But still all got in the new filed was 0's.

In a query use the expression
Field: Val([Claim_Number])

that will return the numeric value of the string. It will drop the leading
zero(s) since a number is a number and numbers do not have leading (or
trailing) zeros.

If you want to permanently store the new number.
== Add a new field to the table
== Set its field type to number and choose the appropriate field type
== Use an update query to enter the value in the new field using the
expression above in the UPDATE to row.

NEW query
== Add your table
== Add the new field to the list of fields
== SELECT Query: Update from the menu
== Enter the following in the UPDATE to row under the new field
Val(Claim_Number])
== Select Query: Run from the menu

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you Sir. But I am not sure where to enter this formula. Could you be a
little more specific please. This is my data field - Claim_Number -
"01234567899"

Try this --
New_Number: Val(Replace([Old_Number], "["]", ""))

--
Build a little, test a little.


:

I have a table imported from some other programme which I have no access to.
The data is in text format and has strings. Example "123456789". I need to
convert this to plain number format ie - 123456789. I have read tons of
srticles but no one has given a clear cut answer. Please help and exapling
exactly how to do this in a query. Thanks.

 

[KARL DEWEY]

Try this --
SELECT [B5046-5047].CLAIM_NUMBER, Val(Replace([CLAIM_NUMBER], "["]", ""))
FROM [B5046-5047];


--
Build a little, test a little.

Here u go Karl.


SELECT [B5046-5047].CLAIM_NUMBER
FROM [B5046-5047];

Post the SQL of the query by opening in design view, click VIEW - SQL View,
highlight all, copy, and paste in a post.
--
Build a little, test a little.

Thank You Duane. Well that helped me alot. Now i have a query but all the new
fields show a 0. why is that ? any thoughts ,plz ?
Rumy

:

Karl was assuming based on your initial posting that the quotes were saved in
the field. His suggestion is to create a new column in your query with the
expression he suggested only replacing [Old_Number] with your actual field
name.
--
Duane Hookom
Microsoft Access MVP


:

Thank you Sir. But I am not sure where to enter this formula. Could you be a
little more specific please. This is my data field - Claim_Number -
"01234567899"


:

Try this --
New_Number: Val(Replace([Old_Number], "["]", ""))

--
Build a little, test a little.


:

I have a table imported from some other programme which I have no access to.
The data is in text format and has strings. Example "123456789". I need to
convert this to plain number format ie - 123456789. I have read tons of
srticles but no one has given a clear cut answer. Please help and exapling
exactly how to do this in a query. Thanks.

 

[Rumy]

i am getting the following error msg.
syntex error in querry expression - Val(Replace([CLAIM_NUMBER], "["]", ""))
FROM [B5046-5047];
Thanks.

Try this --
SELECT [B5046-5047].CLAIM_NUMBER, Val(Replace([CLAIM_NUMBER], "["]", ""))
FROM [B5046-5047];


--
Build a little, test a little.

Here u go Karl.


SELECT [B5046-5047].CLAIM_NUMBER
FROM [B5046-5047];

Post the SQL of the query by opening in design view, click VIEW - SQL View,
highlight all, copy, and paste in a post.
--
Build a little, test a little.


:

Thank You Duane. Well that helped me alot. Now i have a query but all the new
fields show a 0. why is that ? any thoughts ,plz ?
Rumy

:

Karl was assuming based on your initial posting that the quotes were saved in
the field. His suggestion is to create a new column in your query with the
expression he suggested only replacing [Old_Number] with your actual field
name.
--
Duane Hookom
Microsoft Access MVP


:

Thank you Sir. But I am not sure where to enter this formula. Could you be a
little more specific please. This is my data field - Claim_Number -
"01234567899"


:

Try this --
New_Number: Val(Replace([Old_Number], "["]", ""))

--
Build a little, test a little.


:

I have a table imported from some other programme which I have no access to.
The data is in text format and has strings. Example "123456789". I need to
convert this to plain number format ie - 123456789. I have read tons of
srticles but no one has given a clear cut answer. Please help and exapling
exactly how to do this in a query. Thanks.

 

[vanderghast]

If I understand you correctly, the data in the field is

Claim Number 01234567899

ie, you have descriptive text in front of your number. If you have no text
AFTER if, the trick would be to spot the last blanc character, and then, to
explicitly cast the final portion of the string to integer:


Int(Mid(FieldName, InStrRev(FieldName, " ")))


should do. Indeed:


? InStrRev("Claim Number 092344", " ")
13

? Int(Mid("Claim Number 092344", 13 ))
92344




Vanderghast, Access MVP

Wow dat was cool. But still all got in the new filed was 0's.

In a query use the expression
Field: Val([Claim_Number])

that will return the numeric value of the string. It will drop the
leading
zero(s) since a number is a number and numbers do not have leading (or
trailing) zeros.

If you want to permanently store the new number.
== Add a new field to the table
== Set its field type to number and choose the appropriate field type
== Use an update query to enter the value in the new field using the
expression above in the UPDATE to row.

NEW query
== Add your table
== Add the new field to the list of fields
== SELECT Query: Update from the menu
== Enter the following in the UPDATE to row under the new field
Val(Claim_Number])
== Select Query: Run from the menu

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you Sir. But I am not sure where to enter this formula. Could you
be a
little more specific please. This is my data field - Claim_Number -
"01234567899"


:

Try this --
New_Number: Val(Replace([Old_Number], "["]", ""))

--
Build a little, test a little.


:

I have a table imported from some other programme which I have no
access to.
The data is in text format and has strings. Example "123456789". I
need to
convert this to plain number format ie - 123456789. I have read tons
of
srticles but no one has given a clear cut answer. Please help and
exapling
exactly how to do this in a query. Thanks.

 

[Rumy]

I amnot sure where to enter this formula..sorry.

If I understand you correctly, the data in the field is

Claim Number 01234567899

ie, you have descriptive text in front of your number. If you have no text
AFTER if, the trick would be to spot the last blanc character, and then, to
explicitly cast the final portion of the string to integer:


Int(Mid(FieldName, InStrRev(FieldName, " ")))


should do. Indeed:


? InStrRev("Claim Number 092344", " ")
13

? Int(Mid("Claim Number 092344", 13 ))
92344




Vanderghast, Access MVP

Wow dat was cool. But still all got in the new filed was 0's.

In a query use the expression
Field: Val([Claim_Number])

that will return the numeric value of the string. It will drop the
leading
zero(s) since a number is a number and numbers do not have leading (or
trailing) zeros.

If you want to permanently store the new number.
== Add a new field to the table
== Set its field type to number and choose the appropriate field type
== Use an update query to enter the value in the new field using the
expression above in the UPDATE to row.

NEW query
== Add your table
== Add the new field to the list of fields
== SELECT Query: Update from the menu
== Enter the following in the UPDATE to row under the new field
Val(Claim_Number])
== Select Query: Run from the menu

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Rumy wrote:
Thank you Sir. But I am not sure where to enter this formula. Could you
be a
little more specific please. This is my data field - Claim_Number -
"01234567899"


:

Try this --
New_Number: Val(Replace([Old_Number], "["]", ""))

--
Build a little, test a little.


:

I have a table imported from some other programme which I have no
access to.
The data is in text format and has strings. Example "123456789". I
need to
convert this to plain number format ie - 123456789. I have read tons
of
srticles but no one has given a clear cut answer. Please help and
exapling
exactly how to do this in a query. Thanks.

 

[raskew via AccessMonster.com]

Hi -

Maybe I'm missing something here, but it would seem that the Val() function
should convert your text 'number' (real numbers don't contain preceding
zeroes) to an integer. Example from debug window:

x = "01234567899"
? val(x)
1234567899
? x / 2
617283949.5

HTH - Bob

I amnot sure where to enter this formula..sorry.

If I understand you correctly, the data in the field is

[quoted text clipped - 62 lines]

 

[vanderghast]

You can enter it in a new query, as a computed expresion. Replace FieldName
with the real field name.

Vanderghast, Access MVP

I amnot sure where to enter this formula..sorry.

If I understand you correctly, the data in the field is

Claim Number 01234567899

ie, you have descriptive text in front of your number. If you have no
text
AFTER if, the trick would be to spot the last blanc character, and then,
to
explicitly cast the final portion of the string to integer:


Int(Mid(FieldName, InStrRev(FieldName, " ")))


should do. Indeed:


? InStrRev("Claim Number 092344", " ")
13

? Int(Mid("Claim Number 092344", 13 ))
92344




Vanderghast, Access MVP

Wow dat was cool. But still all got in the new filed was 0's.

:

In a query use the expression
Field: Val([Claim_Number])

that will return the numeric value of the string. It will drop the
leading
zero(s) since a number is a number and numbers do not have leading (or
trailing) zeros.

If you want to permanently store the new number.
== Add a new field to the table
== Set its field type to number and choose the appropriate field type
== Use an update query to enter the value in the new field using the
expression above in the UPDATE to row.

NEW query
== Add your table
== Add the new field to the list of fields
== SELECT Query: Update from the menu
== Enter the following in the UPDATE to row under the new field
Val(Claim_Number])
== Select Query: Run from the menu

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Rumy wrote:
Thank you Sir. But I am not sure where to enter this formula. Could
you
be a
little more specific please. This is my data field - Claim_Number -
"01234567899"


:

Try this --
New_Number: Val(Replace([Old_Number], "["]", ""))

--
Build a little, test a little.


:

I have a table imported from some other programme which I have no
access to.
The data is in text format and has strings. Example "123456789". I
need to
convert this to plain number format ie - 123456789. I have read
tons
of
srticles but no one has given a clear cut answer. Please help and
exapling
exactly how to do this in a query. Thanks.

 

[vanderghast]

? val("1234C"), val("C1234")
1234 0



so val will work ONLY if the number starts the sequence, not if it is in the
middle or at the end of the sequence.



Vanderghast, Access MVP

Hi -

Maybe I'm missing something here, but it would seem that the Val()
function
should convert your text 'number' (real numbers don't contain preceding
zeroes) to an integer. Example from debug window:

x = "01234567899"
? val(x)
1234567899
? x / 2
617283949.5

HTH - Bob

I amnot sure where to enter this formula..sorry.

If I understand you correctly, the data in the field is

[quoted text clipped - 62 lines]

exapling
exactly how to do this in a query. Thanks.