Data Mismatch

[bhipwell via AccessMonster.com]

Need some guidance.

I have a query where I am trying to extract a number based on where what age
range a person's age is. I have the query all set up (without any criteria
yet) and the data required to the calculation I need all pulls on the
datasheet as needed.

There is a column for AGE. The AGE is pulled from a query which calculates
each individuals age.

If I apply "ascending" to the Sort option for the AGE column in my new query,
I get the "Data mismatch" error.

If I remove the "ascending" from the Sort option, the datashet opens no
problem.

Thoughts?

BH

 

[Duane Hookom]

I expect there are some records in your "query which calculates each
individuals age" that can't calculate the age. Possibly a date of birth field
might be null.

If you can't figure this out, come back with some SQL views and your method
for calculating Age.

 

[bhipwell via AccessMonster.com]

SQL for age:

SELECT tblEmployee.EmployeeID, tblEmployee.EmployeeBirthday, tblEmployer.
EmployerID, tblEmployer.EmployerEffectiveDate, DateDiff("yyyy",
[EmployeeBirthday],Date())+(Format([EmployeeBirthday],"mmdd")>Format(Date(),
"mmdd")) AS CalcAge, DateDiff("yyyy",[EmployeeBirthday],Date())+(Format(
[EmployeeBirthday],"mmdd")>Format(Date(),"mmdd")) AS ActualAge, Int(Format(
[EmployerEffectiveDate],"yyyy.mmdd")-Format([EmployeeBirthday],"yyyy.mmdd"))
AS Age, [ActualAge]+IIf((Format([EmployeeBirthday],"mmdd"))<(Format(
[EmployerEffectiveDate],"mmdd")) And (Format([EmployeeBirthday],"mmdd"))
<Format(Date(),"mmdd"),-1,0) AS OldAge, tblEmployee.EmployeeLastName
FROM tblEmployer INNER JOIN tblEmployee ON tblEmployer.EmployerID =
tblEmployee.EmployerID;

SQL where I get data mismatch when ascending age:

SELECT tblEmployee.EmployeeLastName, tblReductions.ReductionAmount AS LIFE,
tblReductions.PlansID, tblReductions.ReductionAmount, QryEmployeeAge.Age,
tblReductions.ReductionID, tblEmployee.EmployerID
FROM (tblEmployee INNER JOIN QryEmployeeAge ON tblEmployee.EmployeeID =
QryEmployeeAge.EmployeeID) INNER JOIN tblReductions ON tblEmployee.LifeID =
tblReductions.PlansID
ORDER BY QryEmployeeAge.Age;

If you take out the order by statement, the datasheet works fine.

 

[bhipwell via AccessMonster.com]

SQL for age:

SELECT tblEmployee.EmployeeID, tblEmployee.EmployeeBirthday, tblEmployer.
EmployerID, tblEmployer.EmployerEffectiveDate, DateDiff("yyyy",
[EmployeeBirthday],Date())+(Format([EmployeeBirthday],"mmdd")>Format(Date(),
"mmdd")) AS CalcAge, DateDiff("yyyy",[EmployeeBirthday],Date())+(Format(
[EmployeeBirthday],"mmdd")>Format(Date(),"mmdd")) AS ActualAge, Int(Format(
[EmployerEffectiveDate],"yyyy.mmdd")-Format([EmployeeBirthday],"yyyy.mmdd"))
AS Age, [ActualAge]+IIf((Format([EmployeeBirthday],"mmdd"))<(Format(
[EmployerEffectiveDate],"mmdd")) And (Format([EmployeeBirthday],"mmdd"))
<Format(Date(),"mmdd"),-1,0) AS OldAge, tblEmployee.EmployeeLastName
FROM tblEmployer INNER JOIN tblEmployee ON tblEmployer.EmployerID =
tblEmployee.EmployerID;

SQL where I get data mismatch when ascending age:

