Date Difference limited to 100 years

[Richard S.]

I have a query to extract all persons with an age eaual to or greater than a
user-entered paramater (criteria). The expression
(Year(Date())-Year([Membership].[BirthDt])) when compared to the criterion
does not extract any age 100 or older. In addition, it extracted a person
with an erroneous future birth date of 2968. I had the same erroneous
results using DateDiff().

I will appreciate any help to correct the query to extract even those with
ages 100 or greater and to exclude any future dates.

 

[Jerry Whittle]

I have no trouble getting it to work with Access 2007 and dates over 100 years.

Please provide the entire SQL statement. Open the query in design view. Next
go to View, SQL View and copy and past it here.

If the SQL statement doesn't start with the word PARAMETER, I bet that is
the problem. You need to define a date parameter as Date/Time or things can
get strange.

 

[Richard S.]

Here is the entire SQL statement:
SELECT Membership.BirthDt, IIf([BirthDtCd]>1,Format([BirthDt],"mmmm"),"") AS
Mo, IIf([BirthDtCd]=3,DatePart("d",[BirthDt]),"") AS Dt,
DatePart("yyyy",[BirthDt]) AS Yr, IIf([BirthDtCd]=1,0,1) AS SortCd,
Membership.BirthDtCd, Membership.ChapID, Chapters.ChapName,
Chapters.ChapCity, Chapters.ChapDistrict, Membership.MbrID, Membership.Title,
Membership.LN, Membership.FN, Membership.MaidenName, Membership.Addr1,
Membership.Addr2, Membership.City, Membership.State, Membership.Zip,
Membership.MbrStatus, Membership.HusbName,
(Year(Date())-Year(Membership.BirthDt)) AS YrLapse,
(Year(Date())-Year(Membership.BirthDt)) AS Expr1
FROM Chapters INNER JOIN Membership ON Chapters.ChapID = Membership.ChapID
WHERE (((Membership.BirthDt) Is Not Null And (Membership.BirthDt) Not Like
"") AND ((Membership.MbrStatus) Like "A") AND
(((Year(Date())-Year([Membership].[BirthDt])))>=[Minimum Age Parameter]))
ORDER BY Membership.LN, Membership.FN;

I have no trouble getting it to work with Access 2007 and dates over 100 years.

Please provide the entire SQL statement. Open the query in design view. Next
go to View, SQL View and copy and past it here.

If the SQL statement doesn't start with the word PARAMETER, I bet that is
the problem. You need to define a date parameter as Date/Time or things can
get strange.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query to extract all persons with an age eaual to or greater than a
user-entered paramater (criteria). The expression
(Year(Date())-Year([Membership].[BirthDt])) when compared to the criterion
does not extract any age 100 or older. In addition, it extracted a person
with an erroneous future birth date of 2968. I had the same erroneous
results using DateDiff().

I will appreciate any help to correct the query to extract even those with
ages 100 or greater and to exclude any future dates.

 

[Richard S.]

Incidentally, I am using Access 2003, in case that might be a factor.

Here is the entire SQL statement:
SELECT Membership.BirthDt, IIf([BirthDtCd]>1,Format([BirthDt],"mmmm"),"") AS
Mo, IIf([BirthDtCd]=3,DatePart("d",[BirthDt]),"") AS Dt,
DatePart("yyyy",[BirthDt]) AS Yr, IIf([BirthDtCd]=1,0,1) AS SortCd,
Membership.BirthDtCd, Membership.ChapID, Chapters.ChapName,
Chapters.ChapCity, Chapters.ChapDistrict, Membership.MbrID, Membership.Title,
Membership.LN, Membership.FN, Membership.MaidenName, Membership.Addr1,
Membership.Addr2, Membership.City, Membership.State, Membership.Zip,
Membership.MbrStatus, Membership.HusbName,
(Year(Date())-Year(Membership.BirthDt)) AS YrLapse,
(Year(Date())-Year(Membership.BirthDt)) AS Expr1
FROM Chapters INNER JOIN Membership ON Chapters.ChapID = Membership.ChapID
WHERE (((Membership.BirthDt) Is Not Null And (Membership.BirthDt) Not Like
"") AND ((Membership.MbrStatus) Like "A") AND
(((Year(Date())-Year([Membership].[BirthDt])))>=[Minimum Age Parameter]))
ORDER BY Membership.LN, Membership.FN;

