Query using Date() criteria

[rpeare]

But there's no time field or any time stamp on the data you're supplying so what are you supposed to check the time against?

[Bruce]

The system clock. If the CloseDate = the current date and the system clock indicates >=15:00 return true.

[rpeare]

So this report is run once per day at exactly 3pm?

Then if that's the case you wouldn't need the time check at all, you would just assume anything with a closed date was closed prior to 3pm of today's date.

If you are running it at any time during the day, let's say you run it at 5pm there is no way to tell by your data which items were closed prior to 3pm. You would have to have a time field or set your date/time field to include the hour the item was closed. Artificially adding a 3pm date to anything with a closed date of today really does nothing for your query because you're not actually checking it against anything, the same goes for the lower end (now()-180) you would.

I don't follow why you need to perform a time check when you are not storing the time, you are creating a time then checking against that time which doesn't make sense to me.

[Bruce]

I have a "report" window that runs constantly and updates every 5 minutes. It's function is to show orders that have expired starting with the current date and going back 180 days, that need to be manually closed out in the db. When the order is closed out, it does not show up in the query as the status is now "closed". When the db was designed, the time of closure was not a factor "as every order expires at 3:00 p.m. on the CloseDate".
In the date.db I provided please enter "=open" for the criteria under the "Status" column.
i.e. If the CloseDate = Date() and the system clock indicates it is 3:00 p.m. or after, the query should return that item. If the CloseDate = Date() and the system clock indicates it is 10:00 a.m. the query should not return that item. Currently it does. Thus my conundrum.

It's no big deal. Just need to teach the users that if an items shows up needing closure and the CloseDate is the current date, not to close out the item until after 3:00 p.m. It would be better if the item did not show up in the list until after 3:00 p.m.

[rpeare]

Ok, I see now.

Would you prefer the database to update the open/close flag whenever you enter a close date? that way you wouldn't have to rely on human input. Alternately, you don't need to even save the OPEN/CLOSE flag on the table at all, you can code such that any item with a close date is closed. Is there a reason for having a dual flag (close date and field that literally says 'open' or 'closed')?

If you would rather do this it can be handled more fairly simply if you have a reason for having two 'open' flags I'll have another go at your query.

[Bruce]

Great! The reason for the open/close flag is for users to manually confirm they have "also" performed the necessary physical steps to close the order prior to setting the db open/close flag to "closed" which is performed via another form.

Can you tell me why I can't use the Date() function (in this db) as criteria in the query? The error indicates I have an undefined function. I always thought Date() was a predefined function, like Now().

[alansidman]

Can you tell me why I can't use the Date() function (in this db) as criteria in the query? The error indicates I have an undefined function. I always thought Date() was a predefined function, like Now().

I have experienced this. Open the VBA window (Control +G). Click on Tools, References. Check to see if any items listed are indicated as missing. If so, scroll down until you find the missing reference and click it. Update the references and close the VBA window.

Alan

[Bruce]

I have experienced this. Open the VBA window (Control +G). Click on Tools, References. Check to see if any items listed are indicated as missing. If so, scroll down until you find the missing reference and click it. Update the references and close the VBA window.

Alan

Thanks, I did what you mentioned but there are many "unchecked" references. I'm not sure what you mean by "find the missing reference".

[alansidman]

A reference that is needed that is not checked will be marked with the term "Missing." If you have searched and not found this, then it is another issue. I am not sure what is the cause is to create this. You might want to create a new thread with this issue so that it gains some attention. Be sure to make your title clear.

Alan

[rpeare]

These are the references I have and the date() function works in queries

Visual Basic for Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Access Database engine Objects

(this is on a 2007 machine your versions may be 11.0?)

I'm working on the query will post it later.

Hrm this is weird I set up identical references in your database and the date() function still does not work, may have to use a different reference for earlier versions of MS access.

[rpeare]

Try using this query:

SELECT WorkOrders.WorkOrderID, WorkOrders.OpenDate, WorkOrders.CloseDate, WorkOrders.Status, Now() AS CurrentTime
FROM WorkOrders
WHERE (((WorkOrders.CloseDate)<Format(Now(),"Short Date")) AND ((WorkOrders.Status)="open")) OR (((WorkOrders.CloseDate)=Format(Now(),"Short Date")) AND ((WorkOrders.Status)="open") AND ((Now())>CDate(Format(Now(),"Short Date") & " 03:00:00 PM")));

I put everything in terms of now() so it shouldn't cause you any grief if you can't get the references working.

[alansidman]

On the Date() function not working: I found this unusual. I tried it out on two different machines, one with Access XP and one with Access 2007. Both indicated undefined function. I then created a new db and imported the table and query to the new db. Changed Now() to Date() in the query and it works. I think that you might have something corrupted in your db. You can always import to a new db and give it a try. Still not sure what the cause was.

Alan

[Bruce]

I fixed this one. I added the expression Hour(Now()) which provided the system clock hour, I then set the criteria to >=15 and it works!