Splitting Strings / Integers

  • Thread starter Thread starter Jake
  • Start date Start date
J

Jake

Hi

I wish to split National Insurance numbers up into individual letters, ie

AA332405B

into

9 separate fields
A A 3 3 2 4 0 5 B

I also need to do this with integers, ie

233 into 2 3 3

Help, as always, would be much appreciated

Jake
 
Hi

I wish to split National Insurance numbers up into individual letters, ie
Click to expand...

Ummm... WHY? Does each digit have its own meaning? You can do it, but
I'm curious why you would want to... see below.
AA332405B

into

9 separate fields
A A 3 3 2 4 0 5 B
Click to expand...

Field1: Mid([NIN], 1, 1)
Field2: Mid([NIN], 2, 1)

etc.
I also need to do this with integers, ie

233 into 2 3 3
Click to expand...

What if the number has four digits, or five, or six? The same Mid()
expression will work, after casting the number into a String, but
Mid(number, 1, 1)

will be the first digit - which might be the ones, tens, hundreds or
thousands place.
 
Thank you John

I needed to do this in order to format the NI number onto a pre-printed
timesheet.

Users will load the printer with the pre-printed stationery and click on
PRINT - unfortunately, the NI number on the form is split into 9 individual
boxes quite far apart, so there was a need to split up the NI number to
format it to put onto report.

Same thing with a week number, which can never be greater than 52, so I
needed to split the 2 digits in order to separate them into separate boxes

Job done, many thanks

!!!

Jake
John Vinson said:
Hi

I wish to split National Insurance numbers up into individual letters, ie
Click to expand...

Ummm... WHY? Does each digit have its own meaning? You can do it, but
I'm curious why you would want to... see below.
AA332405B

into

9 separate fields
A A 3 3 2 4 0 5 B
Click to expand...

Field1: Mid([NIN], 1, 1)
Field2: Mid([NIN], 2, 1)

etc.
I also need to do this with integers, ie

233 into 2 3 3
Click to expand...

What if the number has four digits, or five, or six? The same Mid()
expression will work, after casting the number into a String, but
Mid(number, 1, 1)

will be the first digit - which might be the ones, tens, hundreds or
thousands place.
Click to expand...
 
Using an update query, where [NINum] is the the master
number field and [NIPos1] is the position of the first
character, [NIPos2] is the second chacter, etc., enter in
the update value cell the following:

for [NIPos1] Right([NIPos1],1)

For [NIPosx] Mid ([NIPos2], x,x) where x is the position
of the character in the string.

Check the help function for info on Left(), Mid() and Right
() functions.

Once you get the statements correct, run the query and it
will update the entire table.
 
Back
Top