Update Query for Fiscal Date range

[workweek]

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?

 

[John Spencer]

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
'====================================================

 

[John W. Vinson/MVP]

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.