Convert text to number

[Guest]

I have a field in a table that combines text and number. Is there a function
that will allow me to break this up into two fields-one integer and one text?
I tried the VALUE and TEXT functions. They don't work at all. I did
stumble on the VAL function which returns a zero. The source field looks
like CAN-8. I want to append to a the data to a table that puts the CAN in
one field and the 8 in another. I use Access 2000 with W2000. thanks in
advance to anyone who can help.

 

[fredg]

I have a field in a table that combines text and number. Is there a function
that will allow me to break this up into two fields-one integer and one text?
I tried the VALUE and TEXT functions. They don't work at all. I did
stumble on the VAL function which returns a zero. The source field looks
like CAN-8. I want to append to a the data to a table that puts the CAN in
one field and the 8 in another. I use Access 2000 with W2000. thanks in
advance to anyone who can help.

If all of the data is in the format of text hyphen number, then you
can use:

TextPart:Left([FieldName],InStr([FieldName],"-")-1)

NumberPart:Val([Mid([FieldName],InStr([FieldName],"-")+1))

 

[John Spencer]

What you want may be doable, but you really need to give us a few more
examples or the rules for the field's values.

Is there always a dash in the field?
Does the dash always separate the letters from the numbers?
Are the letters always three in length?
Is the number always one character long?
etc.

 

[John Vinson]

I have a field in a table that combines text and number. Is there a function
that will allow me to break this up into two fields-one integer and one text?
I tried the VALUE and TEXT functions. They don't work at all. I did
stumble on the VAL function which returns a zero. The source field looks
like CAN-8. I want to append to a the data to a table that puts the CAN in
one field and the 8 in another. I use Access 2000 with W2000. thanks in
advance to anyone who can help.

Val() will convert the *LEFTMOST* characters of a text string to
number, if they make sense as a number - e.g. Val("8-CAN") will return
8, but Val("CAN-8") will not, as you have seen.

What range of formats do you have? Is the numeric portion always one
digit, or always preceded by a hyphen, or can it appear anywhere in
the string? If it's always preceded by a hyphen, you can use

Left([fieldname], InStr([fieldname], "-") - 1)

to get the text portion, and

Val(Mid([fieldname], InStr([fieldname], "-") + 1))

to get the numeric portion. InStr() finds the position of the hyphen,
and the Left() and Mid() functions extract substrings.

John W. Vinson[MVP]