Query to find records after specific date and time today


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

Advertisements

G

ghetto_banjo

Try changing your criteria to use the Now() function instead of the
Date() function.
 
G

ghetto_banjo

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

Jerry Whittle

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

John W. Vinson

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

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?
 
D

DTNHRD

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.

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'
 
Ad

Advertisements

D

DTNHRD

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
 
G

ghetto_banjo

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

DTNHRD

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

Advertisements

G

ghetto_banjo

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 ;-)
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top