Query to find records after specific date and time today

  • Thread starter Thread starter DTNHRD
  • Start date Start date
D

DTNHRD

Our contract requires me to post a job for 10 calendar days and it must
remain active until 8:00 am the date of the deadline. After that it must be
removed from the list. If I post it today (3/15/10 regardless of the time)
the job would have to be available right up through 3/25/10 at 7:59am - then
it must be gone/unavailble at 8:00 am. The record has a field entitled
"Deadline" where date and time would be entered into the field as "03/25/10
08:00:00 am"

However, all records with that deadline for that particular date are removed
at midnight (00:00:00) on that date - I still need to show those records
until 8am. What do I need to do differently - change my field format, the
input/data or change the criteria in the query? I have entered my query
critera used in that "deadline" field below. Thank you in advance for your
consideration.
 
Try changing your criteria to use the Now() function instead of the
Date() function.
 
is this something that is always refreshing/updating? Or are you
calling the updates at a certain time?

seems like you could just do this:

criteria: >=Now()


then at 3/25/10 08:00:01 AM, any records with deadline of 3/25/10
08:00:00 would not show up.
 
<=DateAdd("h",8,Date())

Your criteria looks for records 8 hours into the future. I would think that
it should delete all future records. You actually want older records.

Also what runs at midnight to cause this to happen? You might want to show
us the code.
 
<=DateAdd("h",8,Date())

Your criteria looks for records 8 hours into the future. I would think that
it should delete all future records. You actually want older records.
Click to expand...

Jerry? That's <= (Less Than or Equal); as written it would choose all records
older than 8am on the morning that the query is run (back to January 1,
100AD). Date() returns midnight at the beginning or today's date... right?
 
John W. Vinson said:
<=DateAdd("h",8,Date())

Your criteria looks for records 8 hours into the future. I would think that
it should delete all future records. You actually want older records.
Click to expand...

Jerry? That's <= (Less Than or Equal); as written it would choose all records
older than 8am on the morning that the query is run (back to January 1,
100AD). Date() returns midnight at the beginning or today's date... right?

--

John W. Vinson [MVP]
.
This system is on all the time - 24/7, so employees may apply for jobs at all hours. The query behind the report was intended to return records UNTIL they hit their individual deadline date and time. All of them have the same deadline TIME but different dates - I want the query to compare the deadline date and time against that of the system date and time. Return/show the record (JobPosting) until the SYSTEM time = (Deadline) date and time. I looked at my underlying date format to make sure it will correspond to the system and it appears that it does. In plain language, I want this query to 'SHOW [JOBPOSTING] IF [DEADLINE] IS MORE THAN TODAY AT 8AM'
Click to expand...
 
Please note that this a query for employees to view only. There is no data
entry or edit ability. Am I approaching it from the wrong angle? TIA
 
Quoted: This system is on all the time - 24/7, so employees may apply
for jobs at all hours. The query behind the report was intended to
return records UNTIL they hit their individual deadline date and
time. All of them have the same deadline TIME but different dates - I
want the query to compare the deadline date and time against that of
the system date and time. Return/show the record (JobPosting) until
the SYSTEM time = (Deadline) date and time. I looked at my underlying
date format to make sure it will correspond to the system and it
appears that it does. In plain language, I want this query to 'SHOW
[JOBPOSTING] IF [DEADLINE] IS MORE THAN TODAY AT 8AM'
-------------------------------------


The criteria on the Deadline should simply be >=Now(). If they open
the report at 3/15 8:00:01 AM, anything with a deadline of 3/15
8:00:00 AM will NOT show up anymore.
 
Thank you. I knew there had to be something simpler and I got ahead of
myself trying to find a more complex answer. Again, many thanks.
 
no problem, hope that works out for ya!

i have many many time tried to make something way more difficult than
necessary. access seems to do that to people ;-)
 
Back
Top