• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 684
  • Last Modified:

Access Query WHERE String Not Like Date

I'm working in Access DB on large sets of data.  There is a string field in my query called "Doc_Date" which I have imported from another source.  I would like to select all records WHERE the "Doc_Date" IS NOT LIKE a date format (i.e. 'm/d/yyyy' ).

Below is an example of the select results and an example of the select results with the WHERE clause to return the records that is not in the 'm/d/yyyy' date format.  Thanks for the help!


Example
0
KANEDA 0149
Asked:
KANEDA 0149
1 Solution
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
not like '%/%/%'    is a very simple condition, but should work.
0
 
Gustav BrockCIOCommented:
Or perhaps a little clevier:

WHERE Not IsDate([Doc_Date])

/gustav
0
 
KANEDA 0149Author Commented:
Thanks Qlemo, that was really close but I found some data like this (sorry did not include in my example);

06/30//1998

02/028/2011

1/13/20144

Who would think it but yes, it's there.  Any ideas on identifying these types?  I would like to include them as items that are bad.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Did you try Gustav's suggestion?
0
 
KANEDA 0149Author Commented:
Whoops totally missed Gustav's, thanks Qlemo.  I did try that one which is close but it excluded 'Doc_Date' formatted like these...  this is so frustrating.

7/1/204
October 1007
02/028/2011
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Let's try if Access is exact:   not IsDate(Doc_Date) or not like '%/%/____'
0
 
Rey Obrero (Capricorn1)Commented:
place this codes in a regular module

Function IsReallyDate(v As String) As Boolean
Dim xArr() As String
If InStr(v, "/") Then
    xArr = Split(v, "/")
    Select Case UBound(xArr)
        Case 2
            If Len(xArr(2)) = 4 And (Len(xArr(1)) = 2 Or Len(xArr(1)) = 1) Then
            IsReallyDate = True
            Exit Function
            Else
            IsReallyDate = False
            Exit Function
            End If
        Case 3
        IsReallyDate = False
        Exit Function
    End Select
    ElseIf InStr(v, " ") Then
        IsReallyDate = False
        Exit Function
    ElseIf InStr(v, "-") Then
        IsReallyDate = False
        Exit Function
    
End If
End Function

Open in new window


create a query like this

SELECT TableDate.Doc_Date, IsReallyDate([Doc_Date]) AS Expr2
FROM TableDate
WHERE IsReallyDate([Doc_Date])=0

you can change the codes to suit to other variations of the records in the field [Doc-Date]
0
 
PatHartmanCommented:
If your table has a primary key, you can create a new table with just two columns.  The PK and the date field.  Make sure the date field is defined as a date data type and marked as required.  Then append all the existing data to the new table.  Only the rows with valid dates will be appended.  You can then use a left join to find the ones that didn't get appended.  This method is certainly more round about than what was suggested by gustav but it is always good to have options.

Also
7/1/204
 October 1007
 02/028/2011
are invalid.  Why would you want them to be included?  Just because a human can interpret the values doesn't make them valid.  Once you see all the variations of bad data, you can write code to correct them one at a time.
0
 
KANEDA 0149Author Commented:
Thank you, this worked exactly and captured all text not identified as 'm/d/yyyy'.  The goals is to isolate these 'bad' document dates so they can be updated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now