table - onefield with two different data - how can i divide & format? 1

[kev510]

External Customer Audit Team
Central Quality Auditing Team

hi everyone,
I am trying to pull data from one table field into two columns of a table in my ASP page. The database table contains FieldA which has data such as shown below -


FieldA
1234567890 : 20080307
2234567891 : 20080308
3234567892 : 20080309

First part of the field will always be 10 digit numbers.
The second part of the field, divided by the " : " will always be dates.

I would like them displayed on my asp page as -

ColumnA ColumnB
1234567890 03/07/2008
2234567891 03/08/2008
3234567892 03/09/2008

Any suggestions or help is appreciated! Thank you!

 

[guitarzan]

You can use the Split command to divide each database row into two parts, and Instr (also Right() and Left() functions) can be used switch the date values around (if they are in a consistent format in the database).

Do While Not rs.EOF
   sFieldA = rs("FieldA")
   arrFieldA = Split(sFieldA, ":")
   [COLOR=green]'Extract the two values from FieldA[/color]
   sColumnA = Trim(arrFieldA(0))
   sColumnB = Trim(arrFieldA(1))
   [COLOR=green]'Fix the date[/color]
   sColumnB = _
      Mid(sColumnB, 5, 2) & "/" & _
      Mid(sColumnB, 7, 2) & "/" & _
      Mid(sColumnB, 1, 4)
   [COLOR=green]'Add a row to your HTML table here...[/color]
Loop

 

[kev510]

amazing.. you even went the extra step to create the array process for me. thank you so much!!

 

[guitarzan]

Your very welcome, thanks for the star.