Right Join query with Between HELP

[Giorgio]

Hi,
I have a table with a date_created and date_expired and I want to
look
at the 15th of each month and every month since the system started
and
count up how many accounts were active at each of those points in
time.
Will have to assume that if there's an expiry date bigger than today
then that account was active at each of those points in time.
I created a numbers table from 1 to 100.

I have the following code working well in SQL2000 but I can't make it
work in Access! Can someone tell me how to make this work in Access
please?

SELECT The15th, COUNT(Account_Created) AS Cnt
FROM AccountsHistorical
RIGHT JOIN (SELECT DATEADD(month,n,'20020703') as The15th FROM
Numbers) x
ON The15th BETWEEN Account_Created AND Accounts_Expire
GROUP BY The15th
ORDER BY The15th

 

[Michel Walsh]

Try to change:


DATEADD(month,n,'20020703')



to:


DateAdd("m", n, #07/03/2002#)



to add the amount of months stored in the field n , to the third of July
2002.



Hoping it may help,
Vanderghast, Access MVP

 

[Giorgio]

Try to change:

DATEADD(month,n,'20020703')

to:

DateAdd("m", n, #07/03/2002#)

to add the amount of months stored in the field n , to the third of July
2002.

Hoping it may help,
Vanderghast, Access MVP








- Show quoted text -

Didn't work!
The error message I get is - Between operator without And in query
expression 'The15th BETWEEN Account_Created'.

 

[Michel Walsh]

Change:

.... ) x ON The15th BETWEEN Account_Created AND Accounts_Expire


into:

.... ) AS x ON (The15th BETWEEN Account_Created AND Accounts_Expire)


or even into:

.... ) AS x ON (x.The15th BETWEEN AccountsHistorical.Account_Created AND
AccountsHistorical.Accounts_Expire)


which added the key word AS, a pair of parenthesis, and also added the
tables names to the implied fields, in the ON clause.


Hoping it may help,
Vanderghast, Access MVP