Problems in spacing data in fields

  • Thread starter Thread starter jack
  • Start date Start date
J

jack

Hello,

I'm currently importing data into Access and am having
problems.

The data I'm pulling in represents choices made my
customers on a question. They must check all that apply to
them and it translates into "01", "02", etc...all the way
to "50". When importing this data it's possible to get
only a small amount such as "011522" which represents the
1st, 15th, and 22nd choices selected or could literally be
"010203040506..."

I need to be able to look at the data and split it by twos
"01", "02", etc...and put that data into a field and have
it properly spaced. For example, if the data imported is
"010306", the field I'm putting this into must look like
this: "01 03 06". Inserting two spaces where any
"gaps" may occur.

Is this even possible?
Thank you in advance,
Jack
 
It may be possible using just queries, but it would be very, very complex. It would be much simpler to write some VBA code to do the job. Pseudo code just off the top of my head would be:

Dim PlaceHolder, NewField

open recordset
read record

Do while not EOF

PlaceHolder = 1
NewField = ""

for n = 1 to 50

if Mid$([FieldName],PlaceHolder, 2) = Format$ (n, "00") then

NewField = NewField & CurrentNumber
PlaceHolder = PlaceHolder + 2

else

NewField = NewField & " " ' Two spaces

endif

next for

record.Field = NewField

update record

Loop

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/Uwe/Forum.aspx/access-externaldata/7106
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=be8280e97b6b494c9b8004ed3ff33e7f
*****************************************
 
Back
Top