contracts query

[ntd1]

Hi
I am trying to write a query that with display monthly income for a desired month. The problem is that it only shows the contracts that have started and ended in that month and not ongoing contract payments, that have either started in earlier months or have ended in later months.
Below is my current attempt.

Thanks in advance

SELECT Customer.CustomerNo, Customer.Forename, Customer.Surname, Contracts.ContractNo, Contracts.AddressOfWork, Contracts.MonthlyCost, Gardener.GardenerNo, Gardener.Forename, Gardener.Surname


FROM Customer, Gardener, Contracts
WHERE Customer.CustomerNo=Contracts.CustomerNo And Gardener.GardenerNo=Contracts.GardenerNo And Contracts.StartDate And Format([StartDate],"mm/yyyy")=[Enter Month/Year];

[RuralGuy]

I don't think you did a Cut 'N Paste of the SQL because it should have thrown an error as written. How do you know what are ongoing contract payments for any given date?

[ntd1]

I don't think you did a Cut 'N Paste of the SQL because it should have thrown an error as written. How do you know what are ongoing contract payments for any given date?

Once the contract starts it runs every month with the same amount until the end date.

[RuralGuy]

As a test, try something like:
And Format([Contracts.StartDate],"mm/yyyy") <= [Enter mm/yyyy]
And Format([Contracts.EndDate],"mm/yyyy") > [Enter mm/yyyy] ;
...it will ask for the date twice.

[ntd1]

As a test, try something like:
And Format([Contracts.StartDate],"mm/yyyy") <= [Enter mm/yyyy]
And Format([Contracts.EndDate],"mm/yyyy") > [Enter mm/yyyy] ;
...it will ask for the date twice.

I tried that. But it only asked for the date once? I have attached the database file so it might be clearer what the problem is. Thanks for your help.

[ntd1]

I tried that. But it only asked for the date once? I have attached the database file so it might be clearer what the problem is. Thanks for your help.

That was the wrong file. Try this one