Date Range Query. Criteria based in a table.

  • Thread starter Thread starter Angela
  • Start date Start date
A

Angela

Hi,


I have a table in below format.

ReportName StartDate EndDate Criteria CriteriaDetail Selection
AAA 01/01/2010 29/01/2010 RANGE BETWEEN [STARTDATE] AND [ENDDATE] YES/
NO
BBB 01/01/2010 01/01/2010 SAMEDATE YES/NO
CCC 31/12/2009 31/12/2009 YESTERDAY YES/NO

As I have to adjust my resulting query according to date changes, each
time I have to edit the parameters, like in case of range, I have to
edit the between statement each time.

I was looking for a way to make changes to this table & get the
results accordingly in the query instead of editing the conditions in
a query everytime.

The selection column will decide which line I would like to use for
what report.
I can change report names & adjust them as desired. Basic requirements
of all reports are same.

Both tables have date column in common as we are drilling information
datewise.
 
Hi Angela. Do I understand that you are trying to pull sets of
records from your table based on different date ranges? If so, I
think you want to use a query with parameters. The SQL syntax would
look like this (though it could be in a query object):

Select *
from MyTable
Where StartDate between [paramDateRangeBegin] and [paramDateRangeEnd]


When you run the query you can input different begin and end dates to
pull up different sets of records. Is this what you need to do?


Karl Hoaglund, MCSD
Microsoft Access Programmer
http://www.nexuscgi.net
 
Hey Karl,

Yes I have done that already.
I was looking for giving the dates in a table & then running the query
according to that.

Thanks for the reply anyways.
 
Hi Angela. Ah, I think I'm starting to understand. You want to use
the StartDate and EndDate in the table you mentioned in your first
message to filter the records that show up in your query. You could
use a cross join for this. The syntax would be like the following, if
we assume that you want to pull records from a table called Orders
that have an EnteredDate within the date range of the selected record
in a table called ReportCriteria.

Select o.*
from Orders as o cross join ReportCriteria as c
where (c.Selected is True) and (o.EnteredDate between c.StartDate and
c.EndDate)


A warning: if more than one criteria record is selected you'll get
multiple sets of records--duplicates--so you would want to take care
that only one record is selected at time.

Hope this helps.

Karl


Karl Hoaglund, MCSD
Microsoft Access Programmer
http://www.nexuscgi.net
 
Hey Karl,

I have managed to do that.
The warning message can be avoided if I use the "reportname" for the
criteria that I choose : )

Thanks again for the sql input.. ; )
Much appreciated.
 
Back
Top