I have no trouble getting it to work with Access 2007 and dates over 100 years.

Please provide the entire SQL statement. Open the query in design view. Next
go to View, SQL View and copy and past it here.

If the SQL statement doesn't start with the word PARAMETER, I bet that is
the problem. You need to define a date parameter as Date/Time or things can
get strange.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query to extract all persons with an age eaual to or greater than a
user-entered paramater (criteria). The expression
(Year(Date())-Year([Membership].[BirthDt])) when compared to the criterion
does not extract any age 100 or older. In addition, it extracted a person
with an erroneous future birth date of 2968. I had the same erroneous
results using DateDiff().

I will appreciate any help to correct the query to extract even those with
ages 100 or greater and to exclude any future dates.

 

[Jerry Whittle]

I'm going to assume that the BirthDt field is a Date/Time data type in the
Membership table. Actually I'm worried that the BirthDt field is text.
Otherwise why would you be checking for empty strings. If it is a text data
type, you are going to need the CDate function to convert the text to a date
or maybe the Right function to extract just the last 4 characters as the year.

For the birth year of 2968, did you check the record to make sure that it
isn't a case of dirty data?

Speaking of dirty data, is the BirthDt field displayed as a 4 digit year? If
only 2 digits, you just might not have in the years that you think. Access
makes some assumptions if you just type in 1/1/25. It's going to store 2025
as the year.

The first line of the SQL statement should be this otherwise it's possible
that Access can get confused by the data type. For example it might think it
is text instead of a long integer number:
PARAMETERS [Minimum Age Parameter] Long;

Often the Date() function can be the problem; however, I would have thougth
that it would have raised an error. Do a Crtl + g to bring up the VBA
window. In the Immediate window put the following and hit enter:

Debug.Print Date()

If an error occurs there is probably a problem with References (or you typed
it in wrong). If it returns a date, is it a correct date? I've seen computers
off decades before.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here is the entire SQL statement:
SELECT Membership.BirthDt, IIf([BirthDtCd]>1,Format([BirthDt],"mmmm"),"") AS
Mo, IIf([BirthDtCd]=3,DatePart("d",[BirthDt]),"") AS Dt,
DatePart("yyyy",[BirthDt]) AS Yr, IIf([BirthDtCd]=1,0,1) AS SortCd,
Membership.BirthDtCd, Membership.ChapID, Chapters.ChapName,
Chapters.ChapCity, Chapters.ChapDistrict, Membership.MbrID, Membership.Title,
Membership.LN, Membership.FN, Membership.MaidenName, Membership.Addr1,
Membership.Addr2, Membership.City, Membership.State, Membership.Zip,
Membership.MbrStatus, Membership.HusbName,
(Year(Date())-Year(Membership.BirthDt)) AS YrLapse,
(Year(Date())-Year(Membership.BirthDt)) AS Expr1
FROM Chapters INNER JOIN Membership ON Chapters.ChapID = Membership.ChapID
WHERE (((Membership.BirthDt) Is Not Null And (Membership.BirthDt) Not Like
"") AND ((Membership.MbrStatus) Like "A") AND
(((Year(Date())-Year([Membership].[BirthDt])))>=[Minimum Age Parameter]))
ORDER BY Membership.LN, Membership.FN;

I have no trouble getting it to work with Access 2007 and dates over 100 years.

Please provide the entire SQL statement. Open the query in design view. Next
go to View, SQL View and copy and past it here.

If the SQL statement doesn't start with the word PARAMETER, I bet that is
the problem. You need to define a date parameter as Date/Time or things can
get strange.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query to extract all persons with an age eaual to or greater than a
user-entered paramater (criteria). The expression
(Year(Date())-Year([Membership].[BirthDt])) when compared to the criterion
does not extract any age 100 or older. In addition, it extracted a person
with an erroneous future birth date of 2968. I had the same erroneous
results using DateDiff().

I will appreciate any help to correct the query to extract even those with
ages 100 or greater and to exclude any future dates.

 

[vanderghast]

The dates may have been entered (and formatted out) with two digits for the
year,


