Datediff results in criteria expression

I have an Access query in which I have an expression that calculates the number of months since a person's employment was verified by using the DateDiff function.  The field that contains the date of the employment verification is named "EMPLOYMENT_DATE".  The expression that determines the number of months since the last verification is:

MonthsSinceEmploymentVerification: DateDiff("m",[EMPLOYMENT_DATE],Date())

This returns what looks like an integer value, and the values appear correct.  The problem is that I want to see only those records where the number of months is >6.  If I put >6 on the criteria line of the query I get a data type mismatch error.  I have tried a variety of things including wrapping EMPLOYMENT_DATE in DateValue(EMPLOYMENT_DATE), and although that appears to produce the same integer value, I can't put the criteria on it either without throwing the data type mismatch error.  

I have read the various postings in the knowledge base, but have not found anything that addresses this directly,  
Thanks in advance for your help.
Ron
rfreudAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
OK, here it is:

SELECT
  ALD.POOLNUMBER,
  ALD.EMPLOYMENT,
  ALD.EMPLOYMENT_DATE,
  DateValue([EMPLOYMENT_DATE]) AS DateValueOfEmploymentDate,
  DateDiff("m",IIf(IsDate([EMPLOYMENT_DATE]),DateValue([EMPLOYMENT_DATE]),Date()),Date()) AS MonthsSinceEmploymentVerification
FROM
  ALD
WHERE
  (((ALD.EMPLOYMENT)="Yes" Or (ALD.EMPLOYMENT)="Y")
  AND
  ((DateDiff("m",IIf(IsDate([EMPLOYMENT_DATE]),DateValue([EMPLOYMENT_DATE]),Date()),Date()))>6)
  AND
  ((IsDate([ALD].[EMPLOYMENT_DATE]))=True))
ORDER BY
  ALD.POOLNUMBER;

/gustav
0
 
serchlopCommented:
I don't know if could help to use val() function



MonthsSinceEmploymentVerification: val(DateDiff("m",[EMPLOYMENT_DATE],Date()))
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
is Employment_Date coming in as a DATE/TIME? or STRING?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
rfreudAuthor Commented:
Tried your suggestion and got the same error.
0
 
rfreudAuthor Commented:
It is coming from a string.
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
that could be your problem your problem.

do this:
DateDiff("m",DateValue ([EMPLOYMENT_DATE]),Date())

Then put in >6 in criteria.


if that fails, we can do the stronger Date covnersion, but because it's a string, it could be coming back in the wrong value set.
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
Here's a stronger string to date conversion:
cdate(format(mystringFieldThatIsInYYYYMMDDFormat, "####/##/##"))
0
 
rfreudAuthor Commented:
I tried this, and still got the same error.  I then tried creating a date value for EMPLOYMENT_DATE using this expression:
DateValueOfEmploymentDate: DateValue([EMPLOYMENT_DATE])    and then substituting
DateValueOfEmploymentDate in the original datediff expression.  

This resulted in Access asking for the Parameter Value for DateValueOfEmploymentDate
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
can you please post your query in whole again?
0
 
rfreudAuthor Commented:
Not sure I know what you mean, "in whole"...
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
THis:
MonthsSinceEmploymentVerification: DateDiff("m",[EMPLOYMENT_DATE],Date())

What does it look like now?
0
 
rfreudAuthor Commented:
MonthsSinceEmploymentVerification: DateDiff("m",[DateValueOfEmploymentDate],Date())

I created DateValueOfEomploymentDate this way:  
DateValueOfEmploymentDate: DateValue([EMPLOYMENT_DATE])
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
Try this:
MonthsSinceEmploymentVerification: DateDiff("m",DateValue([EMPLOYMENT_DATE]),Date())
0
 
rfreudAuthor Commented:
Got the same data type mismatch error.  If I take off the crieteria >6 and run the query it again runs fine and appears to deliver integer values for MonthsSinceEmploymentVerification.
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
ok...can you please send me a screenshot of your entire query window?
0
 
Jeffrey CoachmanMIS LiasonCommented:
As a shot in the dark, try it like this perhaps:

MonthsSinceEmploymentVerification: clng(DateDiff("m",[EMPLOYMENT_DATE],Date()))
0
 
rfreudAuthor Commented:
Nope - got the same data type mismatch error!
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
This maybe part of a relationship with the rest of the query.
Can you send over your SQL?
0
 
rfreudAuthor Commented:
Here is what I get when I show the SQL:

SELECT ALD.POOLNUMBER, ALD.EMPLOYMENT, ALD.EMPLOYMENT_DATE, DateValue([EMPLOYMENT_DATE]) AS DateValueOfEmploymentDate, CLng(DateDiff("m",[EMPLOYMENT_DATE],Date())) AS MonthsSinceEmploymentVerification
FROM ALD
WHERE (((ALD.EMPLOYMENT)="Yes" Or (ALD.EMPLOYMENT)="Y") AND ((ALD.EMPLOYMENT_DATE) Is Not Null And (ALD.EMPLOYMENT_DATE)<>"nAV") AND ((CLng(DateDiff("m",[EMPLOYMENT_DATE],Date())))>6))
ORDER BY ALD.POOLNUMBER;
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
EMPLOYMENT_DATE IS A STRING, right?
Can you please tell me the format of the string?
0
 
