I have created a document database and want to pull a report of all upcoming documents that require review in the next month
I have a query that works until I try to filter
DocReview field is a number representing how long before review in months 1, 4 6 12 etc
VerDate is the date of the latest version of each document
the query runs ok until and gives a list of documents and their review dates as expected, until I add the criteria
Then it gives a Data Type Mismatch in query criteria error, I cant see why as it should be comapring dates with dates.
ReviewDate: DateAdd("m",[DocReview],[VerDate])
criteria
Between Date() And DateAdd("m",1,Date())
sql code
I have a query that works until I try to filter
DocReview field is a number representing how long before review in months 1, 4 6 12 etc
VerDate is the date of the latest version of each document
the query runs ok until and gives a list of documents and their review dates as expected, until I add the criteria
Then it gives a Data Type Mismatch in query criteria error, I cant see why as it should be comapring dates with dates.
ReviewDate: DateAdd("m",[DocReview],[VerDate])
criteria
Between Date() And DateAdd("m",1,Date())
sql code
Code:
SELECT DocMain.DocRef, DocMain.DocTitle, DocMain.DocType, DocMain.DocStatus, VerQryDate.MaxOfDocVerNo, VerQryDate.VerDate, DateAdd("m",[DocReview],[VerDate]) AS ReviewDate
FROM DocMain INNER JOIN VerQryDate ON DocMain.DocRef = VerQryDate.DocRef
WHERE (((DocMain.DocStatus)=True) AND ((DateAdd("m",[DocReview],[VerDate])) Between Date() And DateAdd("m",1,Date())))
ORDER BY DocMain.DocRef;