Greater than in DateDiff

[Cathy]

Hello,

I am requesting help with a DateDiff Calc. I would like a query to return
only records that are over 45 days. I have put >45 in the Criteria and keep
getting a Data Mismatch in expression error.

Can anyone help.

Much appreciation,
Cathy

 

[Rick B]

You have put >45 in the criteria for what?

What field? Do you have a field that calculates the age in days?

 

[Cathy]

Rick B, Thank you for your prompt response.

A column in the Query has the DateDiff:

I built this as a separate Expression in an additional column being

Expr1: DateDiff("d",[Date1],[Date2])

and then in the criteria below in the same column I put >45 to pull only
records that are over 45 days.

I hope this makes sense. Or possible should the go into a separate column?

Cathy

 

[Rick B]

I went to the Northwinds database and created a query using the Employee
table. I added a column...
age in days: DateDiff("d",[Birthdate],Date())


And put in critera...

16000

and I got the expected results. What happens if you take out the criteria
and run it? Do you get the results you expect? Do you have your dates
backward so you are getting anegative number? If so, then no records would
be >45 days, but you might have some <-45 days.

Hope that is the case.

Rick B

Rick B, Thank you for your prompt response.

A column in the Query has the DateDiff:

I built this as a separate Expression in an additional column being

Expr1: DateDiff("d",[Date1],[Date2])

and then in the criteria below in the same column I put >45 to pull only
records that are over 45 days.

I hope this makes sense. Or possible should the go into a separate column?

Cathy

You have put >45 in the criteria for what?

What field? Do you have a field that calculates the age in days?




and
keep

 

[Cathy]

Hi Rick,

I pull the criteria out and the DateDiff Function does work and returns all
records with no Data Mismatch. But as soon as I put the >45 in, and try to
view and the Data Mismatch error pops up again.

You did get me thinking and I reviewed all the data to make sure there were
no text in the date fields and wrong formats and found a few issues and fixed
them, but still remain to be stuck in neutral on this with the Data Mismatch
error continuing to pop up.

Thanks, Cathy

I went to the Northwinds database and created a query using the Employee
table. I added a column...
age in days: DateDiff("d",[Birthdate],Date())


And put in critera...

16000

and I got the expected results. What happens if you take out the criteria
and run it? Do you get the results you expect? Do you have your dates
backward so you are getting anegative number? If so, then no records would
be >45 days, but you might have some <-45 days.

Hope that is the case.

Rick B

Rick B, Thank you for your prompt response.

A column in the Query has the DateDiff:

I built this as a separate Expression in an additional column being

Expr1: DateDiff("d",[Date1],[Date2])

and then in the criteria below in the same column I put >45 to pull only
records that are over 45 days.

I hope this makes sense. Or possible should the go into a separate column?

Cathy

You have put >45 in the criteria for what?

What field? Do you have a field that calculates the age in days?




Hello,

I am requesting help with a DateDiff Calc. I would like a query to return
only records that are over 45 days. I have put >45 in the Criteria and
keep
getting a Data Mismatch in expression error.

Can anyone help.

Much appreciation,
Cathy

 

[Rick B]

I'm lost. Do you have any null dates? When you pull out the criteria and
run the query do you get a "days" result for every record in the database?

Hi Rick,

I pull the criteria out and the DateDiff Function does work and returns all
records with no Data Mismatch. But as soon as I put the >45 in, and try to
view and the Data Mismatch error pops up again.

You did get me thinking and I reviewed all the data to make sure there were
no text in the date fields and wrong formats and found a few issues and fixed
them, but still remain to be stuck in neutral on this with the Data Mismatch
error continuing to pop up.

Thanks, Cathy

I went to the Northwinds database and created a query using the Employee
table. I added a column...
age in days: DateDiff("d",[Birthdate],Date())


And put in critera...

16000

and I got the expected results. What happens if you take out the criteria
and run it? Do you get the results you expect? Do you have your dates
backward so you are getting anegative number? If so, then no records would
be >45 days, but you might have some <-45 days.

