Date Filter

[Jody]

I recently ran up against something I hadn't run up against before (though
I'm sure many have). I needed to build a report to show totals of items for
logging how many of what item was logged per date range. Since there are
multiple columns with item numbers and quantities I first made a Union Query
to compile the data. I then built my select query to perform the aggregate
calculations (group by item and show total quantities).

The date in this particular database is set to General Date so they can
capture time as well as day. So, when attempting to filter the data as I
normally would with a simple Between [StartDate] And [EndDAte] I noticed that
there was large chunks of data missing, and I couldn't filter down to one day
(i.e. the StartDate and EndDate having the same date) because it would not
return any data. The catch is the time stamp. Although this may not be the
absolute best solution, this is what I did to pull the data accuratly:

In the Date criteria:
Between [Beginning Date] & " 00:00:00 AM" And [End Date] & " 11:59:59 PM"

I hope this is at least a little helpful for anyone experiencing the same
issue.

 

[John Spencer]

Better would be to use the following.

Between [Beginning Date] AND DateAdd("s",86399,[End Date])

OR

= [Beginning Date] AND < DateAdd("d",1,[End Date])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I recently ran up against something I hadn't run up against before (though
I'm sure many have). I needed to build a report to show totals of items for
logging how many of what item was logged per date range. Since there are
multiple columns with item numbers and quantities I first made a Union Query
to compile the data. I then built my select query to perform the aggregate
calculations (group by item and show total quantities).

The date in this particular database is set to General Date so they can
capture time as well as day. So, when attempting to filter the data as I
normally would with a simple Between [StartDate] And [EndDAte] I noticed that
there was large chunks of data missing, and I couldn't filter down to one day
(i.e. the StartDate and EndDate having the same date) because it would not
return any data. The catch is the time stamp. Although this may not be the
absolute best solution, this is what I did to pull the data accuratly:

In the Date criteria:
Between [Beginning Date] & " 00:00:00 AM" And [End Date] & " 11:59:59 PM"

I hope this is at least a little helpful for anyone experiencing the same
issue.

 

[Jody]

In Between [Beginning Date] AND DateAdd("s",86399,[End Date]) what does
"s",86399, mean?

Better would be to use the following.

Between [Beginning Date] AND DateAdd("s",86399,[End Date])

OR

= [Beginning Date] AND < DateAdd("d",1,[End Date])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I recently ran up against something I hadn't run up against before (though
I'm sure many have). I needed to build a report to show totals of items for
logging how many of what item was logged per date range. Since there are
multiple columns with item numbers and quantities I first made a Union Query
to compile the data. I then built my select query to perform the aggregate
calculations (group by item and show total quantities).

The date in this particular database is set to General Date so they can
capture time as well as day. So, when attempting to filter the data as I
normally would with a simple Between [StartDate] And [EndDAte] I noticed that
there was large chunks of data missing, and I couldn't filter down to one day
(i.e. the StartDate and EndDate having the same date) because it would not
return any data. The catch is the time stamp. Although this may not be the
absolute best solution, this is what I did to pull the data accuratly:

In the Date criteria:
Between [Beginning Date] & " 00:00:00 AM" And [End Date] & " 11:59:59 PM"

I hope this is at least a little helpful for anyone experiencing the same
issue.

 

[Jody]

Also, and this may necessitate a separate post, but do you know how I can
display my date range (i.e. Beginning Date and End Date) on the report that
is being generated from this query?

 

[Jody]

The answer is to put =[Filter] in the Control Source. In my case =[Beginning
Date] and =[End Date].

Thanks for your help though, I modified my criteria as per your suggestions.

 

[John Spencer]

"s" means add seconds
86399 is one less than the total number of seconds (86400) in a day

So adding 86399 seconds to a date gives you the date you started with and a
time of 23:59:59.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

In Between [Beginning Date] AND DateAdd("s",86399,[End Date]) what does
"s",86399, mean?

Better would be to use the following.

Between [Beginning Date] AND DateAdd("s",86399,[End Date])

OR

= [Beginning Date] AND < DateAdd("d",1,[End Date])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I recently ran up against something I hadn't run up against before (though
I'm sure many have). I needed to build a report to show totals of items for
logging how many of what item was logged per date range. Since there are
multiple columns with item numbers and quantities I first made a Union Query
to compile the data. I then built my select query to perform the aggregate
calculations (group by item and show total quantities).

The date in this particular database is set to General Date so they can
capture time as well as day. So, when attempting to filter the data as I
normally would with a simple Between [StartDate] And [EndDAte] I noticed that
there was large chunks of data missing, and I couldn't filter down to one day
(i.e. the StartDate and EndDate having the same date) because it would not
return any data. The catch is the time stamp. Although this may not be the
absolute best solution, this is what I did to pull the data accuratly:

In the Date criteria:
Between [Beginning Date] & " 00:00:00 AM" And [End Date] & " 11:59:59 PM"

I hope this is at least a little helpful for anyone experiencing the same
issue.