SELECT tblEmployee.EmployeeLastName, tblReductions.ReductionAmount AS LIFE,
tblReductions.PlansID, tblReductions.ReductionAmount, QryEmployeeAge.Age,
tblReductions.ReductionID, tblEmployee.EmployerID
FROM (tblEmployee INNER JOIN QryEmployeeAge ON tblEmployee.EmployeeID =
QryEmployeeAge.EmployeeID) INNER JOIN tblReductions ON tblEmployee.LifeID =
tblReductions.PlansID
ORDER BY QryEmployeeAge.Age;

If you take out the order by statement, the datasheet works fine.

 

[bhipwell via AccessMonster.com]

SQL for age:

SELECT tblEmployee.EmployeeID, tblEmployee.EmployeeBirthday, tblEmployer.
EmployerID, tblEmployer.EmployerEffectiveDate, DateDiff("yyyy",
[EmployeeBirthday],Date())+(Format([EmployeeBirthday],"mmdd")>Format(Date(),
"mmdd")) AS CalcAge, DateDiff("yyyy",[EmployeeBirthday],Date())+(Format(
[EmployeeBirthday],"mmdd")>Format(Date(),"mmdd")) AS ActualAge, Int(Format(
[EmployerEffectiveDate],"yyyy.mmdd")-Format([EmployeeBirthday],"yyyy.mmdd"))
AS Age, [ActualAge]+IIf((Format([EmployeeBirthday],"mmdd"))<(Format(
[EmployerEffectiveDate],"mmdd")) And (Format([EmployeeBirthday],"mmdd"))
<Format(Date(),"mmdd"),-1,0) AS OldAge, tblEmployee.EmployeeLastName
FROM tblEmployer INNER JOIN tblEmployee ON tblEmployer.EmployerID =
tblEmployee.EmployerID;

SQL where I get data mismatch when ascending age:

SELECT tblEmployee.EmployeeLastName, tblReductions.ReductionAmount AS LIFE,
tblReductions.PlansID, tblReductions.ReductionAmount, QryEmployeeAge.Age,
tblReductions.ReductionID, tblEmployee.EmployerID
FROM (tblEmployee INNER JOIN QryEmployeeAge ON tblEmployee.EmployeeID =
QryEmployeeAge.EmployeeID) INNER JOIN tblReductions ON tblEmployee.LifeID =
tblReductions.PlansID
ORDER BY QryEmployeeAge.Age;

If you take out the order by statement, the datasheet works fine.

 

[bhipwell via AccessMonster.com]

SQL for age:

SELECT tblEmployee.EmployeeID, tblEmployee.EmployeeBirthday, tblEmployer.
EmployerID, tblEmployer.EmployerEffectiveDate, DateDiff("yyyy",
[EmployeeBirthday],Date())+(Format([EmployeeBirthday],"mmdd")>Format(Date(),
"mmdd")) AS CalcAge, DateDiff("yyyy",[EmployeeBirthday],Date())+(Format(
[EmployeeBirthday],"mmdd")>Format(Date(),"mmdd")) AS ActualAge, Int(Format(
[EmployerEffectiveDate],"yyyy.mmdd")-Format([EmployeeBirthday],"yyyy.mmdd"))
AS Age, [ActualAge]+IIf((Format([EmployeeBirthday],"mmdd"))<(Format(
[EmployerEffectiveDate],"mmdd")) And (Format([EmployeeBirthday],"mmdd"))
<Format(Date(),"mmdd"),-1,0) AS OldAge, tblEmployee.EmployeeLastName
FROM tblEmployer INNER JOIN tblEmployee ON tblEmployer.EmployerID =
tblEmployee.EmployerID;

SQL where I get data mismatch when ascending age:

SELECT tblEmployee.EmployeeLastName, tblReductions.ReductionAmount AS LIFE,
tblReductions.PlansID, tblReductions.ReductionAmount, QryEmployeeAge.Age,
tblReductions.ReductionID, tblEmployee.EmployerID
FROM (tblEmployee INNER JOIN QryEmployeeAge ON tblEmployee.EmployeeID =
QryEmployeeAge.EmployeeID) INNER JOIN tblReductions ON tblEmployee.LifeID =
tblReductions.PlansID
ORDER BY QryEmployeeAge.Age;