Hope that is the case.

Rick B

Rick B, Thank you for your prompt response.

A column in the Query has the DateDiff:

I built this as a separate Expression in an additional column being

Expr1: DateDiff("d",[Date1],[Date2])

and then in the criteria below in the same column I put >45 to pull only
records that are over 45 days.

I hope this makes sense. Or possible should the go into a separate column?

Cathy

:

You have put >45 in the criteria for what?

What field? Do you have a field that calculates the age in days?




Hello,

I am requesting help with a DateDiff Calc. I would like a query

to
return

only records that are over 45 days. I have put >45 in the

Criteria
and

keep
getting a Data Mismatch in expression error.

Can anyone help.

Much appreciation,
Cathy

 

[Cathy]

Well I surely appreciate your effort.

No, I set it up so [Date1] Is Not Null and as I said [Date2] is today being
Date(). When I pull out the criteria all records for the "days" return a
number value.

Cathy

I'm lost. Do you have any null dates? When you pull out the criteria and
run the query do you get a "days" result for every record in the database?

Hi Rick,

I pull the criteria out and the DateDiff Function does work and returns all
records with no Data Mismatch. But as soon as I put the >45 in, and try to
view and the Data Mismatch error pops up again.

You did get me thinking and I reviewed all the data to make sure there were
no text in the date fields and wrong formats and found a few issues and fixed
them, but still remain to be stuck in neutral on this with the Data Mismatch
error continuing to pop up.

Thanks, Cathy

I went to the Northwinds database and created a query using the Employee
table. I added a column...
age in days: DateDiff("d",[Birthdate],Date())


And put in critera...
16000


and I got the expected results. What happens if you take out the criteria
and run it? Do you get the results you expect? Do you have your dates
backward so you are getting anegative number? If so, then no records would
be >45 days, but you might have some <-45 days.

Hope that is the case.

Rick B



Rick B, Thank you for your prompt response.

A column in the Query has the DateDiff:

I built this as a separate Expression in an additional column being

Expr1: DateDiff("d",[Date1],[Date2])

and then in the criteria below in the same column I put >45 to pull only
records that are over 45 days.

I hope this makes sense. Or possible should the go into a separate
column?

Cathy

:

You have put >45 in the criteria for what?

What field? Do you have a field that calculates the age in days?




Hello,

I am requesting help with a DateDiff Calc. I would like a query to
return
only records that are over 45 days. I have put >45 in the Criteria
and
keep
getting a Data Mismatch in expression error.

Can anyone help.

Much appreciation,
Cathy

 

[John Vinson]

Well I surely appreciate your effort.

No, I set it up so [Date1] Is Not Null and as I said [Date2] is today being
Date(). When I pull out the criteria all records for the "days" return a
number value.

Cathy

Cathy, please open your Query in SQL view and post the SQL text here.
Rick or someone else may be able to find the problem if we can see the
whole query.

John W. Vinson[MVP]

 

[Cathy]

John,

Thank you, here it is:

