Break text into separate fields - HELP!!!

[Guest]

Hello -

I have a table with many fields that should not be in just one field. Below is what I have to work with:

040303110211020901110109001100090003991198119809

[The rest of the table consists of about 30 other unrelated fields]

(This should really be split as follows:

0403 0311 0211 0209 0111 0109 0011 009 0003 9911 9811 9809
(These would correspond to):
3/04 11/03 11/02 9/02 11/01 9/01 11/00 9/00 3/00 11/99 11/98 9/98

WHAT A MESS!!!!)

At any rate, I created fields for 3/04, 11/03, 11/02, etc. I need code that goes through each row and when it gets to this field, if there is a value for 0403, for example, it would put a "1" in the 3/04 column for that row; then if it has 0311, it would put a "1" in the 11/03 column and if it does not have a value for any of the above dates, it skips to the next until all the values are represented by a "1" in the corresponding field, if they have a numerical value for same and are null if they don't. (Other rows have other values for this field, e.g. they may have something like 0211011101090011000900039911)

Is this at all possible? Any help will be GREATLY appreciated!

Sandy

P.S. I did get a reply from Graham who has not had a chance to reply again, however Graham pointed out this was not something one would normally do in a normalized database. I am aware of that and wouldn't generally follow this procedure, but I just need to get the information out for the future. The database is going to be scrapped after that.

 

[Allen Browne]

Sandy, the simplest way to clean this up might be to create another table to
receive a record for each valid date. The target table would have a date
field, plus whatever other fields are relevant.

This aircode illustrates how you might loop through all the records in the
original source table, and write to a Date/Time field in the target table.
You can then use DCount() or queries to get the results you want from the
target table.

-----------code begins------------
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim strDate As String

Set db = dbEngine(0)(0)
Set rsSource = db.OpenRecordset("NameOfYourSourceTable")
Set rsTarget = db.OpenRecordset("NameOfYourTargetTable")

Do While Not rsSource.EOF
For i = 0 to Len(rsSource!SourceField) - 1 Step 4
strDate = Mid(rsSource!SourceField, i, 4)
rsTarget.AddNew
rsTarget!DateField = DateSerial(Mid(strDate, 3,2), Left(strDate, 2),
1)
rstTarget.Update
Next
rsSource.MoveNext
Loop

'Clean up
rsTarget.Close
rsSource.Close
Set rsTarget = Nothing
Set rsSource = Nothing
Set db = Nothing
-----------code ends------------

Note: That's completely untested aircode, probably missing brackets and
stuff, but hopefully gives you the idea of how to built this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello -

I have a table with many fields that should not be in just one field.

Below is what I have to work with:

040303110211020901110109001100090003991198119809

[The rest of the table consists of about 30 other unrelated fields]

(This should really be split as follows:

0403 0311 0211 0209 0111 0109 0011 009 0003 9911 9811 9809
(These would correspond to):
3/04 11/03 11/02 9/02 11/01 9/01 11/00 9/00 3/00 11/99 11/98 9/98

WHAT A MESS!!!!)

At any rate, I created fields for 3/04, 11/03, 11/02, etc. I need code

that goes through each row and when it gets to this field, if there is a
value for 0403, for example, it would put a "1" in the 3/04 column for that
row; then if it has 0311, it would put a "1" in the 11/03 column and if it
does not have a value for any of the above dates, it skips to the next until
all the values are represented by a "1" in the corresponding field, if they
have a numerical value for same and are null if they don't. (Other rows
have other values for this field, e.g. they may have something like
0211011101090011000900039911)

Is this at all possible? Any help will be GREATLY appreciated!

Sandy

P.S. I did get a reply from Graham who has not had a chance to reply

again, however Graham pointed out this was not something one would normally
do in a normalized database. I am aware of that and wouldn't generally
follow this procedure, but I just need to get the information out for the
future. The database is going to be scrapped after that.