Thread: 2 questions
04-06-06, 15:19 #1Registered User
- Join Date
- Aug 2004
- Cinci, OH
Unanswered: 2 questions
I am still learning so please bare with me.
I have 17 select queries that I am running from the same table. they all run off of a 'between [start date] and [end date]'. Can I run all of them off of one report? If so how would I do that and still include the 'between [start date] and [end date]'?
When I run one of these queries I use the 'between [start date] and [end date]' so I will enter 03/01/2006 and 03/31/2006. The results will only provied dates to 03/30/2006. For me to grab the dates of 03/31 I have to use the end date of 04/01/2006. What would cause that? My other database does not do that.
Thank you very much in advance for any answers, suggestions, or information that you can provide.
04-06-06, 18:13 #2Registered User
- Join Date
- Apr 2004
- outside the rim
17 queries off one report that all use the same criteria? Are all the queries structurally the same? Are they AND or are they OR? Either way, you can probably accomplish the same thing in one query, then you have no problem with the one report.
You can also apply the "Where Clause" as a Filter to the report.
Select * From tblSource Where [MyFirstDateField] Between #1/1/2003# And #1/31/2003# And [MySecondDateField] Between #1/15/2003# And #1/28/2003#;
Probably has to do with the time (whether or not the time is part of the actual data). Technically, a value is not BETWEEN the bounds if it is equal to one of the bounds.
Hope this helps.
04-06-06, 19:28 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
the reason for the between date failing is almost certainly that you cut off point is a date, whereas the value being comapred to is a date/time
so when you specify a between 01/mar/xxxx and 31/mar/xxxx
is effectively that you are asking the db engine to return all rows which match inbetween
01/mar/xxxx 00:00:00 and 31/mar/xxxx 00:00:00
whereas what you probably want is
01/mar/xxxx 00:00:00 and 31/mar/xxxx 23:59:59
so the soultion is to either change the colun to a date type, or specify a time with it.
as regard the first question
if you are running the queries using the same date banding then it would make sense to pull the vakues for the dates from a parameter form
forms!frmparameters!txtStartDateI'd rather be riding on the Tiger 800 or the Norton