Parameter query on calculated field

[eddie442]

I hope someone can help as I am stumped at present. I have created a calculated query using dateadd. I have a table that has date fields, ServiceContractAgreed and ServiceFrequency. The service frequency field holds a value of 3,6,9 or 12 in months. Using dateadd I can add the months to the ServiceContractAgreed field in a field called NextService, ie =IIf(IsNull([ServiceContractAgreed]),"N/A",DateAdd("m",[ServiceFrequency],[ServiceContractAgreed]))
This works fine but what I want to do now is create a parameter query to search the NextService field, using the between operator to search between dates. I've created a new query to do this but get every record back, no matter what dates I put in. It's probably something really simple to do but I'd be really grateful for any advice!

Cheers

Dave

 

[Jon K]

You can use Null instead of "N/A". "N/A" would change NextService into text strings.

IIf(IsNull([ServiceContractAgreed]), Null, DateAdd("m",[ServiceFrequency],[ServiceContractAgreed]))
.

 

[eddie442]

Cheers for that, that was just the job!

Dave