Data Modification - Code Help 1

[pvrrev]

Good morning.....

I am creating an Access Project (SQL 2000) and I need some help in converting a text field to a date field. I have the old field imported from a DBase IV database and I want to convert data to a date field. Here is the data fields from the table CERTMSTR:

1 EXPIRES nvarchar 6 1 (Old Dbase)
0 ExpDate datetime 8 1 (New)

And here is sample data:

EXPIRES ExpDate
200501 1/31/2005
200001 1/31/2000
200001 1/31/2000
200503 3/31/2005

The expires data is read by the user as: yyyy/mm

I would like to write some code that will allow me to take the character date of EXPIRES and move it to the ExpDate field as a mm/dd/yyyy.

Any suggestions would be appreciated as I am one of those Network guys that suddenly found themselves in the role of DBA. Thanks

 

[cmmrfrds]

You can do it in Query Analyzer with an SQL Statement, or make a pass-thru query, or thru ADO. The SQL in any case would look like:

Update yourtable
set expdate = substring(expires,4,2) + '/31/' + left(expires,4)
From yourtable

 

[katdav]

I'll skip the part about gaining access to the data - assuming it's in a recordset.

Dim strYr as String
Dim strMth as String
Do Until rs.EOF
rs.Edit
strYr = Left(rs!Expires,4)
strMth = Right(rs!Expires,2)
Select Case strMth
Case "01"
strDate = strMth & "/" & "31" & "/" & strYr
rs!ExpDate = Format(strDate, "mm/dd/yyyy&quot
Case "02"
strDate = strMth & "/" & "28" & "/" & strYr
rs!ExpDate = Format(strDate, "mm/dd/yyyy&quot
End Select
rs.update
rs.Movenext
Loop

I think this should get you started.
You'll need to take care of leap years.
Have fun,

David