Appending Records in a table 1

[gxd135]

I have a table which has one column and a bunch of UPC's. The first record, and every record which begins with 99999 is followed by a 3 digit number then a four digit number. What I need to do is append the 3 digit and 4 digit numbers into two new columns for every UPC that follows the 99999 record, until the next 99999 record.

ex:

999996565253
065828285216
095622366266
058413265648
999996566012
065656565656
085484656565
065652626555
055122626266

Final result:

999996565253 656 5253
065828285216 656 5253
095622366266 656 5253
058413265648 656 5253
999996566012 656 6012
065656565656 656 6012
085484656565 656 6012
etc....

I know it is very easy with VB, but I don't know Vb yet, so I am open to suggestions.
I have gone about it by trying a primary number, and have not found a way to autofill the rest of the columns.

Thank you for your time.

 

[JoeMiller]

You can make a quick function that will do this. Go into the modules and make a new module. Paste this into it:

[tt]
Public CurrentUPC As String

Function ValidateUPC(UPC As String) As String

If Left(UPC, 5) = "99999" Then
CurrentUPC = UPC
End If

ValidateUPC = CurrentUPC
End Function
[/tt]

Now in your query, set the second column to this:

[tt]
SecColumn: Mid(ValidateUPC([UPC]),6,3)
[/tt]

and your third column to this:
[tt]
ThirdColumn: Right(ValidateUPC([UPC]),4)
[/tt]

And that should do it. I have a sample DB that I can send you where I have this, email me if you're interested.





Joe Miller
joe.miller@flotech.net