VB script to format date

[hinkwale]

I have a table in access 2007

sample table

Column A Column B Column C

Servername</SPAN>	Backup Date</SPAN>	Expiration</SPAN>
cmc2asff1-netbackup</SPAN>	20030927205029000</SPAN>	20110501030000000</SPAN>
cmc2asff1-netbackup</SPAN>	20031104121955000</SPAN>	20110501030000000</SPAN>
cmc2asff1-netbackup</SPAN>	20040328092657000</SPAN>	20110501030000000</SPAN>
cmc2asff1-netbackup</SPAN>	20060702042604000</SPAN>	20090701082604000</SPAN>
cmc2asff1-netbackup</SPAN>	20060728230901000</SPAN>	20090728030901000</SPAN>
cmc2asff1-netbackup</SPAN>	20060825231204000</SPAN>	20090825031204000</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

Iwould like to have a macro that formats Columns B & C to for example

09/27/2003 20:50:29 Thanking you in anticpation for your help

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[Joe4]

You didn't mention the data type currently on the field (Text or Numeric?).
And you didn't mention if you need it to be returned in a Date format of Text format (if you are doing any sort of calculations on the field, you may need it to be Date/Time).

Here is a calculated field you can use in a query that will return it as Text exactly like you want it to look:
Calc_Date: Mid([BackupDate],5,2) & "/" & Mid([BackupDate],7,2) & "/" & Left([BackupDate],4) & " " & Mid([BackupDate],9,2) & ":" & Mid([BackupDate],11,2) & ":" & Mid([BackupDate],13,2)