Date Query ...

[eword]

I have a stored sql query which should return information between dates set in a table called TblReportDates. These dates are [Start Date] and [End Date]. The issue I have is that each time I run the query it prompts me for the values of [Start Date] and [End Date] rather than using what is stored in the table. I cant see any spelling errors. Here is the query. It works fine when the dates are set by hand.


SELECT TblDividendPayments.Owner, TblDividendPayments.Security, TblDividendPayments.[Tax Date], TblDividendPayments.[Amount Received], TblDividendPayments.[DRP Amount], TblDividendPayments.[Unfranked Amount], TblDividendPayments.[Franked Amount], TblDividendPayments.[Interest Amount], TblDividendPayments.[Tax Free], TblDividendPayments.[Tax Deferred], TblDividendPayments.[CGT Gains], TblDividendPayments.[Foreign Income], TblDividendPayments.[Traditional Income], TblDividendPayments.[Tax Credit Amount], TblDividendPayments.[Foreign Tax Credit]
FROM TblDividendPayments
WHERE (((TblDividendPayments.Owner)="Smith" Or (TblDividendPayments.Owner)="margin")) AND TblDividendPayments.[Tax Date] BETWEEN [TblReportDates].[Start Date] And [TblReportDates].[End Date];

 

[pbaldy]

The problem is that table isn't in the FROM clause. If it only has one record, you can do this:

FROM TblDividendPayments, TblReportDates

If it has more than one record, that will give you a Cartesian product.