SELECT tQuery.FileID, tQuery.BusUnit, tQuery.FileType, tQuery.DecisionDt,
tQuery.Disposition, [MComplete Date].QuesLong, [MComplete Date].Answer,
PTToLegalDate.QuesLong, PTToLegalDate.Answer, PTFromLegalDate.QuesLong,
PTFromLegalDate.Answer, PTToVPDate.QuesLong, PTToVPDate.Answer,
PTFromVPDate.QuesLong, PTFromVPDate.Answer, PTExcpDate.QuesLong,
PTExcpDate.Answer, PTtoAPDate.QuesLong, PTtoAPDate.Answer,
PTtoCustomerDate.QuesLong, PTtoCustomerDate.Answer, [MProcess End
Date2].QuesLong, [MProcess End Date2].Answer, Date() AS Expr2,
DateDiff("d",[MComplete Date.Answer],Date()) AS Expr3, MNoLA.QuesLong,
MNoLA.Answer
FROM ([MComplete Date] INNER JOIN (PTExcpDate INNER JOIN ((((((PTToLegalDate
INNER JOIN PTFromLegalDate ON PTToLegalDate.FileID = PTFromLegalDate.FileID)
INNER JOIN PTToVPDate ON (PTToLegalDate.FileID = PTToVPDate.FileID) AND
(PTFromLegalDate.FileID = PTToVPDate.FileID)) INNER JOIN PTFromVPDate ON
(PTToLegalDate.FileID = PTFromVPDate.FileID) AND (PTFromLegalDate.FileID =
PTFromVPDate.FileID) AND (PTToVPDate.FileID = PTFromVPDate.FileID)) INNER
JOIN PTtoAPDate ON (PTToLegalDate.FileID = PTtoAPDate.FileID) AND
(PTFromLegalDate.FileID = PTtoAPDate.FileID) AND (PTToVPDate.FileID =
PTtoAPDate.FileID) AND (PTFromVPDate.FileID = PTtoAPDate.FileID)) INNER JOIN
PTtoCustomerDate ON (PTToLegalDate.FileID = PTtoCustomerDate.FileID) AND
(PTFromLegalDate.FileID = PTtoCustomerDate.FileID) AND (PTToVPDate.FileID =
PTtoCustomerDate.FileID) AND (PTFromVPDate.FileID = PTtoCustomerDate.FileID)
AND (PTtoAPDate.FileID = PTtoCustomerDate.FileID)) INNER JOIN [MProcess End
Date2] ON (PTToLegalDate.FileID = [MProcess End Date2].FileID) AND
(PTFromLegalDate.FileID = [MProcess End Date2].FileID) AND (PTToVPDate.FileID
= [MProcess End Date2].FileID) AND (PTFromVPDate.FileID = [MProcess End
Date2].FileID) AND (PTtoAPDate.FileID = [MProcess End Date2].FileID) AND
(PTtoCustomerDate.FileID = [MProcess End Date2].FileID)) ON
(PTExcpDate.FileID = [MProcess End Date2].FileID) AND (PTExcpDate.FileID =
PTtoCustomerDate.FileID) AND (PTExcpDate.FileID = PTtoAPDate.FileID) AND
(PTExcpDate.FileID = PTFromVPDate.FileID) AND (PTExcpDate.FileID =
PTToVPDate.FileID) AND (PTExcpDate.FileID = PTToLegalDate.FileID) AND
(PTExcpDate.FileID = PTFromLegalDate.FileID)) ON ([MComplete Date].FileID =
PTToLegalDate.FileID) AND ([MComplete Date].FileID = PTFromLegalDate.FileID)
AND ([MComplete Date].FileID = PTToVPDate.FileID) AND ([MComplete
Date].FileID = PTFromVPDate.FileID) AND ([MComplete Date].FileID =
PTtoAPDate.FileID) AND ([MComplete Date].FileID = PTtoCustomerDate.FileID)
AND ([MComplete Date].FileID = [MProcess End Date2].FileID) AND ([MComplete
Date].FileID = PTExcpDate.FileID)) INNER JOIN (MNoLA INNER JOIN tQuery ON
(MNoLA.FileID = tQuery.FileID) AND (MNoLA.FileID = tQuery.FileID) AND
(MNoLA.FileID = tQuery.FileID)) ON (PTToLegalDate.FileID = tQuery.FileID) AND
(PTFromLegalDate.FileID = tQuery.FileID) AND (PTToVPDate.FileID =
tQuery.FileID) AND (PTFromVPDate.FileID = tQuery.FileID) AND
(PTtoAPDate.FileID = tQuery.FileID) AND (PTtoCustomerDate.FileID =
tQuery.FileID) AND ([MComplete Date].FileID = tQuery.FileID) AND
(PTExcpDate.FileID = tQuery.FileID) AND ([MProcess End Date2].FileID =
tQuery.FileID) AND ([MComplete Date].FileID = tQuery.FileID)
WHERE ((([MComplete Date].Answer) Is Not Null) AND ((DateDiff("d",[MComplete
Date.Answer],Date()))>45));

