Stuck with this Query

[MrMcFestoe]

I have a query that i cant get to work, so iam about to give up, any help or ideas on this would be great.

I have a field called LAST_SERVICE_VISIT which displays the date a service was made on a system ddmmyyyy this works fine and pulls the information from a subform.

I also have a field SERVICE_FREQ which is a combo box and can equal 3, 6, 12 months.

what iam trying to do is have a query list the systems that have not been serviced within the SERVICE_FREQ of the date the query is run.

so if SERVICE_FREQ = 12 then list all systems that have not been serviced in the last 12months of the current date.

does that make sense.

Thanks

 

[r937]

[tt]select somecolumns
from thetable
where (
SERVICE_FREQ = 3
and LAST_SERVICE_VISIT < dateadd(&quot;m&quot;,-3,Date())
)
or (
SERVICE_FREQ = 6
and LAST_SERVICE_VISIT < dateadd(&quot;m&quot;,-6,Date())
)
or (
SERVICE_FREQ = 12
and LAST_SERVICE_VISIT < dateadd(&quot;m&quot;,-12,Date())
)[/tt]

rudy

http://r937.com/

 

[MrMcFestoe]

rudy

thanks for the quick reply, where does it go. Does it go in the query (desgin view)??

Thanks

 

[r937]

yes... Query, Design, SQL View

paste into the window and run it