Can I convert numbers stored as text back to numbers in MS Access

  • Thread starter Thread starter CUserM
  • Start date Start date
C

CUserM

I have numbers that are exported from an online system in text format. I
need to use the numbers as integers in my Access work. I can find a way to
change the text data to numbers in Excel but haven't found a way to do that
in Access. I'd appreciate any help.
 
I have numbers that are exported from an online system in text format. I
need to use the numbers as integers in my Access work. I can find a way to
change the text data to numbers in Excel but haven't found a way to do that
in Access. I'd appreciate any help.
Click to expand...

The only data in the text field are numbers?

=Val([TextField])

will convert "123" to 123.
 
Sure thing. There's a bunch of type conversion functions in Access VBA.

CInt will change a string to an interger. Use only if number range is
between -32,000 and + 32000

CLng converts to bigger integers.

CDbl if you want decimal points.

In a query put something like this in the field row:

ToNumber: CLng([TheFieldName])
 
Thanks to Jerry & Fred. I tried both the Val and CLng functions but continue
to get a type conversion failure with each function. I see 10 digits but am
beginning to think I have some hidden code or digit with the text/numbers I
can see. Any other suggestions? Thx

Jerry Whittle said:
Sure thing. There's a bunch of type conversion functions in Access VBA.

CInt will change a string to an interger. Use only if number range is
between -32,000 and + 32000

CLng converts to bigger integers.

CDbl if you want decimal points.

In a query put something like this in the field row:

ToNumber: CLng([TheFieldName])
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


CUserM said:
I have numbers that are exported from an online system in text format. I
need to use the numbers as integers in my Access work. I can find a way to
change the text data to numbers in Excel but haven't found a way to do that
in Access. I'd appreciate any help.
Click to expand...
Click to expand...
 
If you use
Asc(Mid([YourField],1,1))
you will see the Ascii code for the first position of the text field.
Second position is
Asc(Mid([Plan],2,1)) and so forth.

Setting criteria to show (un)acceptable values could expose the
culprits - maybe something like
Not Between 16 And 128
 
CLng and other type conversion functions will throw that error if you have
zero lenght strings. This is different than nulls; rather it's "". Run a
query with "" in the criteria and see what happens. Also CLng can't handle
any text including leading spaces.

However Val doesn't have that problem with ZLS or text. However both Val and
CLng will throw an "94 Invalid Use of Null" error if there are nulls in the
field.

I couldn't find a way to get a type conversion error with Val in some
limited testing. Are you sure that's the error? Please copy and paste what
you are using in the query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


CUserM said:
Thanks to Jerry & Fred. I tried both the Val and CLng functions but continue
to get a type conversion failure with each function. I see 10 digits but am
beginning to think I have some hidden code or digit with the text/numbers I
can see. Any other suggestions? Thx

Jerry Whittle said:
Sure thing. There's a bunch of type conversion functions in Access VBA.

CInt will change a string to an interger. Use only if number range is
between -32,000 and + 32000

CLng converts to bigger integers.

CDbl if you want decimal points.

In a query put something like this in the field row:

ToNumber: CLng([TheFieldName])
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


CUserM said:
I have numbers that are exported from an online system in text format. I
need to use the numbers as integers in my Access work. I can find a way to
change the text data to numbers in Excel but haven't found a way to do that
in Access. I'd appreciate any help.
Click to expand...
Click to expand...
Click to expand...
 
Keep NZ open as an option when dealing when encountering Null in
numeric regards, although it doesn't sound likely as the problem in
this thread, without Error 94 as you pointed out.

Jerry - I don't following that "leading spaces" confounds CLng. What
is that restriction? (E.g. CLng(" 3 ") equals 3)
 
Good catch. You are correct. I misspoke. I should have said that zero length
strings OR just spaces will cause the error. The following will cause a type
mismatch.

Debug.Print CLng(" ")

Of course so will the following:

Debug.Print CLng("A")
 
I just discovered that my CLng conversion error was caused by the Account numbers I was trying to convert were simply too large numerically.
 
I tried Tonumber and val and Clng but any of these come up with Undefined function '...' in expression. I am using access 2016 if this makes any difference.

I too am trying to convert a string to a number to be able to link the field to another table which is in a number format.
 
Jerry Whittle said:
Sure thing. There's a bunch of type conversion functions in Access VBA.

CInt will change a string to an interger. Use only if number range is
between -32,000 and + 32000

CLng converts to bigger integers.

CDbl if you want decimal points.

In a query put something like this in the field row:

ToNumber: CLng([TheFieldName])
Click to expand...

In my case I am a singer and have a database that I downloaded of the catalog of music. There are 36.000+ records.
For the BPM (Beats Per Minute) is currently a TEXT field called BPM.

Some records has the word 'track' in the BPM field, some numbers are positive numbers, others are negative numbers, meaning that there is a - sign in front of a lot of the numbers. They look like this:

track
96
102
-84
-62

The ones that say 'track' should stay 'track'. For the rest of them, they should look like this:


96
102
84
62

I created a second field called BMPNumber as a number field. What should I do?
 
Back
Top