Query for Records AFTER Certain Time on a Certain DATE ??

[kev100 via AccessMonster.com]

I'm needing a query to display all records created After a certian Time on a
specific DATE.

The table contains fields for the Date and Time seperately.

Currently, it will prompt the user for the date, then the time.

The sort simply has the sort order / criteria of, first ....all records after
the user entered Date...then all after the user entered TIME.

However, while the result will correctly display all records created after
the entered DATE.....it will only show the records after the entered TIME on
all those displayed dates.

For example. The user needs to display all records created after a certain
Time on a Specific date (e.g. Show me all records created after 3:30 PM on
5/23/06).
.....But.....the query needs to return records created on, for example 5/23/06
at 3:35 PM, 4:50 PM, 11:15 PM......and on 5/24/06 at 8:15 AM, 10:20 AM, 4:35
PM, 6:14 PM, and later, etc.

HOWEVER...what will currently display in the above example is:
5/23/06 at 3:35 PM, 4:50 PM, 11:15 PM......and on 5/24/06 but only at 4:35
PM, 6:14 PM, and later.

The records on 5/24/06 before 3:30 PM are excluded. They need to be INcluded
b/c they were created AFTER 5/23/06.

I'm thinking that this must be a simple issue....I just can't seem to get it.
With 2 seperate fields for Date and Time....how can the query tie the user
entered time to just ONE date?

Thanks very much...hope that makes sense.

 

[Steve Schapel]

Kev,

It sounds like you are using a Parameter Query to prompt the user to
enter the criteria date and time. So in your query design view you
would see something like this in the Criteria of your date column...

[Enter date...]

.... and something like this in the Criteria of the time column...

[Enter time...]

Well, if you are committed to this approach, you can go to the next line
down in the query design grid, in the date column, and put like this...

[Enter date...]+1

(with the part within the []s exactly the same as the line above.

An aternative would be to forget the parameter prompts (always a good
idea in my opinion), and provide an unbound textbox on a form where the
user can enter the date and time criteria, for example...
5/23/06 3:30pm
Then, in your query you could make a calculated field like this...
DateAndTime: [YourDateField]+[YourTimeField]
.... and in the criteria of this column, put a reference to the textbox,
using syntax such as...

[Forms]![NameOfForm]![NameOfTextbox]

 

[kev100 via AccessMonster.com]

Kev,

It sounds like you are using a Parameter Query to prompt the user to
enter the criteria date and time. So in your query design view you
would see something like this in the Criteria of your date column...

[Enter date...]

... and something like this in the Criteria of the time column...

[Enter time...]

Well, if you are committed to this approach, you can go to the next line
down in the query design grid, in the date column, and put like this...

[Enter date...]+1

(with the part within the []s exactly the same as the line above.

I tried the >[Enter date...]+1 adddition one line down in the grid.....

....the query returns that generic "This expression is typed incorrectly, or
it is too complex to be evaluated....." error message.

Do I need to ad anything to the second line of the TIME criteria ?

An aternative would be to forget the parameter prompts (always a good
idea in my opinion), and provide an unbound textbox on a form where the
user can enter the date and time criteria, for example...
5/23/06 3:30pm
Then, in your query you could make a calculated field like this...
DateAndTime: [YourDateField]+[YourTimeField]
... and in the criteria of this column, put a reference to the textbox,
using syntax such as...

[Forms]![NameOfForm]![NameOfTextbox]

I'm needing a query to display all records created After a certian Time on a
specific DATE.

[quoted text clipped - 29 lines]

Thanks very much...hope that makes sense.

 

[Steve Schapel]

Kev,

Ok, sorry, I have never done this myself (I never use parameter
queries), but I thought it would work. Anyway, try this instead...

DateAdd("d",1,[Enter date...])

That worked for me when I tested.
No, nothing in the time column. What you are doing is saying "after the
time entered on the day entered, or from the following day any time".

--
Steve Schapel, Microsoft Access MVP

I tried the >[Enter date...]+1 adddition one line down in the grid.....

...the query returns that generic "This expression is typed incorrectly, or
it is too complex to be evaluated....." error message.

Do I need to ad anything to the second line of the TIME criteria ?

 

[kev100 via AccessMonster.com]

DateAdd("d",1,[Enter date...])


Thanks very much Steve....that seems to do great.