Slit String

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hello
i got a table with 1 field, and that field as info like this
OLDA000000000000000 000000000000012
OLDA000000000000000 000000000000008
as a string
how do i slit the string like this into two fields and make the second a
numeric
like this
field1 field2
OLDA000000000000000 12
OLDA000000000000000 8

into a new table.

thanks
 
If the format of the string is always the same, then you can use the Left and
Right functions to extract the left and right parts of the string into Field1
and Field2. Additionally, you would use the Val function which converts a
string into a numeric value.

For example, let's say your original string is String1, then to get Field1
you would do Field1 = Left(String1,19) (take the 19 leftmost characters of
String1 and put them into Field1). Then to get Field2 you would do Field2 =
Val(Right(String1,15)) (take the 15 rightmost characters of String1 and
convert them into a numeric value. Make sure you define Field1 as a String
and Field2 as a numeric variable. Val should ignore the leading 0s, but make
sure Right(String1,15) won't contain any non-numeric values because then
you'd get an error.

Michael
 
Assuming your existing field is name MyField, you can extract the two parts
as:

Field1: Left([MyField], InStr([MyField], " ") - 1)
Field2: CLng(Mid([MyField], InStr([MyField], " "))

You can use those expressions in an update query.
 
thanks guys

dont understand very well the second ideia, but i would like to understand a
bit better because i would prefer to use an update query to do this operation

thanks

Douglas J. Steele said:
Assuming your existing field is name MyField, you can extract the two parts
as:

Field1: Left([MyField], InStr([MyField], " ") - 1)
Field2: CLng(Mid([MyField], InStr([MyField], " "))

You can use those expressions in an update query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Roy said:
hello
i got a table with 1 field, and that field as info like this
OLDA000000000000000 000000000000012
OLDA000000000000000 000000000000008
as a string
how do i slit the string like this into two fields and make the second a
numeric
like this
field1 field2
OLDA000000000000000 12
OLDA000000000000000 8

into a new table.

thanks
Click to expand...
Click to expand...
 
thanks guys

dont understand very well the second ideia, but i would like to understand a
bit better because i would prefer to use an update query to do this operation

thanks

Douglas J. Steele said:
Assuming your existing field is name MyField, you can extract the two parts
as:

Field1: Left([MyField], InStr([MyField], " ") - 1)
Field2: CLng(Mid([MyField], InStr([MyField], " "))

You can use those expressions in an update query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Roy said:
hello
i got a table with 1 field, and that field as info like this
OLDA000000000000000 000000000000012
OLDA000000000000000 000000000000008
as a string
how do i slit the string like this into two fields and make the second a
numeric
like this
field1 field2
OLDA000000000000000 12
OLDA000000000000000 8

into a new table.

thanks
Click to expand...
Click to expand...
Click to expand...

The first expression
Field1: Left([MyField], InStr([MyField], " ") - 1)

will find the first space in the field and return everything to the
left of it.

The second expression
Field2: CLng(Mid([MyField], InStr([MyField], " "))

will find that same first space and convert everything to the right of
it to a Long Integer number. Since Access does not save or display
preceding zero's in a Number datatype field, the saved value is the
number itself, as a number datatype.

Look up the InStr, Left, Right, and Mid functions in VBA help files.

Hope this helps.
 
Back
Top