Module to convert field to a date format unsuccessful

I have a table in Access that contains a field which was setup as a 'Number' type field instead of a 'Date' type field and as a result, it displays the dates as 91205, 121409, etc. In trying to change the format of the dates so that it displays them as 9/12/2005, 12/14/2009, etc.,the module I had created in Access only converts some of the dates to the desired format. The dates that did manage to convert successfully were 5 digits, as none of the 6 digit dates converted. The query that calls the function is as follows: [codebox]SELECT QQQF_SPTRN0201.ST1ST & "-" & QQQF_SPTRN0201.ST2ST AS Expr1, QQQF_SPTRN0201.TRNCST, QQQF_SPTRN0201.TRNDST, NumToDate([TRNDST]) AS TrndstDate, QQQF_SPTRN0201.QTYST
FROM QQQF_SPTRN0201
WHERE (((QQQF_SPTRN0201.TRNCST)="D"))
ORDER BY QQQF_SPTRN0201.ST1ST;[/codebox]

The code that converts the field is as follows:
[codebox]Function NumToDate(lInput As Integer) As Date
Dim strTemp As String

strTemp = Format(lInput, "000000")
NumToDate = DateSerial(Val(Right(strTemp, 2)), Val(Left(strTemp, 2)), Val(Mid(strTemp, 3, 2)))
End Function[/codebox]

If there's a better way to do the date conversion, please let me know as I've even tried a different approach to the problem by trying to export the query results to Excel, but I'm not sure how to get it to work.

Any suggestions/ideas to this dilemma are welcomed.

Thanks!

Thanks Dan for the feedback. I'll give that a try (after I make a good backup of the data)...was there anything out of the ordinary in the code you used to convert the number type to text? I'm not an expert in coding, but I understand enough to cause trouble..lol

Thanks again!

Steve

Thanks for the heads up...I updated the code tags....another obstacle that I noticed is that because it's a 'linked' table, Access won't allow me to change the property for a linked table (which is the error message I received when I tried to change the field type from 'number' to 'text'.....the initial warning message stated that the 'table is a linked table with some properties that can't be modified'. If I can get past that hurdle, my next question would be where would I drop the code statement that adds the '/' to the date field? would it replace this code statement?

Please Login or Register  to view this content.

Thanks for your help!


Here's the script to convert the field to a date format....

Please Login or Register  to view this content.

Thankfully, it just has to happen once.

That's a great idea...thanks Dan! It'll be easier to create the new table and set things up so that I'm not having to change anything to do with the linked tables.