Between date with greater than or eaqual to

[Philip Klaerner]

I need some help I am trying to create a query that return date values. This is how I originally set it up and I get date ranges that are earlier then what I want.

---------------------------------------------------
Query is one table query, named Modified
---------------------------------------------------
Query make up

Field: END_Date
Table: Modified
Total: Group By
Sort: (Blank)
Criteria: Between [Enter Start Date:] And >=[Enter End Date]

Field: Company
Table: Modified
Total: Group By


Field: CO TOTALS: Sum((IIf([CONVALESCENT_LEAVE],1,0))+(IIf([HOSPITALIZED],1,0))+(IIf([LIGHT_DUTY],1,0))+(IIf([SIQ],1,0)))
Table: (Blank)
Sort: (Blank)
Total: Expression

----------------------------------------------------
I want to count Y/N for 7 days, Separated into their date and company, see below with out between statement

END DATE COMPANY CO TOTALS
10/8/2009 H&S 2
10/8/2009 SPT 3
10/8/2009 MT 1



EggHeadCafe - Software Developer Portal of Choice
C# Threading Http Requests
http://www.eggheadcafe.com/tutorial...69-7010659bd748/c-threading-http-request.aspx

 

[John Spencer]

Between [Enter Start Date:] And [Enter End Date]

Or

=[Enter Start Date:] And <=[Enter End Date]

Also you might want to change Total: Group By to WHERE under the End_Date field.

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

I need some help I am trying to create a query that return date values. This is how I originally set it up and I get date ranges that are earlier then what I want.

---------------------------------------------------
Query is one table query, named Modified
---------------------------------------------------
Query make up

Field: END_Date
Table: Modified
Total: Group By
Sort: (Blank)
Criteria: Between [Enter Start Date:] And >=[Enter End Date]

Field: Company
Table: Modified
Total: Group By


Field: CO TOTALS: Sum((IIf([CONVALESCENT_LEAVE],1,0))+(IIf([HOSPITALIZED],1,0))+(IIf([LIGHT_DUTY],1,0))+(IIf([SIQ],1,0)))
Table: (Blank)
Sort: (Blank)
Total: Expression

----------------------------------------------------
I want to count Y/N for 7 days, Separated into their date and company, see below with out between statement

END DATE COMPANY CO TOTALS
10/8/2009 H&S 2
10/8/2009 SPT 3
10/8/2009 MT 1



EggHeadCafe - Software Developer Portal of Choice
C# Threading Http Requests
http://www.eggheadcafe.com/tutorial...69-7010659bd748/c-threading-http-request.aspx

 

[Philip Klaerner]

John

I tried the between statement and get some of what I need there are dates that go beyond that [Enter End Date] that still have to be included. There where statements I am not to familiar with but I am still reading on the subject trying to find things I can modify and work for me.



John Spencer wrote:

Between [Enter Start Date:] And [Enter End Date]OrAlso you might want to
01-Nov-09

Between [Enter Start Date:] And [Enter End Date]

Or


Also you might want to change Total: Group By to WHERE under the End_Date field.

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

Philip Klaerner wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Asynchronous Fire and Forget Pattern Redux
http://www.eggheadcafe.com/tutorial...b-1f5810590fca/asynchronous-fire-and-for.aspx

 

[John Spencer]

Do you need further help? If so, can you explain what you want when you say
you want records that go beyond the [Enter end date]?

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

 

[John W. Vinson]

I tried the between statement and get some of what I need there are dates that go beyond that [Enter End Date] that still have to be included.

You'll need to explain that statement.

BETWEEN [Enter start date] AND [Enter end date]

will retrieve all records where the date field is greater than or equal to the
date in the first prompt, and less than or equal to the date in the second
text box. If there are dates beyond the [Enter end date] the user enters, and
you want them retrieved anyway, why ask for an end date??? What's the logic?

The one exception is that if you have a time component in the date/time field
in the table, you need to include all times *on the last date* - i.e. if the
user puts 11/02 in response to the [Enter end date] prompt, Access will get
those records up to midnight at the start of that date; #11/02/09 10:19:43#
will NOT be included. To handle this, you can use

= [Enter start date:] AND < DateAdd("d", 1, [Enter end date:])

to add one day to the date entered and get up to (but not including) midnight
on THAT date.