Results 1 to 7 of 7
  1. #1
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46

    Super Strange Behavior - Kudos if you can solve this one -

    I have two tables IDENTICAL structure (as a matter of fact the structure was created from the other that is working)



    I have a query to return DISTINCT dates works on one, the other returns all the dates.

    The SQL:

    Works:
    SELECT DISTINCT GlucoseReadings.ReadingDate
    FROM GlucoseReadings
    ORDER BY GlucoseReadings.ReadingDate;


    Doesn't work:
    SELECT DISTINCT Results.ReadingDate
    FROM Results
    ORDER BY Results.ReadingDate;



    The two tables: GlucoseReadings and Results

    The Query: DateQuery

    If I run the DateQuery on the GlucoseReadings Table , works, returns only Distinct Dates
    Inside the same Query if I change to the Results Table in returns every single record

    I am attaching the example database, all you have to do is go to query design and change the field from one query to the other to see the STRANGE behavior.

    I don't have enough hair to pull out anymore on this one.

    example.zip

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The reading date field has times in it. You don't see them because of the format applied.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46
    Ok, I see what you mean now....

    How in the heck did this happen and how do I fix it........
    Last edited by wrayman; 10-19-2016 at 03:13 PM. Reason: Wrong post

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I mean the date field itself. Try this query:

    SELECT Results.ReadingDate, Format([ReadingDate],"mm/dd/yy hh:nn") AS Expr1
    FROM Results;
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If I had to guess, you're populating one field with Date() and the other with Now(). Now() includes the time.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46
    What happened was this was a linked table SOMEHOW that data type got changed

    The tables were made from an append query at two different times from the same dataset, I was using an old appended table When I created a second table from the same append Query that date field had changed to the hold the date/time value. that is why I couldn't figure it out.

    Easy fix ReadingDate: DateValue([date_time]) to the append Query and it returns just the date, sometimes it pays to have an extra set of eyes and brains.

    Thanks so much

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, glad you got it sorted.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Strange TransferSpreadsheet Behavior
    By JoeM in forum Programming
    Replies: 6
    Last Post: 08-05-2015, 07:47 AM
  2. Replies: 3
    Last Post: 07-18-2013, 12:23 PM
  3. Kudos!!!
    By Stephanie53 in forum Access
    Replies: 3
    Last Post: 06-05-2013, 11:35 AM
  4. Strange Behavior on Startup
    By RonL in forum Programming
    Replies: 3
    Last Post: 02-14-2013, 03:31 PM
  5. Strange Behavior when Sorting
    By geniass in forum Queries
    Replies: 5
    Last Post: 09-02-2010, 03:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums