Solved

Access Query WHERE String Not Like Date

Posted on 2015-01-28
9
618 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 70

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
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 
LVL 70

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 70

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 38

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

622 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