If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Difference limited to 100 years
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. |
#2
|
|||
|
|||
Date Difference limited to 100 years
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. "Richard S." wrote: 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. |
#3
|
|||
|
|||
Date Difference limited to 100 years
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; "Jerry Whittle" wrote: 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. "Richard S." wrote: 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. |
#4
|
|||
|
|||
Date Difference limited to 100 years
Incidentally, I am using Access 2003, in case that might be a factor.
"Richard S." wrote: 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; "Jerry Whittle" wrote: 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. "Richard S." wrote: 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. |
#5
|
|||
|
|||
Date Difference limited to 100 years
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. And (Membership.BirthDt) Not Like "" 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. "Richard S." wrote: 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; "Jerry Whittle" wrote: 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. "Richard S." wrote: 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. |
#6
|
|||
|
|||
Date Difference limited to 100 years
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." wrote in message ... 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. |
#7
|
|||
|
|||
Date Difference limited to 100 years
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. "Jerry Whittle" wrote: 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. And (Membership.BirthDt) Not Like "" 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. "Richard S." wrote: 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; "Jerry Whittle" wrote: 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. "Richard S." wrote: 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. |
Thread Tools | |
Display Modes | |
|
|