Fiscal Period

[Michael N]

I have a table with my fiscal start date and fiscal end date and period number
Ex:
09/26/2004 (fiscalStartDate)
10/23/2004 (FiscalEndDate)
1 (Fiscal Period)

I have data that has a StartDate in another table. I would like to create
an expression or query that will asign the Fiscal period to the data in the
second table with the startDate as it falls between the FiscalStartDate and
the FiscalEndDate.

I am sure this is an easyone, but I can't quite get it right. Any Ideas?

 

[Michel Walsh]

Hi,



SELECT 0.startDate, f.*
FROM FiscalPeriods As f INNER JOIN OtherTable As o
ON o.startDate BETWEEN f.FiscalStartDate AND f.FiscalEndDate



Hoping it may help,
Vanderghast, Access MVP

 

[Michael N]

This looks like it shoud work but I am getting an error that does not make
sense unles I am missing something in my syntax:

Between Operator with AND in query expression'o.DischargeDate BETWEEN
f.FiscalStartDate'

The SQL staement is as follows:

SELECT o.dischargedate, f.*
FROM tblFiscalCalendar as f INNER JOIN qryCardiacReadmitsas o
ON o.dischargedate between f.fiscalstartdate and f.fiscalenddate;

Any thoughts?

 

[Michel Walsh]

Hi,


I assume you have a space after the qryCardiacReadmits and the keyword
as.

I would try to add parenthesis, sometimes the AND conjunction is not parsed
well, the parser seeing a standard AND criteria rather than the
continuation of the BETWEEN syntax, but if that does not work, probably:

ON o.dischargedate >= f.fiscalstartdate
AND o.dischargedate <= f.fiscalenddate


can do the job too.


Hoping it may help,
Vanderghast, Access MVP

 

[Michael N]

That worked great. Thanks for the help.

Hi,


I assume you have a space after the qryCardiacReadmits and the keyword
as.

I would try to add parenthesis, sometimes the AND conjunction is not parsed
well, the parser seeing a standard AND criteria rather than the
continuation of the BETWEEN syntax, but if that does not work, probably:

ON o.dischargedate >= f.fiscalstartdate
AND o.dischargedate <= f.fiscalenddate


can do the job too.


Hoping it may help,
Vanderghast, Access MVP