Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:

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


    What am I doing wrong?

    Thanks, John

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

    <code>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];</code>


    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.
    Francois

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Still not getting it, see qry_TWS_57_Check_License_Date

    Thanks, John
    Attached Files Attached Files

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 .
    Attached Files Attached Files
    Francois

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thanks Francois & Hans

    The following works with your help:
    <pre>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];
    </pre>



    Now Iím trying to isolate just the OK records and Iím getting Enter Parameter Value OKDate
    <pre>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"));
    </pre>


    What should I change to get this to work?

    Thanks, John

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

    Thanks, John
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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)
    Attached Files Attached Files

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thanks for your help

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •