none
Query with a Date RRS feed

  • Question

  • I want to show all items that have not yet been shipped yet in a query. I know that the criteria >Date() shows all items shipped after today. But what if someone wants to enter a date and see all items shipped before, let's say 1/2/2013 or 3/5/2015 (as examples). How can I enter the criteria so someone can put in the date that they want and find all items shipped after that date? Thanks.
    Friday, September 8, 2017 6:43 PM

All replies

  • In query design view enter    < 1/2/2013   as criteria

    Build a little, test a little

    Friday, September 8, 2017 6:48 PM
  • Hi,

    You could try using a criteria like:

    >[Enter Date]

    or

    >CDate([Enter Date]

    In addition, you may have to declare the parameter as a Date in your query. For example:

    PARAMETERS [Enter Date] DateTime;
    SELECT * FROM tblShipments
    WHERE ShipDate>[Enter Date];

    If you declare the parameter, then you should be fine with the first sample criteria. I don't think you'll need to use the second one.

    Hope it helps...



    Friday, September 8, 2017 6:57 PM
  • You can make the query more flexible by defining a date range by start and end date parameters, both of which are optional, e.g.

    PARAMETERS [Start Date:] DATETIME,
    [End Date:] DATETIME;
    SELECT *
    FROM Shipments
    WHERE (DateShipped >= [Start Date:]
    OR [Start Date:] IS NULL)
    AND (DateShipped < [End Date:]+1
    OR [End Date:] IS NULL)
    ORDER BY DateShipped;

    The user can enter both dates to define a range; only one of the dates to return all shipments after a start date, or before an end date; or neither of the dates to return all rows from the table.  It works by testing for NULL parameters.  If the user leaves a parameter empty (NULL) when prompted, the second part of the OR operation will evaluate to TRUE regardless of the value in the DateShipped column in a row.

    Ken Sheridan, Stafford, England

    Friday, September 8, 2017 10:31 PM
  • Hi Duncipoo,

    I can see that you did not follow up this thread , after creating this thread.

    is your issue solved?

    if you got the solution by yourself then I suggest you to post the solution and mark it as an answer.

    if your issue is still exist then I suggest you to refer the suggestions given by the community members.

    if still you have any further question regarding same issue then I suggest you to let us know about that.

    we will try to provide you further suggestions to solve the issue.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 12, 2017 6:36 AM
    Moderator