Start Free Trial

asked on

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

I don't know if could help to use val() function

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

is Employment_Date coming in as a DATE/TIME? or STRING?

ASKER

Tried your suggestion and got the same error.

ASKER

It is coming from a string.

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.

Here's a stronger string to date conversion:
cdate(format(mystringFieldThatIsInYYYYMMDDFormat, "####/##/##"))

ASKER

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

can you please post your query in whole again?

ASKER

Not sure I know what you mean, "in whole"...

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

What does it look like now?

ASKER

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

I created DateValueOfEomploymentDate this way:
DateValueOfEmploymentDate: DateValue([EMPLOYMENT_DATE])

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

ASKER

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.

ok...can you please send me a screenshot of your entire query window?

Jeffrey Coachman

As a shot in the dark, try it like this perhaps:

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

ASKER

Nope - got the same data type mismatch error!

This maybe part of a relationship with the rest of the query.
Can you send over your SQL?

ASKER

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;

EMPLOYMENT_DATE IS A STRING, right?
Can you please tell me the format of the string?

ASKER

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

what's the format of the text string?
How is the date represented?

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;

ASKER

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.

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

ASKER

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

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;

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

ASKER

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

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?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

ASKER

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!

Gustav got to it before I did...
i was just constructing it.

Good stuff Gustav.
He gets credit.

Thanks, you are welcome!

/gustav