Stumped on a query

[rikklaney1]

I need some help with a date/time query on a report record source. I want to return all record from any day that where created between two times. If I use

>#7:00:00 am# and < #7:00:00 pm#

in the criteria field of the reports record source I get no records at all. I'm pretty sure this has to do with the date but how can I tell it to include any DAY but only between these hours?

People in this forum have been greatly helpful to me as I tried to learn access so far and I appreciate all the help from you guys.

 

[MarkK]

You need to drop the day portion of your date/time value using TimeValue() first.

If TimeValue(MyDate) >= #07:00# AND TimeValue(MyDate) <= #19:00# Then

 

[rikklaney1]

I assume I need to change (MyDate) to something else but what do I change it to for all dates?

 

[MarkK]

What do you mean "all dates?" What is the record source of the report? If you intend to limit a report by date, it is most commonly on a single date field.

 

[rikklaney1]

The record source is a table with about 900 entries. What I want to do is see just the ones created on first shift in one report and just third shift in another. So I need to see records created between 7am and 7pm no matter what day they were created. If that makes sense.

 

[MarkK]

What I recommend is that you open the query design grid and figure out how to write a query that returns the records you need. In that view you will see the fields that are in the table. Against one or more of those fields you need to apply the constraint show in post #2.

Figuring that stuff out will be a huge advantage to you using Access. Post back if you have more questions or if you get stuck.

Cheers,

 

[rikklaney1]

The query design grid for the report is where I am working.

 

[MarkK]

OK, so is there a problem? If so, please describe it in detail. Presumably there are fields in the grid. Note that one of the rows is labelled "Criteria:" (look all the way to the left for row labels).
Specify criteria in that row and run the query. You'll get detailed error messages or results that you can assess and refine.

 

[rikklaney1]

So if I take the example from 2 and do this

TimeValue(1/1/1999) >= #07:00# AND TimeValue(10/8/2015) <= #19:00#

I would get records created between 7AM on 1/1/1999 and 7pm on 10/8/2015.

when what I need is a wildcard day like

TimeValue(*/*/*) >= #07:00# AND TimeValue(*/*/*) <= #19:00#

 

[rikklaney1]

I would rather not have to do this

Criteria TimeValue(1/1/1999) >= #07:00# AND TimeValue(1/1/1999) <= #19:00#
or TimeValue(1/2/1999) >= #07:00# AND TimeValue(1/2/1999) <= #19:00#
or TimeValue(1/3/1999) >= #07:00# AND TimeValue(1/3/1999) <= #19:00#
or TimeValue(1/4/1999) >= #07:00# AND TimeValue(1/4/1999) <= #19:00#
etc
etc
etc

 

[Galaxiom]

TimeValue(1/1/1999)

At best, this expression would equal 00:01:48. It is the Time representation of 1 divided by 1999.

Times and dates must be delimited by #.

 

[MarkK]

Let's say you have a field called "datetime" which contains both a date and a time. To do what you say in your original post, return "all record from any day that where created between two times" then you need to drop the date portion of the field value and just return the time, which you could do like this . . .

timeonly: TimeValue([datetime])

. . . in the query design grid. And then for critieria in that field, do this . . .

>= #07:00 AND <= #19:00#

. . . which should return records from any day between 7am and 7pm.
If you also want to filter by day, then do that in a different field.
Hope this helps,

 

[rikklaney1]

I finally just went the easy way (I think) and added a field to the table and the form to designate first/third shift and will sort them that way. Doesn't do much for existing records but going forward they'll be sortable and I'll figure out something else for the old records. lol. Thanks for the help anyway.

 

[smig]

do you have a field where you store the date and time the record was created or changed ?