Data type mismatch problem

[ronniepurpleuk]

Hi All,

I'm not sure whether this should go elsewhere really, but the problem is on a form so I've put it in forms.

I have a Service Reminder form which is supposed to pull up customers who's services are due in the next 2 months. I can then print letters from the form to send to the customers.

I have a query in the Completion Date field to select the correct records, which is as follows:

Between DateAdd("m",([months between services]-2),Date()) And DateAdd("m",([months between services]-0),Date())

The Completion Date, in these cases, is the date at which the last service was done. Is there something wrong with the above? I've used the form before and it's worked okay - now it won't work.

Something I have found in my investigations is that I had set Months Between Services in the query to <>0. When I changed it to Is Not Null, the form opened but it displayed all customers where Months Before Servicing is 0 and none of those that are set at, for example, 6 and 12 months, who should be showing up.

What else should I be looking out for? This is doing my head in!!

With thanks

Ronnie

 

[Bungle]

Maybe I misunderstand what you're trying to do, but surely you've got your dates backwards?

Shouldn't you be selecting records where today's date Date() is

Between DateAdd("m",([months between services]-2),CompletionDate) And DateAdd("m",([months between services]-0),CompletionDate)

At the moment the only records for which your query is valid are where [months between services] is 0.