• How to Query Good and Bad Dates In A Text Field? (a2k (9.0.6926) SP-3 Jet

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » How to Query Good and Bad Dates In A Text Field? (a2k (9.0.6926) SP-3 Jet

    Author
    Topic
    #445129

    I have a table with 15k records and date field defined as a text with dates stored as mm/d/yyyy

    Most dates are OK, some dates are not are not valid.

    DATE OF MARRIAGE
    12/31/1990
    12/31/1990
    12/71/1913
    14/04/1975
    14/12/1969
    17/17/1970
    17/17/1970

    I want to build a query that will list all the records with the date field and a additional column with OK (for good dates) or NG (for bad dates)

    I have this so far however it dosen’t return any records:

    SELECT [MARRIAGE LICENSES].ID, IsDate([DATE OF MARRIAGE]) AS Expr1
    FROM [MARRIAGE LICENSES]
    WHERE (((IsDate([DATE OF MARRIAGE]))=True));
    

    What am I doing wrong?

    Thanks, John

    Viewing 0 reply threads
    Author
    Replies
    • #1078437

      These two queries work for me (the only difference is that I have only SP2) :

      SELECT [Marriage Licences].Id, IsDate([Date of marriage]) AS Expr1
      FROM [Marriage Licences]
      WHERE (((IsDate([Date of marriage]))=True));

      SELECT [Marriage Licences].Id, [Marriage Licences].[Date of marriage], IIf(IsDate([date of marriage]),"OK","NG") AS Expr1
      FROM [Marriage Licences];

      If the second query don’t work for you, maybe you can post some records of your database.

      Edit : For Access 2K I have also SP3. It is for Access 2003 that I only have SP2. But the two queries are also working in Access 2K for me.

      • #1078444

        Still not getting it, see qry_TWS_57_Check_License_Date

        Thanks, John

        • #1078448

          John,

          I removed the input mask in the table.
          Then I rework query qry_TWS_57_Check_License_Date with two expressions :
          Expr4: Left([Date of marriage];2) & “/” & Mid([date of marriage];3;2) & “/” & Right([date of marriage];4)
          Expr5: IIf(IsDate([expr4]);”OK”;”NG”)

          Hope this is what you want .

          • #1078452

            it’s getting closer, if you sort Expr4 and go to end of file, there are several dates with bad months that are coded OK

            14/04/1975 OK bad month
            14/12/1969 OK bad month
            17/17/1970 NG
            17/17/1970 NG
            17/17/1970 NG
            17/17/1970 NG
            17/17/1970 NG
            17/17/1970 NG
            17/17/1970 NG
            17/17/1976 NG
            21/01/1970 OK bad month

            Something still isn’t right, any clues?

            Thanks, John

            • #1078455

              Replace Expr5 with the following :
              Expr5: IIf(IsDate([expr4]) And Left([expr4];2)<13;"OK";"NG")

            • #1078470

              Francois already provided an excellent solution.

              The explanation for the behavior you saw is that Windows does its best to interpret a date even if it hasn’t been entered using the system date format. Since you are in the USA, you use mm/dd/yyyy format. If you enter 21/01/1970, Windows will convert it automatically to 01/21/1970. This does not work with (for example) 21/13/1970 because it’s still invalid if you switch 21 and 13.

            • #1078521

              Thanks Francois & Hans

              The following works with your help:

              SELECT [Marriage Licenses].ID, [Marriage Licenses].[DATE OF MARRIAGE], 
              Left([Date of marriage],2) & "/" & Mid([date of marriage],3,2) & "/" & 
              Right([date of marriage],4) 
              AS Date1, IIf(IsDate([Date1]) And Left([Date1],2)<13,"OK","NG") AS OKDate
              FROM [Marriage Licenses];
              

              Now I’m trying to isolate just the OK records and I’m getting Enter Parameter Value OKDate

              SELECT [Marriage Licenses].ID, [Marriage Licenses].[DATE OF MARRIAGE], 
              Left([Date of marriage],2) & "/" & Mid([date of marriage],3,2) & "/" & 
              Right([date of marriage],4) AS Date1, 
              IIf(IsDate([Date1]) And Left([Date1],2)<13,"OK","NG") AS OKDate
              FROM [Marriage Licenses]
              WHERE ((([OKDate])="OK"));
              

              What should I change to get this to work?

              Thanks, John

            • #1078526

              SELECT [Marriage Licenses].ID, [Marriage Licenses].[DATE OF MARRIAGE],
              Left([Date of marriage],2) & “/” & Mid([date of marriage],3,2) & “/” &
              Right([date of marriage],4) AS Date1,
              IIf(IsDate([Date1]) And Left([Date1],2)<13,"OK","NG") AS OKDate
              FROM [Marriage Licenses]
              WHERE IIf(IsDate([Date1]) And Left([Date1],2)<13,"OK","NG")="OK"

              or

              SELECT [Marriage Licenses].ID, [Marriage Licenses].[DATE OF MARRIAGE],
              Left([Date of marriage],2) & "/" & Mid([date of marriage],3,2) & "/" &
              Right([date of marriage],4) AS Date1,
              IIf(IsDate([Date1]) And Left([Date1],2)<13,"OK","NG") AS OKDate
              FROM [Marriage Licenses]
              WHERE IsDate([Date1]) And Left([Date1],2)<13

            • #1078647

              On both queries I’m getting Enter Parameter Value Date1, see attached Hans1 & Hans2

              Thanks, John

            • #1078654

              OK, it’s too much at once for Access to evaluate. I have removed the criteria from the two queries, and created two new ones that add the criteria. See attached version (I didn’t bother giving the new queries meaningful names, I accepted the default Query1 and Query2)

            • #1078664

              Thanks for your help

              John

    Viewing 0 reply threads
    Reply To: How to Query Good and Bad Dates In A Text Field? (a2k (9.0.6926) SP-3 Jet

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: