Results 1 to 4 of 4
  1. #1
    mrmmickle1's Avatar
    mrmmickle1 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    North Carolina
    Posts
    78

    SQL Statement to Get Only 1 Date for Timestamp Field in Query

    The Timestamp Field I have is formatted like this: 11/4/2014 5:56:46 AM. The field name is [Timestamp] it is in a table named INPUT_RedSheets_Plates. I have a query right now that pulls in certain fields from this table based on criteria of the [Type] field Like This:



    Code:
    SELECT INPUT_RedSheets_Plates.[Type], INPUT_RedSheets_Plates.[BatchDate], INPUT_RedSheets_Plates.[BatchNumber], INPUT_RedSheets_Plates.[SampleNumber], INPUT_RedSheets_Plates.[Compound], INPUT_RedSheets_Plates.[DateRequested], INPUT_RedSheets_Plates.[RequestedBy], INPUT_RedSheets_Plates.[AcknowledgedBy], INPUT_RedSheets_Plates.[Timestamp], INPUT_RedSheets_Plates.[Notes]
    FROM INPUT_RedSheets_Plates
    WHERE (((INPUT_RedSheets_Plates.[Type])="Urine" Or (INPUT_RedSheets_Plates.[Type])="OF" Or (INPUT_RedSheets_Plates.[Type])="RFPanels" Or (INPUT_RedSheets_Plates.[Type])="Barbs" Or (INPUT_RedSheets_Plates.[Type])="BathSalt" Or (INPUT_RedSheets_Plates.[Type])="UCEX"));
    I want to add an additional parameter. That the Timestamp field must be Date()-1. What Am I doing wrong? I tried to add it like this (NEW ADDITION IN RED):

    Code:
    SELECT INPUT_RedSheets_Plates.[Type], INPUT_RedSheets_Plates.[BatchDate], INPUT_RedSheets_Plates.[BatchNumber], INPUT_RedSheets_Plates.[SampleNumber], INPUT_RedSheets_Plates.[Compound], INPUT_RedSheets_Plates.[DateRequested], INPUT_RedSheets_Plates.[RequestedBy], INPUT_RedSheets_Plates.[AcknowledgedBy], INPUT_RedSheets_Plates.[Timestamp], INPUT_RedSheets_Plates.[Notes]
    FROM INPUT_RedSheets_Plates
    WHERE (((INPUT_RedSheets_Plates.[Type])="Urine" Or (INPUT_RedSheets_Plates.[Type])="OF" Or (INPUT_RedSheets_Plates.[Type])="RFPanels" Or (INPUT_RedSheets_Plates.[Type])="Barbs" Or (INPUT_RedSheets_Plates.[Type])="BathSalt" Or (INPUT_RedSheets_Plates.[Type])="UCEX") AND (INPUT_RedSheets_Plates.[Timestamp])= TO_DATE(Date()-1,'MM/DD/YYYY'));
    I get the error that TO_DATE Is not a function. What do I need to use in place of this?

    Any help would be much appreciated! Thanks for taking a look!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Seems you must have experience with Oracle where To_Date is a valid function.
    How is the field Timestamp defined in your Table?

    I would try Date -1, but if your Timestamps are showing up with Time, the equals may not work.

    Try it and post back. You may have to use

    where format([Timestamp],"MM/DD/YYYY") = format(Date()-1,"MM/DD/YYYY")

    Simplifying your sql for readability
    Code:
    SELECT [Type]
    , [BatchDate]
    , [BatchNumber]
    , [SampleNumber]
    , [Compound]
    , [DateRequested]
    , [RequestedBy]
    , [AcknowledgedBy]
    , [Timestamp]
    , [Notes]
    FROM INPUT_RedSheets_Plates
    WHERE 
    (
      (
       ([Type])="Urine" Or 
       ([Type])="OF" Or 
       ([Type])="RFPanels" Or
       ([Type])="Barbs" Or
       ([Type])="BathSalt" Or
       ([Type])="UCEX"
      ) AND 
       ([Timestamp])= Date()-1)
    );

  3. #3
    mrmmickle1's Avatar
    mrmmickle1 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    North Carolina
    Posts
    78
    It is Defined: Date/Time m/d/yyyy h:nn:ss AM/PM

  4. #4
    mrmmickle1's Avatar
    mrmmickle1 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    North Carolina
    Posts
    78
    Orange,

    Thanks so much for the help. BINGO!!!! I got it working. Just like you suggested:

    Code:
    SELECT [Type]
    , [BatchDate]
    , [BatchNumber]
    , [SampleNumber]
    , [Compound]
    , [DateRequested]
    , [RequestedBy]
    , [AcknowledgedBy]
    , [Timestamp]
    , [Notes]
    FROM INPUT_RedSheets_Plates
    WHERE 
    (
       (
       ([Type])="Urine" Or 
       ([Type])="OF" Or 
       ([Type])="RFPanels" Or
       ([Type])="Barbs" Or
       ([Type])="BathSalt" Or
       ([Type])="UCEX"
       ) AND 
       Format([Timestamp], "MM/DD/YYYY") = Format(Date()-1,"MM/DD/YYYY")
    );

    I was on the right track but didn't think to format the field itself. I had already tried these two failed options:


    Code:
     AND [Timestamp] = Format(Date()-1,"MM/DD/YYYY"));
    
     AND (INPUT_RedSheets_Plates.Timestamp) = CDBL(DATESERIAL(YEAR([Timestamp]), MONTH([Timestamp]), DAY([Timestamp])));

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

Similar Threads

  1. Timestamp based on a field only
    By gaker10 in forum Access
    Replies: 13
    Last Post: 07-28-2014, 12:35 PM
  2. Replies: 5
    Last Post: 07-02-2014, 07:13 AM
  3. Replies: 2
    Last Post: 04-04-2013, 03:13 PM
  4. Date Field empty if statement
    By dubsdj in forum Programming
    Replies: 4
    Last Post: 03-06-2011, 04:02 PM
  5. Access Query to Check Timestamp
    By Meh in forum Access
    Replies: 1
    Last Post: 12-21-2010, 07:18 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