Cathy

Well I surely appreciate your effort.

No, I set it up so [Date1] Is Not Null and as I said [Date2] is today being
Date(). When I pull out the criteria all records for the "days" return a
number value.

Cathy

Cathy, please open your Query in SQL view and post the SQL text here.
Rick or someone else may be able to find the problem if we can see the
whole query.

John W. Vinson[MVP]

 

[Cathy]

I found one of my relationships to be incorrect and I pulled out one of the
querys that would have had conflicting info with another.

This is good enough at this point because now it seems to be working.

I thank you for all your effort.

Cathy

John,

Thank you, here it is:

SELECT tQuery.FileID, tQuery.BusUnit, tQuery.FileType, tQuery.DecisionDt,
tQuery.Disposition, [MComplete Date].QuesLong, [MComplete Date].Answer,
PTToLegalDate.QuesLong, PTToLegalDate.Answer, PTFromLegalDate.QuesLong,
PTFromLegalDate.Answer, PTToVPDate.QuesLong, PTToVPDate.Answer,
PTFromVPDate.QuesLong, PTFromVPDate.Answer, PTExcpDate.QuesLong,
PTExcpDate.Answer, PTtoAPDate.QuesLong, PTtoAPDate.Answer,
PTtoCustomerDate.QuesLong, PTtoCustomerDate.Answer, [MProcess End
Date2].QuesLong, [MProcess End Date2].Answer, Date() AS Expr2,
DateDiff("d",[MComplete Date.Answer],Date()) AS Expr3, MNoLA.QuesLong,
MNoLA.Answer
FROM ([MComplete Date] INNER JOIN (PTExcpDate INNER JOIN ((((((PTToLegalDate
INNER JOIN PTFromLegalDate ON PTToLegalDate.FileID = PTFromLegalDate.FileID)
INNER JOIN PTToVPDate ON (PTToLegalDate.FileID = PTToVPDate.FileID) AND
(PTFromLegalDate.FileID = PTToVPDate.FileID)) INNER JOIN PTFromVPDate ON
(PTToLegalDate.FileID = PTFromVPDate.FileID) AND (PTFromLegalDate.FileID =
PTFromVPDate.FileID) AND (PTToVPDate.FileID = PTFromVPDate.FileID)) INNER
JOIN PTtoAPDate ON (PTToLegalDate.FileID = PTtoAPDate.FileID) AND
(PTFromLegalDate.FileID = PTtoAPDate.FileID) AND (PTToVPDate.FileID =
PTtoAPDate.FileID) AND (PTFromVPDate.FileID = PTtoAPDate.FileID)) INNER JOIN
PTtoCustomerDate ON (PTToLegalDate.FileID = PTtoCustomerDate.FileID) AND
(PTFromLegalDate.FileID = PTtoCustomerDate.FileID) AND (PTToVPDate.FileID =
PTtoCustomerDate.FileID) AND (PTFromVPDate.FileID = PTtoCustomerDate.FileID)
AND (PTtoAPDate.FileID = PTtoCustomerDate.FileID)) INNER JOIN [MProcess End
Date2] ON (PTToLegalDate.FileID = [MProcess End Date2].FileID) AND
(PTFromLegalDate.FileID = [MProcess End Date2].FileID) AND (PTToVPDate.FileID
= [MProcess End Date2].FileID) AND (PTFromVPDate.FileID = [MProcess End
Date2].FileID) AND (PTtoAPDate.FileID = [MProcess End Date2].FileID) AND
(PTtoCustomerDate.FileID = [MProcess End Date2].FileID)) ON
(PTExcpDate.FileID = [MProcess End Date2].FileID) AND (PTExcpDate.FileID =
PTtoCustomerDate.FileID) AND (PTExcpDate.FileID = PTtoAPDate.FileID) AND
(PTExcpDate.FileID = PTFromVPDate.FileID) AND (PTExcpDate.FileID =
PTToVPDate.FileID) AND (PTExcpDate.FileID = PTToLegalDate.FileID) AND
(PTExcpDate.FileID = PTFromLegalDate.FileID)) ON ([MComplete Date].FileID =
PTToLegalDate.FileID) AND ([MComplete Date].FileID = PTFromLegalDate.FileID)
AND ([MComplete Date].FileID = PTToVPDate.FileID) AND ([MComplete
Date].FileID = PTFromVPDate.FileID) AND ([MComplete Date].FileID =
PTtoAPDate.FileID) AND ([MComplete Date].FileID = PTtoCustomerDate.FileID)
AND ([MComplete Date].FileID = [MProcess End Date2].FileID) AND ([MComplete
Date].FileID = PTExcpDate.FileID)) INNER JOIN (MNoLA INNER JOIN tQuery ON
(MNoLA.FileID = tQuery.FileID) AND (MNoLA.FileID = tQuery.FileID) AND
(MNoLA.FileID = tQuery.FileID)) ON (PTToLegalDate.FileID = tQuery.FileID) AND
(PTFromLegalDate.FileID = tQuery.FileID) AND (PTToVPDate.FileID =
tQuery.FileID) AND (PTFromVPDate.FileID = tQuery.FileID) AND
(PTtoAPDate.FileID = tQuery.FileID) AND (PTtoCustomerDate.FileID =
tQuery.FileID) AND ([MComplete Date].FileID = tQuery.FileID) AND
(PTExcpDate.FileID = tQuery.FileID) AND ([MProcess End Date2].FileID =
tQuery.FileID) AND ([MComplete Date].FileID = tQuery.FileID)
WHERE ((([MComplete Date].Answer) Is Not Null) AND ((DateDiff("d",[MComplete
Date.Answer],Date()))>45));

