Date recognition 1

[ktb916]

i want to create a function that takes a "yymmdd" date format and changes it to a "ddmmyyyy" format. I need to make a four digit year in order to differentiate between 1900 and 2000. I have created a function that brings in the "yymmdd" data as a string - changes it around to the correct format and then converts it to a date data type using Cdate - but when i use it in a query the data doesn't seemed to be recognized as a date because it doesn't sort correctly.

Is there a better way? or a different function than CDate to convert the data to a date?

KT

 

[DoubleD]

DateValue(Mid(DateField,3,2) & "/" & Right(DateField,2) & "/" & Left(DateField,2))

This returns a date from a string "yymmdd". You can display it in whatever format you like by setting the Format property in your query.

I am what I am based on the decisions I have made.

DoubleD

 

[Golom]

Believe it or not "yymmdd" as in "041231" is not a date format in the sense that cDate will not recognize it as "yymmdd". What it will do is

* Convert it to a double (i.e. 41231)
* Treat that as the number of days since Dec 31, 1899
* Present that as a date ... i.e. Nov 18, 2012.

If you want to convert this sort of thing to a date, use an explicit conversion function like DateSerial rather than cDate.

yymmdd = "041231"
TheDate = DateSerial ( Val ( Left ( yymmdd, 2), 
                       Val ( Mid  ( yymmdd, 3, 2),
                       Val ( Right( yymmdd, 2 ) )

 

[PaulBricker]

Access stores dates as a number. To see this, you can go to the debug window and type in
?CDbl(Date()) and hit enter. For today, the value returned will be 38355. That is the number of days since 1/1/1899 or some time around then. To convert it you can use this type of combination
Format(CDbl(DateField),"mm/dd/yyyy")

This may or may not do it depending on how the CDbl() function sees your value stored as "yymmdd" but try it out and post back.

Paul

 

[ktb916]

I did what DoubleD suggested however for some reason when i try to sort the results in a query based on the field with the date provided by the function it sorts numerically starting left to right instead of treating the data like a date and sorting accordingly - does it have to be in yymmdd format to sort correctly?

I also used DateSerial as suggested by Golom but i'm getting the same result - the query will not sort correctly.

KT

 

[DoubleD]

Are you sorting on another field also?

I tested this in A2K and it works just fine. Recognizes the field as a date and sorts correctly Ascending or Descending.

I am what I am based on the decisions I have made.

DoubleD

 

[ktb916]

No - i'm only sorting on one field - here is my code - i added an IF statement to take care of the turn of the century.

Public Function ConDate(ByVal QanDate As Variant)
Dim Mnth As String
Dim Yer As String
Dim Dy As String
Dy = Mid$(QanDate, 5, 2)
Mnth = Mid$(QanDate, 3, 2)
If Mid$(QanDate, 1, 2) < 85 Then
    Yer = "20" & Mid$(QanDate, 1, 2)
Else
    Yer = "19" & Mid$(QanDate, 1, 2)
End If

ConDate = Format(DateSerial(Yer, Mnth, Dy), "mm/dd/yyyy")

End Function

 

[DoubleD]

Your function needs to be defined as a date.
And you'll need to Format your column in your query to have it display as DDMMYYYY and still be read as a date.

Public Function ConDate(ByVal QanDate As Variant) as Date
Dim Yer As String

If Left(QanDate, 2) < 85 Then
    Yer = "20" & Left(QanDate, 2)
Else
    Yer = "19" & Left(QanDate, 2)
End If

ConDate = DateSerial(Yer, Mid$(QanDate, 3, 2), Mid$(QanDate, 5, 2))

End Function

I am what I am based on the decisions I have made.

DoubleD

 

[ktb916]

IT WORKS !! Thanks so much!!

KT

 

[ktb916]

i have a new question related to the same issue. i have created this custom function in Access and it works great. it converts the date no problem - but when i try to create an MS Query based on the query in access with this function i get an ODBC error "function not defined". is there a way to get around this problem?

KT