Solved

Access Query WHERE String Not Like Date

Posted on 2015-01-28
9
581 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 68

Expert Comment

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

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
 
LVL 68

Expert Comment

by:Qlemo
ID: 40576207
Did you try Gustav's suggestion?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 68

Expert Comment

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

Accepted Solution

by:
Rey Obrero 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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now