Solved Query giving date mismatch

[Lanser]

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

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;

 

[pbaldy]

Do any records have a Null VerDate?

 

[oleronesoftwares]

Between Date() And DateAdd("m",1,Date())

is it a field in a table that this criteria is based on or a function, e.g ReviewDate: DateAdd("m",[DocReview],[VerDate]), if a function then that might be the reason its given the error

 

[Lanser]

Doh, thanks Paul, yes one of the records does have a null value.
Is there any error catching I could put in place to capture this? Apart from making it a required field.

regards
John

 

[theDBguy]

Doh, thanks Paul, yes one of the records does have a null value.
Is there any error catching I could put in place to capture this? Apart from making it a required field.

regards
John

You could try using the Nz() function to assign a default date.

 

[arnelgp]

the Syntax of your DateAdd is Wrong.

 

[Lanser]

the Syntax of your DateAdd is Wrong.

In what way? It works as expected

 

[arnelgp]

In what way? It works as expected

i see those are Fieldnames.
you need to do some thinking if you want to include those without dates.

 

[Lanser]

There is a date
DocReview is a numerical field indicating the number of months to when the document should be reviewed
DocVer is the date the current document was issued so dateadd is adding x months to the issue date

 

[theDBguy]

There is a date
DocReview is a numerical field indicating the number of months to when the document should be reviewed
DocVer is the date the current document was issued so dateadd is adding x months to the issue date

Good morning. Have you tried the Nz() function, or do you need help with it? If so, what date would you like to use if the date was Null?

 

[Lanser]

Hi, there was only one without a date, not sure how it go there but I have set Date() and required as the default so new records wont have the same error
thanks
John

 

[theDBguy]

Hi, there was only one without a date, not sure how it go there but I have set Date() and required as the default so new records wont have the same error
thanks
John

So, did you just fix that one missing date?

Here's what I meant:

DateAdd("m", [DocReview], Nz([VerDate], Date()))

 

[Pat Hartman]

Did fixing the bad date fix the problem?

 

[Lanser]

Hello, thank you yes, fixing the one bad date did fix it and I also followed DBG suggestion of using NZ() just in case another slips through.

 

[theDBguy]

Hello, thank you yes, fixing the one bad date did fix it and I also followed DBG suggestion of using NZ() just in case another slips through.

Thanks for the update. Good luck with your project.

 

[Pat Hartman]

Rather than catching the null date at the end, why not add validation code in the form's beforeUpdate event to prevent the record from being saved if the date is empty?

 

[Lanser]

Rather than catching the null date at the end, why not add validation code in the form's beforeUpdate event to prevent the record from being saved if the date is empty?

data entry is managed by a different form so I also set the field in the underlying table to required and a default of 12 months
Using Nz was a belt and braces in case any user found a way round and because never used before