I'm currently analysing data from a table which contains 2 fields showing dates in different format e.g. 20170220 and 20/02/2017 and I want to show the data from the same dates from both formats but can't get my query to do this.
What have you tried and how is it not doing what you want? A post of the SQL of the query you tried, the results you got, and the result you want would be helpful.
what I'm trying to achieve is for a query to show for example the data for both 22/02/2017 and 20170222 which are the same date but the query wont show both together.
I can create a function that will help you but before I spend my time on it I like you to confirm that these two formats (yyyymmdd and dd/mm/yyyy) are the only ones in this field. Is this the case?
Basically the function I would create would use the IsDate function to determine the format. If the IsDate function returned true it would just use CDate to return the date and if it returned False use Left, Mid, Right and CDate functions to make a valid date and return it. Do you want to give this a try by yourself?
To display or operate on these date you can use a function that converts them to dates. Below is a function that does that.
Code:
Public Function GetGoodDate(InDate As Variant) As Variant
If IsNull(InDate) Then
Exit Function
End If
If IsDate(InDate) Then
GetGoodDate = CDate(InDate)
Else
GetGoodDate = CDate(Right(InDate, 2) & "/" & Mid(InDate, 5, 2) & "/" & Left(InDate, 4))
End If
End Function
You would put this function in a standard module. In the attached demonstration database I put it in a module named Date Conversion.
You would use this in a query as an expression. In the attached database in the qryTest I added the expression
Code:
Select Date: GetGoodDate([T1])
The GetGoodDate function returns dates in Date/Time format so you use it like that field type. In this query I put the literal date #2/22/2017# which selects those dates in the table.
I also included the qryFixDates, an update query, that could fix this problem for you permanently by moving these mixed format string dates to a Date/Time field. When run it updates a Date/Time field I added named T1Date with the string dates from field [T1].
You could backup your system (of course), add a Date/Time field, run this type of query, delete field T1 and rename the Date/Time field to T1. Then fix affected queries, forms, and report as necessary.