Cathy

Well I surely appreciate your effort.

No, I set it up so [Date1] Is Not Null and as I said [Date2] is today being
Date(). When I pull out the criteria all records for the "days" return a
number value.

Cathy

Cathy, please open your Query in SQL view and post the SQL text here.
Rick or someone else may be able to find the problem if we can see the
whole query.

John W. Vinson[MVP]

 

[John Vinson]

This is good enough at this point because now it seems to be working.

Good... that was a lot of SQL to debug! Glad you got it working, and
Merry Christmas!

John W. Vinson[MVP]

 

[Cathy]

Merry Christmas to you John.

Not to be a bummer, but it worked for only a few minutes and then it
started giving me the error again "Datatype Mismatch" in the query.
Sometimes when I would click on it, it would open the query, look correct and
then the error would pop up proceeding to change all fields with the word
"Name?" in each field.

Does this mean I have something named wrong or is this just too many
subqueries connected for Access to decipher?

Thanks again,
Cathy

 

[John Vinson]

Merry Christmas to you John.

Not to be a bummer, but it worked for only a few minutes and then it
started giving me the error again "Datatype Mismatch" in the query.
Sometimes when I would click on it, it would open the query, look correct and
then the error would pop up proceeding to change all fields with the word
"Name?" in each field.

Does this mean I have something named wrong or is this just too many
subqueries connected for Access to decipher?

It may just be! It's certainly too complex for me to "desk check",
especially without knowing the logic or the tables involved. It's one
of the most complex queries I've seen in terms of number of tables and
complexity of joins.

I'm sorry but it's beyond my skills to help at this point! Maybe you
should repost the SQL with a different subject line and an explanation
of the problem; I'll alert some of the real SQL gurus to take a look
at it.

John W. Vinson[MVP]

 

[Cathy]

I just rebuilt the query from scratch. Now it works.

I was still getting Datamismatch errors when I would put in the >45 in the
criteria. There was another post in here the other day regarding gibberish
on a query and I was having the same problem with a different query I was
working on. The response on that recent post said to group the columns and
select "First" as the grouping method for the problem column(s). I did that
on my other query which worked therefore I tried it on this one, and that
also fixed the Datamismatch error.

I don't know why, but wonderful, it works for me. "Greater than in DateDiff
~ put to rest".

Thank you so much and Happy New Year!

Cathy