Query with a Date

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.

Answer 1

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

---

Build a little, test a little

Answer 2

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

Answer 3

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

Answer 4

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 [e-mail].