Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Query WHERE String Not Like Date

Posted on 2015-01-28
9
Medium Priority
?
627 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 71

Expert Comment

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

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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 71

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 71

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 2000 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 39

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

718 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