Thank you for your help, let me answer your question first I try to pull rooms that are due for inspection by putting
Between [Enter Start Date] And [Enter End date] in criteria of 1,3,6,9 and 1 year field in the query, when I did that nothing came up with no errors.
Now let me give an idea about this database, I have 4 Tables:
Basic Room Info
RoomID Bldgname Bldg# Floor Sectionname

Room Inspection
RoomID InspDate InspReason InspResult Technician

Treatment Dates
RoomID Positive Date Pre-Treat Treat Final Inspection

Adjoining Rooms
RoomID Left Right Above Below

So basically we inspect Rooms if it's positive we treat, we do final inspection and schedule room for follow up inspection 1,3,6,9 and 1 year. if neg we do nothing.

This is SQL for the query
SELECT [Basic Room Info].[RoomID], [Basic Room Info].[Bldgname], [Treatment Dates].[Positive Date], [Treatment Dates].[Final Inspection], DateAdd("m",1,[Final Inspection]) AS [1 Month Scheduled], DateAdd("m",3,[Final Inspection]) AS [3 Month Scheduled], DateAdd("m",6,[Final Inspection]) AS [6 Month Scheduled], DateAdd("m",9,[Final Inspection]) AS [9 Month Scheduled], DateAdd("yyyy",1,[Final Inspection]) AS [1 Year Scheduled]
FROM [Basic Room Info] INNER JOIN [Treatment Dates] ON [Basic Room Info].[RoomID] = [Treatment Dates].[RoomID]
WHERE (((DateAdd("m",1,[Final Inspection])) Between [Enter Start Date] And [Enter End date])) OR (((DateAdd("m",3,[Final Inspection])) Between [Enter Start Date] And [Enter End date])) OR (((DateAdd("m",6,[Final Inspection])) Between [Enter Start Date] And [Enter End date])) OR (((DateAdd("m",9,[Final Inspection])) Between [Enter Start Date] And [Enter End date])) OR (((DateAdd("yyyy",1,[Final Inspection])) Between [Enter Start Date] And [Enter End date]))
ORDER BY [Treatment Dates].[Final Inspection];
Hope this helps. Thanks again