rfreudAuthor Commented:
Here is a screen capture of the EMPLOYMENT_DATE field definition in the table.  I just noticed the "@" sign in the "format" characteristic, and don't know what that is.  Maybe that's getting in the way?  This information was imported from an Excel spreadsheet, if that helps shed some light on this.
Employment-String-Capture.JPG
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
what's the format of the text string?
How is the date represented?
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
Try This:

SELECT ALD.POOLNUMBER, ALD.EMPLOYMENT, DateDiff("m",[EMPLOYMENT_DATE],Date()) AS MonthsSinceEmploymentVerification
FROM ALD
WHERE (((ALD.EMPLOYMENT)="Yes" Or (ALD.EMPLOYMENT)="Y") AND ((DateDiff("m",[EMPLOYMENT_DATE],Date()))>6))
ORDER BY ALD.POOLNUMBER;
0
 
rfreudAuthor Commented:
Still have the same error.  BTW, I opened up a fresh new query in design view and pasted your syntax into it (after adding the ALD table to the upper pat of the grid.  So I don't think that it is some sort of corruption of the object.
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
ok...there's a piece missing here.  THis should work.

Please send me an example out put of that table.
New-Microsoft-Access-Database.accdb
0
 
rfreudAuthor Commented:
I opened your sample (thank you!!)  and when I ran the query with the rows you have there, it ran just fine.  When I added a record and noted the EMPLOYMENT_DATE as "NAV" and ran the query, it produced the same "data mismatch error".

Time for dinner now, and while I am tremendously grateful for your help, I don't think I will be able to continue this discussion throughout the evening.  Can we pick this back up in the AM?

Thanks,
Ron
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
no problem.

When you get back...please explain "AS NAV"
I didn't see that in your SQL:

SELECT ALD.POOLNUMBER, ALD.EMPLOYMENT, ALD.EMPLOYMENT_DATE, DateValue([EMPLOYMENT_DATE]) AS DateValueOfEmploymentDate, CLng(DateDiff("m",[EMPLOYMENT_DATE],Date())) AS MonthsSinceEmploymentVerification
FROM ALD
WHERE (((ALD.EMPLOYMENT)="Yes" Or (ALD.EMPLOYMENT)="Y") AND ((ALD.EMPLOYMENT_DATE) Is Not Null And (ALD.EMPLOYMENT_DATE)<>"nAV") AND ((CLng(DateDiff("m",[EMPLOYMENT_DATE],Date())))>6))
ORDER BY ALD.POOLNUMBER;
0
 
Gustav BrockCIOCommented:
This will work:

SELECT
  ALD.POOLNUMBER,
  ALD.EMPLOYMENT,
  ALD.EMPLOYMENT_DATE,
  DateValue([EMPLOYMENT_DATE]) AS DateValueOfEmploymentDate,
  DateDiff("m",DateValue([EMPLOYMENT_DATE]),Date())) AS MonthsSinceEmploymentVerification
FROM
  ALD
WHERE
  (ALD.EMPLOYMENT="Yes" Or ALD.EMPLOYMENT="Y")
  AND
  IsDate(ALD.EMPLOYMENT_DATE) = True
  AND
  DateDiff("m",DateValue([EMPLOYMENT_DATE]),Date()) > 6
ORDER BY
  ALD.POOLNUMBER;

/gustav
0
 
rfreudAuthor Commented:
Good morning -
This comment addresses both arcee123 and cactus_data's comments.

As to the "NAV" question:  I added a record to the sample database file you prepared.  I entered "Y" in  the EMPLOYMENT column and "NAV" in the EMPLOYMENT_DATE column (see attached file).  In the attached file I have labeled the two queries with the names of the contributors (both arcee123 and cactus_data).

As to cactus-data's solution, I pasted the SQL statement in and when I ran the query it gave me and error explaining that there was an extra parenthesis in the syntax, which was located just after the date().  I removed it and when I ran the query it still gave me the same data type mismatch in criteria expression error.  

I am on the east coast and at 1 PM eastern time I need to go to a meeting that will last until 4 PM eastern time, so I will be out of pocket during those hours.  Otherwise I am available to work on this with you and I REALLY appreciate your assistance.
Thanks,
Ron
Expert-Solution.accdb
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
AAAAHHHHH.....
You put the value "NAV" in the employment_Date column.....
This is why it is failing.

ok.  Is NAV the only value you use besides dates?
if not, what else goes into that column?
0
 
rfreudAuthor Commented:
To arcee123 - in the messy world of spreadsheet users, this field could contain almost any nonsense that they put in there.  Most commonly they put in "NAV" or "NAP".

The solution provided by gustav worked!  I can't tell you how great this is to have this help.  

If I am interpreting this correctly, the key was to have both the "IsDate" be True AND to have the IsDate tested in the the DateDiff expression using the IIF statement so that the DateDiff function only tries to evaluate actual dates.

It also appears that the "DateValueOfEmploymentDate" is superfluous and could be removed?

Thanks again,
Ron
0
 
rfreudAuthor Commented:
Both contributors to this solution helped me a lot, and I really appreciate how quick they were to address this issue and to stick with it when the first possibilities didn't pan out.  Great help - thanks!
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
Gustav got to it before I did...
i was just constructing it.

Good stuff Gustav.
He gets credit.
0
 
Gustav BrockCIOCommented:
Thanks, you are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.