Query using Date() criteria

[Bruce]

I have a table of orders that all have a "CloseDate" associated with them. The purpose of the query is to list orders that have a CloseDate >=Date(). The problem is that the orders are not considered closed until after 3:00 p.m. so I need the query to include this variable. I tried to set the criteria to >=Date(15:00) but Access doesn't like the format. Is there a way to use the >=Date() and also specify a time?

Thanks!

[alansidman]

Use the DateAdd function with the hour interval. Here is a quick explanation on how to apply it.

http://www.techonthenet.com/access/f...te/dateadd.php

Alan

[Bruce]

Humm. that didn't work. Let me be more correct. The criteria defined for the sql query are: "CloseDate" Between Date() and Date()-180. The idea is to show users which orders should be closed out starting today and going back 6 months. The problem is that orders don't expire until after 3:00 p.m. so I don't want orders to show up in the query results if the CloseDate is today (Date()) unless it's after 3:00 p.m. I tried to add
Expr 1:DateAdd('h',15,[CloseDate]) but the query still returned orders with a CloseDate of today even though it is only 10:00 a.m. If I could write the query criteria as I see it logically it would be:

Return true if CloseDate is between today at or past 3:00 p.m. , and today at or past 3:00 p.m. -180 days.

i.e. criteria = Between Date()>=15:00 AND Date()>=15:00 -180

Thanks!

[alansidman]

The date function does not recognize time, so you will need to make 3 pm a fraction of the day. ie. 15/24 or its decimal equivalent, .625.

Try then between Date()-180 and Date() + .625

Alan

[Bruce]

I can see my logic wouldn't work either because of the Date()>=15:00 -180 as that would include orders from today despite the time. bummer!

I'll try your idea.

[Bruce]

Tried several different itterations but I don't think MS-SQL likes using "<" or ">" characters when using the Date() function. Just for grins I set the criteria to CloseDate = Date()+0.333 (or 8:00 a.m.). There are 3 orders that fit the criteria but the query returned null.

Also attempted to remove the "between" function as that includes all from today and used the OR function as: CloseDate =
<Date()-180 OR Date()+.635 but that didn't work either.

No biggie, just hoped I could work this out.

[alansidman]

Perhaps it is time to post a copy of your db with sample data in it that has no confidential information in it. Also, run a compact and repair before posting the db.

Alan

[Bruce]

Thanks for your help and suggestions Alan but it would take too long to redact the sensitive info. I think I'll just have to work with what I've got.

I think if I had used the format Date&Time for the CloseDate field OR added a seperate CloseTime field I would have more options available. That got me thinking I could just add a column named CloseTime to the table and make a 3:00 entry for all entries. I could have the field self populate at 3:00 for all new entries. Then I could filter based on that criteria.

Back to the drawing board

[rpeare]

can I ask how you're storing your date and time the order was closed?

Are they in two separate fields or are they both the same field?

Assume for the sake of argument that it's stored in the same field

let's assume then your table is named CLOSEDATES and the field you're interested in is CLOSEDDATE

now let's assume you have a form called FRM_LOOKUP with a field called LOOKUPDATE that is a date field (a regular dd/mm/yyyy format or mm/dd/yyyy depending on where you are)
In the form put in the date 1/1/2011

in the CLOSEDATES table you have these three records

1/1/2011 2:59:59 PM
1/1/2011 3:00:00 PM
1/1/2011 3:00:01 PM

Next create a query based on the table CLOSEDATES
Add the field CLOSEDDATE

in the criteria of CLOSEDDATE put:

<=CDate([forms]![frm_lookup]![lookupdate] & " 3:00:00 PM")

My query shows record 1 and 2 and does not show 3 which I think is what you're after.

[Bruce]

Thanks very much, you are 100% correct however, the CloseDate field only contains the date, not the time as this was not considered during development. I considered reformatting the field to date and time but have not had time to experiment with it. I did post the idea of adding a new field CloseTime and having all current and new records auto-populate with 15:00. Time to copy the DB to development server and start playing with it.

Thanks!

[rpeare]

So as of the moment anything closed on the current day would be considered closed so you really want to check all items closed before the current date? if you're not track closing times all three of the items in my test would say 1/1/2011 but you would want to exclude them from a report looking at items closed before 3pm on 1/1/2011?

If so in your criteria instead of

<=CDate([forms]![frm_lookup]![lookupdate] & " 3:00:00 PM")

You'd have

<=dateadd("d", -1, (CDate([forms]![frm_lookup]![lookupdate] & " 3:00:00 PM")))

[Bruce]

I will create/post a DB today that has the basic data and also explain the process and we'll see what we can do. TkS!!

[Bruce]

Ok, I've created a new DB called date.mdb. Unfortunately the Date() function was undefined for the query criteria so I switched it to Now() which performs a similar function. The idea is to NOT have records in the
WorkOrders table be returned from the query unless it is after 3:00 p.m. on the day the query was executed even if the CloseDate IS the day the query was run. You can add entries to the WorkOrders table as needed to fit the experiment.

p.s. how do I add/define the Date() function so it works in the criteria for
the query. i.e. Criteria: CloseDate is "Between Date() and Date()-180.

Thanks!

[rpeare]

Your dates do not have a time portion so I can only assume you want to see items that are less than today's date (excludes everything from today) and includes anything from the last 180 days. Which is exactly what my last suggestion does. You can do it with your syntax with between date()-1 and date - 180. It will only show records with a close date that predates the current date and 180 days in the past.

[Bruce]

Yes, that would be easy. The feature I am trying to add is:

If there are any orders with a CloseDate of Date() the query needs to retrun those orders only if it's after 3:00 p.m.

i.e. If CloseDate = Date() AND Time >=15:00 return true.

Tks!