If you take out the order by statement, the datasheet works fine.

 

[bhipwell via AccessMonster.com]

SQL for age:

SELECT tblEmployee.EmployeeID, tblEmployee.EmployeeBirthday, tblEmployer.
EmployerID, tblEmployer.EmployerEffectiveDate, DateDiff("yyyy",
[EmployeeBirthday],Date())+(Format([EmployeeBirthday],"mmdd")>Format(Date(),
"mmdd")) AS CalcAge, DateDiff("yyyy",[EmployeeBirthday],Date())+(Format(
[EmployeeBirthday],"mmdd")>Format(Date(),"mmdd")) AS ActualAge, Int(Format(
[EmployerEffectiveDate],"yyyy.mmdd")-Format([EmployeeBirthday],"yyyy.mmdd"))
AS Age, [ActualAge]+IIf((Format([EmployeeBirthday],"mmdd"))<(Format(
[EmployerEffectiveDate],"mmdd")) And (Format([EmployeeBirthday],"mmdd"))
<Format(Date(),"mmdd"),-1,0) AS OldAge, tblEmployee.EmployeeLastName
FROM tblEmployer INNER JOIN tblEmployee ON tblEmployer.EmployerID =
tblEmployee.EmployerID;

SQL where I get data mismatch when ascending age:

SELECT tblEmployee.EmployeeLastName, tblReductions.ReductionAmount AS LIFE,
tblReductions.PlansID, tblReductions.ReductionAmount, QryEmployeeAge.Age,
tblReductions.ReductionID, tblEmployee.EmployerID
FROM (tblEmployee INNER JOIN QryEmployeeAge ON tblEmployee.EmployeeID =
QryEmployeeAge.EmployeeID) INNER JOIN tblReductions ON tblEmployee.LifeID =
tblReductions.PlansID
ORDER BY QryEmployeeAge.Age;

If you take out the order by statement, the datasheet works fine.

 

[Duane Hookom]

This makes no sense because you are attempting to subtract on string from
another:
Int(Format([EmployerEffectiveDate],"yyyy.mmdd") -
Format([EmployeeBirthday],"yyyy.mmdd")) AS Age

What kind of an "Age" are you attempting to calculate? Also, I mentioned
something about Null date of birth. You didn't provide any feedback whether
you checked this out and if you found any nulls.

 

[bhipwell via AccessMonster.com]

The AGE calculation basically calculates what someones age was at specific
period in time. You could replace EmployerEffectiveDate with Date() and we
would get workable results.

I haven't checked birthday nulls, but when I run the query without the
ascending, every records gets their Age calculated.

BH

 

[John Spencer]

Almost every time I've seen this kind of problem, one of the
calculations is returning #error because of a data problem. Nothing bad
happens UNTIL you sort or filter by the involved field.

Int(Format([EmployerEffectiveDate],"yyyy.mmdd")-
Format([EmployeeBirthday],"yyyy.mmdd"))

If either EmployerEffectiveDate or EmployeeBirthday is null, the above
will generate an error. If the two fields are NOT date fields then you
could also get an error if the text cannot properly be interpreted as a
date.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[bhipwell via AccessMonster.com]

John,

Thanks for the reply. Looked through the records and sure enough, I was
missing a birthday and the query now works. Would it be as simple as using
an "If Null statement" to work around any null fields I may have?

BH

 

[John Spencer MVP]

IIF(EmployerEffectiveDate is Null or EmployeeBirthday is
Null,Null,Int(Format([EmployerEffectiveDate],"yyyy.mmdd")-
Format([EmployeeBirthday],"yyyy.mmdd")))

Or
IIF(IsDate(EmployerEffectiveDate) And IsDate(EmployeeBirthday),
Int(Format([EmployerEffectiveDate],"yyyy.mmdd")-Format([EmployeeBirthday],"yyyy.mmdd")),
Null)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County