? (Year(Date())-Year(#1/1/1908#))
101

? (Year(Date())-Year(#1/1/08#))
1



The criteria constant may be taken as a litteral:


? (Year(Date())-Year(#1/1/1766#))
243

? (Year(Date())-Year(#1/1/1766#)) > "53"
False

? (Year(Date())-Year(#1/1/1766#)) > 53
True



Or, as usual, something else... :-(



Vanderghast, Access MVP

 

[Richard S.]

In response to Jerry's questions: the BirthDt field is a Date/Time data
type; the check for empty string is actually superfluous - to be deleted;
BirthDt displays a 4-digit year; the "2968" was an erroneous entry, obviously
intended to be "1968".

Inserting PARAMETERS [Minimum Age Parameter] Long; resolved the problem,
and all the dates meeting the parameter criterion are being selected.

Thank you for your help, and particularly for your prompt response. I'll
now have to check any other queries with parameters to ensure I don't have
others that are faulty.

I'm going to assume that the BirthDt field is a Date/Time data type in the
Membership table. Actually I'm worried that the BirthDt field is text.
Otherwise why would you be checking for empty strings. If it is a text data
type, you are going to need the CDate function to convert the text to a date
or maybe the Right function to extract just the last 4 characters as the year.

For the birth year of 2968, did you check the record to make sure that it
isn't a case of dirty data?

Speaking of dirty data, is the BirthDt field displayed as a 4 digit year? If
only 2 digits, you just might not have in the years that you think. Access
makes some assumptions if you just type in 1/1/25. It's going to store 2025
as the year.

The first line of the SQL statement should be this otherwise it's possible
that Access can get confused by the data type. For example it might think it
is text instead of a long integer number:
PARAMETERS [Minimum Age Parameter] Long;

Often the Date() function can be the problem; however, I would have thougth
that it would have raised an error. Do a Crtl + g to bring up the VBA
window. In the Immediate window put the following and hit enter:

Debug.Print Date()

If an error occurs there is probably a problem with References (or you typed
it in wrong). If it returns a date, is it a correct date? I've seen computers
off decades before.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here is the entire SQL statement:
SELECT Membership.BirthDt, IIf([BirthDtCd]>1,Format([BirthDt],"mmmm"),"") AS
Mo, IIf([BirthDtCd]=3,DatePart("d",[BirthDt]),"") AS Dt,
DatePart("yyyy",[BirthDt]) AS Yr, IIf([BirthDtCd]=1,0,1) AS SortCd,
Membership.BirthDtCd, Membership.ChapID, Chapters.ChapName,
Chapters.ChapCity, Chapters.ChapDistrict, Membership.MbrID, Membership.Title,
Membership.LN, Membership.FN, Membership.MaidenName, Membership.Addr1,
Membership.Addr2, Membership.City, Membership.State, Membership.Zip,
Membership.MbrStatus, Membership.HusbName,
(Year(Date())-Year(Membership.BirthDt)) AS YrLapse,
(Year(Date())-Year(Membership.BirthDt)) AS Expr1
FROM Chapters INNER JOIN Membership ON Chapters.ChapID = Membership.ChapID
WHERE (((Membership.BirthDt) Is Not Null And (Membership.BirthDt) Not Like
"") AND ((Membership.MbrStatus) Like "A") AND
(((Year(Date())-Year([Membership].[BirthDt])))>=[Minimum Age Parameter]))
ORDER BY Membership.LN, Membership.FN;

I have no trouble getting it to work with Access 2007 and dates over 100 years.

Please provide the entire SQL statement. Open the query in design view. Next
go to View, SQL View and copy and past it here.

If the SQL statement doesn't start with the word PARAMETER, I bet that is
the problem. You need to define a date parameter as Date/Time or things can
get strange.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have a query to extract all persons with an age eaual to or greater than a
user-entered paramater (criteria). The expression
(Year(Date())-Year([Membership].[BirthDt])) when compared to the criterion
does not extract any age 100 or older. In addition, it extracted a person
with an erroneous future birth date of 2968. I had the same erroneous
results using DateDiff().

I will appreciate any help to correct the query to extract even those with
ages 100 or greater and to exclude any future dates.