Update Query for Fiscal Date range

I have one table that contains Today's Date. I have a second table that
contains fiscal month dates. For example July = 6/28/2008 - 8/01/2008. I
need to populate a third table with the fiscal month beginning date and end
date based on today's date. For example today's date = 7/17/2008; I need to
populate the 3rd table with beginning date field = 6/28/2008 and ending date
field = 8/01/2008. What logic do I use to write this?

SELECT FiscalStart, FiscalEnd
FROM FiscalTable
WHERE FiscalStart = Date() and FiscalEnd = Date()

Expanding on that to do for multiple dates

SELECT FiscalStart, FiscalEnd
FROM FiscalTable, DatesTable
WHERE FiscalStart = DatesTable.SomeDate
AND FiscalEnd = DatesTable.SomeDate

OR

SELECT FiscalStart, FiscalEnd
FROM FiscalTable INNER JOIN DatesTable
ON FiscalTable.FiscalStart = DatesTable.SomeDate
AND FiscalTable.FiscalEnd = DatesTable.SomeDate



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


workweek wrote:
I have one table that contains Today's Date. I have a second table that
contains fiscal month dates. For example July = 6/28/2008 - 8/01/2008. I
need to populate a third table with the fiscal month beginning date and end
date based on today's date. For example today's date = 7/17/2008; I need to
populate the 3rd table with beginning date field = 6/28/2008 and ending date
field = 8/01/2008. What logic do I use to write this?

On Thu, 17 Jul 2008 07:46:02 -0700, workweek
wrote:

I have one table that contains Today's Date. I have a second table that
contains fiscal month dates. For example July = 6/28/2008 - 8/01/2008. I
need to populate a third table with the fiscal month beginning date and end
date based on today's date. For example today's date = 7/17/2008; I need to
populate the 3rd table with beginning date field = 6/28/2008 and ending date
field = 8/01/2008. What logic do I use to write this?

What's the actual structure of your fiscal dates table? It would be
simplest if it had four fields: FYear, FMonth, StartDate and EndDate,
with values 2008, 7 (or "July" if you prefer), #6/28/2008# and
#8/1/2008#. Also, why do you have a *TABLE* for today's date? You can
read it directly from your compter calendar using the Date() function.

You can run an Append query from my suggested date range table to
insert a record into the third table. Just use a criterion on
StartDate of = Date(), and on EndDate of = Date(). Change the query
to an Append query and append a record.

--

John W. Vinson/MVP