BETWEEN in a FROM Statement

[Cyborg]

Hello,



I'm having trouble using BETWEEN in a FROM statement. The query is pretty simple and any help would be greatly appreciated.

This is a .accdb Access 2007 db
Error message I get: Between operator without And in query expression tblOrders.DeliveryDate BETWEEN tblFiscalCalendar.WeekStart

SELECT tblFiscalCalendar.FiscalYear, tblFiscalCalendar.Period, Sum([tblInvoiceLines]![PricePerUnit]*[tblInvoiceLines]![QuantityShipped]) AS Sales INTO SalesByFiscalPeriod

FROM tblFiscalCalendar INNER JOIN (tblOrders INNER JOIN tblInvoiceLines ON (tblOrders.DeliveryDate = tblInvoiceLines.DeliveryDate) AND (tblOrders.OrderNumber = tblInvoiceLines.OrderNumber)) ON tblOrders.DeliveryDate BETWEEN tblFiscalCalendar.WeekStart AND tblFiscalCalendar.WeekEnd

WHERE (((tblOrders.ChainCode)="WNE") AND (([tblFiscalCalendar].[FiscalYear])=2009 Or ([tblFiscalCalendar].[FiscalYear])=2010 Or ([tblFiscalCalendar].[FiscalYear])=2011))

GROUP BY tblFiscalCalendar.FiscalYear, tblFiscalCalendar.Period;

[ajetrumpet]

did you create this with the qry wizard?

[Cyborg]

did you create this with the qry wizard?

No. I started in design view but edited the sql to try to get that join to work. Doesn't appear to be possible using the ui but i'm no expert

[ajetrumpet]

never written something like this. But I'm guessing it's not going to happen. sorry!

[pbaldy]

I'm not sure you can use Between in the join. You can use >= & <=

[Cyborg]

Thanks pbaldy! That totally just worked. Didn't think to do that.
I know i've seen between used in T-SQL

Much appreciated!!!

[pbaldy]

No problem, and welcome to the site!