Solved

Access Query WHERE String Not Like Date

Posted on 2015-01-28
9
605 Views
Last Modified: 2015-01-28
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
Comment
Question by:KANEDA 0149
9 Comments
 
LVL 69

Expert Comment

by:Qlemo
ID: 40576149
not like '%/%/%'    is a very simple condition, but should work.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40576192
Or perhaps a little clevier:

WHERE Not IsDate([Doc_Date])

/gustav
0
 

Author Comment

by:KANEDA 0149
ID: 40576205
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 69

Expert Comment

by:Qlemo
ID: 40576207
Did you try Gustav's suggestion?
0
 

Author Comment

by:KANEDA 0149
ID: 40576246
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 40576261
Let's try if Access is exact:   not IsDate(Doc_Date) or not like '%/%/____'
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40576299
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
 
LVL 36

Expert Comment

by:PatHartman
ID: 40576396
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
 

Author Closing Comment

by:KANEDA 0149
ID: 40576495
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

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question