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
|
|||
|
|||
Having trouble with Date calculation Query logic
Hi,
I am trying to create a query which performs some date calculations and pulls records based on the calculation. I think it will be simpler to explain with an example; Data: Task Date # of Days Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 If today is December 24, 2008, Query I am trying to write will display the fir three records from this data. Essentially calculate the date/month to see if the date/month are within the (# of Days) period from today. You can say it is similar to figuring our if your birthday is coming up with x number of days. If the date is in the past, it will act as it telling you if your birthday is coming up within x number of days. If the date is in future, it will act as if it is a reminder for that date if that date is within x number of days. thanks. Thank you. |
#2
|
|||
|
|||
Having trouble with Date calculation Query logic
Hi Dragon,
SELECT * FROM Tablename WHERE DateField = (Date() + NumDays) I used NumDays instead of [# of Days] since it is not a good idea to use spaces or special characters (except underscore) in fieldnames btw, do not use DATE as a fieldname or controlname, it is a reserved word Problem names and reserved words in Access, by Allen Browne http://www.allenbrowne.com/Ap****ueBadWord.html Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Dragon wrote: Hi, I am trying to create a query which performs some date calculations and pulls records based on the calculation. I think it will be simpler to explain with an example; Data: Task Date # of Days Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 If today is December 24, 2008, Query I am trying to write will display the fir three records from this data. Essentially calculate the date/month to see if the date/month are within the (# of Days) period from today. You can say it is similar to figuring our if your birthday is coming up with x number of days. If the date is in the past, it will act as it telling you if your birthday is coming up within x number of days. If the date is in future, it will act as if it is a reminder for that date if that date is within x number of days. thanks. Thank you. |
#3
|
|||
|
|||
Having trouble with Date calculation Query logic
Thank you Crystal for your reply. My sample didn't have the actual field
names. I used these words (Date, # of Days etc) just to make it easier to explain :-) Having said that I do not believe your solution will work. It will pickup all date prior to (Date() + NumDays) while I only want date where month/day combination is within range. To explain this a bit mo Today's Date: Dec 24, 2008 # of Days (NoD): 16 Date1 : December 26, 2005 Date 2: April 05, 2008 Date 3: Jan 03, 2009. Query should pickup only Date 1 and Date 3 and not Date 2 because month/date combination is not within the specified range (date() + NoD). Thanks. "strive4peace" wrote in message ... Hi Dragon, SELECT * FROM Tablename WHERE DateField = (Date() + NumDays) I used NumDays instead of [# of Days] since it is not a good idea to use spaces or special characters (except underscore) in fieldnames btw, do not use DATE as a fieldname or controlname, it is a reserved word Problem names and reserved words in Access, by Allen Browne http://www.allenbrowne.com/Ap****ueBadWord.html Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Dragon wrote: Hi, I am trying to create a query which performs some date calculations and pulls records based on the calculation. I think it will be simpler to explain with an example; Data: Task Date # of Days Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 If today is December 24, 2008, Query I am trying to write will display the fir three records from this data. Essentially calculate the date/month to see if the date/month are within the (# of Days) period from today. You can say it is similar to figuring our if your birthday is coming up with x number of days. If the date is in the past, it will act as it telling you if your birthday is coming up within x number of days. If the date is in future, it will act as if it is a reminder for that date if that date is within x number of days. thanks. Thank you. |
#4
|
|||
|
|||
Having trouble with Date calculation Query logic
Hi Dragon,
I did not really follow what you wanted -- just looked at the first 3 records, which you said you wanted and ignored what you wrote because it seemed to contradict that so you want everything between Today and whatever day is will be when the NumDays comes around? like this: WHERE DateField BETWEEN Date() and (Date() + NumDays) ?? Your test date is Dec 24, 2008 -- why would that pick up December 26, 2005 (FIVE)? -- or is that a typo? that is what was confusing me... Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Dragon wrote: Thank you Crystal for your reply. My sample didn't have the actual field names. I used these words (Date, # of Days etc) just to make it easier to explain :-) Having said that I do not believe your solution will work. It will pickup all date prior to (Date() + NumDays) while I only want date where month/day combination is within range. To explain this a bit mo Today's Date: Dec 24, 2008 # of Days (NoD): 16 Date1 : December 26, 2005 Date 2: April 05, 2008 Date 3: Jan 03, 2009. Query should pickup only Date 1 and Date 3 and not Date 2 because month/date combination is not within the specified range (date() + NoD). Thanks. "strive4peace" wrote in message ... Hi Dragon, SELECT * FROM Tablename WHERE DateField = (Date() + NumDays) I used NumDays instead of [# of Days] since it is not a good idea to use spaces or special characters (except underscore) in fieldnames btw, do not use DATE as a fieldname or controlname, it is a reserved word Problem names and reserved words in Access, by Allen Browne http://www.allenbrowne.com/Ap****ueBadWord.html Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Dragon wrote: Hi, I am trying to create a query which performs some date calculations and pulls records based on the calculation. I think it will be simpler to explain with an example; Data: Task Date # of Days Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 If today is December 24, 2008, Query I am trying to write will display the fir three records from this data. Essentially calculate the date/month to see if the date/month are within the (# of Days) period from today. You can say it is similar to figuring our if your birthday is coming up with x number of days. If the date is in the past, it will act as it telling you if your birthday is coming up within x number of days. If the date is in future, it will act as if it is a reminder for that date if that date is within x number of days. thanks. Thank you. |
#5
|
|||
|
|||
Having trouble with Date calculation Query logic
I am sorry if I am not making much sense here. I will try again.
If the date field has a date in the past, we only want to compare the month and date, not the year. If month and date combination is with this range (date() + NoD) then it should be picked up regardless of the year. In my example December 26, 2005 will be picked up because date is in the past (like a birthdate) and day/month combination (December 26) is within the range (Today: December 24 + NoD=16) If the date field has a date in the future, then you also consider the year to see if that date falls within range. In this case something like this would work: If date = (Date() + NoD) and date = Date() Then True I hope it makes sense now. Thanks. "strive4peace" wrote in message ... Hi Dragon, I did not really follow what you wanted -- just looked at the first 3 records, which you said you wanted and ignored what you wrote because it seemed to contradict that so you want everything between Today and whatever day is will be when the NumDays comes around? like this: WHERE DateField BETWEEN Date() and (Date() + NumDays) ?? Your test date is Dec 24, 2008 -- why would that pick up December 26, 2005 (FIVE)? -- or is that a typo? that is what was confusing me... Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Dragon wrote: Thank you Crystal for your reply. My sample didn't have the actual field names. I used these words (Date, # of Days etc) just to make it easier to explain :-) Having said that I do not believe your solution will work. It will pickup all date prior to (Date() + NumDays) while I only want date where month/day combination is within range. To explain this a bit mo Today's Date: Dec 24, 2008 # of Days (NoD): 16 Date1 : December 26, 2005 Date 2: April 05, 2008 Date 3: Jan 03, 2009. Query should pickup only Date 1 and Date 3 and not Date 2 because month/date combination is not within the specified range (date() + NoD). Thanks. "strive4peace" wrote in message ... Hi Dragon, SELECT * FROM Tablename WHERE DateField = (Date() + NumDays) I used NumDays instead of [# of Days] since it is not a good idea to use spaces or special characters (except underscore) in fieldnames btw, do not use DATE as a fieldname or controlname, it is a reserved word Problem names and reserved words in Access, by Allen Browne http://www.allenbrowne.com/Ap****ueBadWord.html Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Dragon wrote: Hi, I am trying to create a query which performs some date calculations and pulls records based on the calculation. I think it will be simpler to explain with an example; Data: Task Date # of Days Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 If today is December 24, 2008, Query I am trying to write will display the fir three records from this data. Essentially calculate the date/month to see if the date/month are within the (# of Days) period from today. You can say it is similar to figuring our if your birthday is coming up with x number of days. If the date is in the past, it will act as it telling you if your birthday is coming up within x number of days. If the date is in future, it will act as if it is a reminder for that date if that date is within x number of days. thanks. Thank you. |
#6
|
|||
|
|||
Having trouble with Date calculation Query logic
Let me rephrase the problem to confirm my understanding.
Given Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 Then Return record for Task 1 If today is between Jan 1 and Jan 17 Task 2 If today is between Dec 25 and Dec 31 or between Jan 1 and Jan 8 Task 3 If today is between Jan 5 and Jan 25 Task 4 if today is between Jan 25 and Jan 30 Task 5 if today is between Apr 9 and Apr 24 I think the following may work for you. SELECT * FROM Table WHERE Date() Between DateSerial(Year(Date()),Month(DateField),Day(DateF ield)) and DateSerial(Year(Date()),Month(DateField),Day(DateF ield)+ NumDays) OR Date() Between DateSerial(Year(Date())+1,Month(DateField),Day(Dat eField)) and DateSerial(Year(Date())+1,Month(DateField),Day(Dat eField)+ NumDays) The second criteria is to handle dates at the end of the year (such as Dec 25) and could also be written as DateAdd("yyyy",1,Date()) Between DateSerial(Year(Date()),Month(DateField),Day(DateF ield)) and DateSerial(Year(Date()),Month(DateField),Day(DateF ield)+ NumDays) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Dragon wrote: I am sorry if I am not making much sense here. I will try again. If the date field has a date in the past, we only want to compare the month and date, not the year. If month and date combination is with this range (date() + NoD) then it should be picked up regardless of the year. In my example December 26, 2005 will be picked up because date is in the past (like a birthdate) and day/month combination (December 26) is within the range (Today: December 24 + NoD=16) If the date field has a date in the future, then you also consider the year to see if that date falls within range. In this case something like this would work: If date = (Date() + NoD) and date = Date() Then True I hope it makes sense now. Thanks. "strive4peace" wrote in message ... Hi Dragon, I did not really follow what you wanted -- just looked at the first 3 records, which you said you wanted and ignored what you wrote because it seemed to contradict that so you want everything between Today and whatever day is will be when the NumDays comes around? like this: WHERE DateField BETWEEN Date() and (Date() + NumDays) ?? Your test date is Dec 24, 2008 -- why would that pick up December 26, 2005 (FIVE)? -- or is that a typo? that is what was confusing me... Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Dragon wrote: Thank you Crystal for your reply. My sample didn't have the actual field names. I used these words (Date, # of Days etc) just to make it easier to explain :-) Having said that I do not believe your solution will work. It will pickup all date prior to (Date() + NumDays) while I only want date where month/day combination is within range. To explain this a bit mo Today's Date: Dec 24, 2008 # of Days (NoD): 16 Date1 : December 26, 2005 Date 2: April 05, 2008 Date 3: Jan 03, 2009. Query should pickup only Date 1 and Date 3 and not Date 2 because month/date combination is not within the specified range (date() + NoD). Thanks. "strive4peace" wrote in message ... Hi Dragon, SELECT * FROM Tablename WHERE DateField = (Date() + NumDays) I used NumDays instead of [# of Days] since it is not a good idea to use spaces or special characters (except underscore) in fieldnames btw, do not use DATE as a fieldname or controlname, it is a reserved word Problem names and reserved words in Access, by Allen Browne http://www.allenbrowne.com/Ap****ueBadWord.html Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Dragon wrote: Hi, I am trying to create a query which performs some date calculations and pulls records based on the calculation. I think it will be simpler to explain with an example; Data: Task Date # of Days Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 If today is December 24, 2008, Query I am trying to write will display the fir three records from this data. Essentially calculate the date/month to see if the date/month are within the (# of Days) period from today. You can say it is similar to figuring our if your birthday is coming up with x number of days. If the date is in the past, it will act as it telling you if your birthday is coming up within x number of days. If the date is in future, it will act as if it is a reminder for that date if that date is within x number of days. thanks. Thank you. |
#7
|
|||
|
|||
Having trouble with Date calculation Query logic
John,
We could be talking about the same thing but from different angle but I am a bit confused so I will use your approach to clarify things. Given Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 And Today being December 24, 2008 Then Return record for Task 1 If Date [Jan 01] is between Today (Dec 24, 2008) and 16 Days from Today (Jan 09, 2009) = True in this case Task 2 If Date [Dec 25] is between Today (Dec 24, 2008) and 14 Days from Today (Jan 7, 2009) = True in this case Task 3 If Date [Jan 05, 2009] is between Today (Dec 24, 2008) and 20 Days from Today (Jan 13, 2009) = True in this case Task 4 If Date [Jan 25, 2010] is between Today (Dec 24, 2008) and 5 Days from Today (Dec 29, 2008) = False in this case Task 5 If Date [Apr 09] is between Today (Dec 24, 2008) and 15 Days from Today (Jan 08, 2009) = False in this case The Login I can come up with is: If MyDate Date () Then MyMonth = Month (MyDate) MyDay = Day(MyDate) If (MyMonthMyDay) = MonthDay(Date()) and (MyMonthMyDay) = MonthDay(Date()) + NoD Then Select Record = True Else If MyDate Date () + NoD then Select Record = True Else Select record = False If it still doesn't make sense and I am confusing everyone, then I think I am simply not capable of explaining this right :-) One way to think if this problem is that it has two parts. - Find out if a birthday is coming up within a given period. Typically you will assume the given period to be fixed, say 14 days, but in this case each birthday has a variable period.And since birthday dates are in the past, you have to ignore the year when calculating. - Find out if there is an upcoming event coming up within a given period for each event. Since events are in the future, you have to make sure look at the year as well and not display event for someting that isn't due for a few years. "John Spencer (MVP)" wrote in message ... Let me rephrase the problem to confirm my understanding. Given Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 Then Return record for Task 1 If today is between Jan 1 and Jan 17 Task 2 If today is between Dec 25 and Dec 31 or between Jan 1 and Jan 8 Task 3 If today is between Jan 5 and Jan 25 Task 4 if today is between Jan 25 and Jan 30 Task 5 if today is between Apr 9 and Apr 24 I think the following may work for you. SELECT * FROM Table WHERE Date() Between DateSerial(Year(Date()),Month(DateField),Day(DateF ield)) and DateSerial(Year(Date()),Month(DateField),Day(DateF ield)+ NumDays) OR Date() Between DateSerial(Year(Date())+1,Month(DateField),Day(Dat eField)) and DateSerial(Year(Date())+1,Month(DateField),Day(Dat eField)+ NumDays) The second criteria is to handle dates at the end of the year (such as Dec 25) and could also be written as DateAdd("yyyy",1,Date()) Between DateSerial(Year(Date()),Month(DateField),Day(DateF ield)) and DateSerial(Year(Date()),Month(DateField),Day(DateF ield)+ NumDays) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Dragon wrote: I am sorry if I am not making much sense here. I will try again. If the date field has a date in the past, we only want to compare the month and date, not the year. If month and date combination is with this range (date() + NoD) then it should be picked up regardless of the year. In my example December 26, 2005 will be picked up because date is in the past (like a birthdate) and day/month combination (December 26) is within the range (Today: December 24 + NoD=16) If the date field has a date in the future, then you also consider the year to see if that date falls within range. In this case something like this would work: If date = (Date() + NoD) and date = Date() Then True I hope it makes sense now. Thanks. "strive4peace" wrote in message ... Hi Dragon, I did not really follow what you wanted -- just looked at the first 3 records, which you said you wanted and ignored what you wrote because it seemed to contradict that so you want everything between Today and whatever day is will be when the NumDays comes around? like this: WHERE DateField BETWEEN Date() and (Date() + NumDays) ?? Your test date is Dec 24, 2008 -- why would that pick up December 26, 2005 (FIVE)? -- or is that a typo? that is what was confusing me... Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Dragon wrote: Thank you Crystal for your reply. My sample didn't have the actual field names. I used these words (Date, # of Days etc) just to make it easier to explain :-) Having said that I do not believe your solution will work. It will pickup all date prior to (Date() + NumDays) while I only want date where month/day combination is within range. To explain this a bit mo Today's Date: Dec 24, 2008 # of Days (NoD): 16 Date1 : December 26, 2005 Date 2: April 05, 2008 Date 3: Jan 03, 2009. Query should pickup only Date 1 and Date 3 and not Date 2 because month/date combination is not within the specified range (date() + NoD). Thanks. "strive4peace" wrote in message ... Hi Dragon, SELECT * FROM Tablename WHERE DateField = (Date() + NumDays) I used NumDays instead of [# of Days] since it is not a good idea to use spaces or special characters (except underscore) in fieldnames btw, do not use DATE as a fieldname or controlname, it is a reserved word Problem names and reserved words in Access, by Allen Browne http://www.allenbrowne.com/Ap****ueBadWord.html Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Dragon wrote: Hi, I am trying to create a query which performs some date calculations and pulls records based on the calculation. I think it will be simpler to explain with an example; Data: Task Date # of Days Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 If today is December 24, 2008, Query I am trying to write will display the fir three records from this data. Essentially calculate the date/month to see if the date/month are within the (# of Days) period from today. You can say it is similar to figuring our if your birthday is coming up with x number of days. If the date is in the past, it will act as it telling you if your birthday is coming up within x number of days. If the date is in future, it will act as if it is a reminder for that date if that date is within x number of days. thanks. Thank you. |
#8
|
|||
|
|||
Having trouble with Date calculation Query logic
John:
Shouldn't it be: SELECT * FROM Table WHERE DATE() BETWEEN DATESERIAL(YEAR(DATE()),MONTH(DateField),DAY(DateF ield)) AND DateSerial(YEAR(DATE()),MONTH(DateField),DAY(DateF ield)+ NumDays) OR DATE() BETWEEN DATESERIAL(YEAR(DATE())-1,MONTH(DateField),DAY(DateField)) AND DATESERIAL(YEAR(DATE())-1,MONTH(DateField),DAY(DateField)+ NumDays) For end-of year DateField values, if the current date falls within the range but in the new year, then I think you'd have to start the range from last year, not next year. Ken Sheridan Stafford, England "John Spencer (MVP)" wrote: Let me rephrase the problem to confirm my understanding. Given Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 Then Return record for Task 1 If today is between Jan 1 and Jan 17 Task 2 If today is between Dec 25 and Dec 31 or between Jan 1 and Jan 8 Task 3 If today is between Jan 5 and Jan 25 Task 4 if today is between Jan 25 and Jan 30 Task 5 if today is between Apr 9 and Apr 24 I think the following may work for you. SELECT * FROM Table WHERE Date() Between DateSerial(Year(Date()),Month(DateField),Day(DateF ield)) and DateSerial(Year(Date()),Month(DateField),Day(DateF ield)+ NumDays) OR Date() Between DateSerial(Year(Date())+1,Month(DateField),Day(Dat eField)) and DateSerial(Year(Date())+1,Month(DateField),Day(Dat eField)+ NumDays) The second criteria is to handle dates at the end of the year (such as Dec 25) and could also be written as DateAdd("yyyy",1,Date()) Between DateSerial(Year(Date()),Month(DateField),Day(DateF ield)) and DateSerial(Year(Date()),Month(DateField),Day(DateF ield)+ NumDays) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Dragon wrote: I am sorry if I am not making much sense here. I will try again. If the date field has a date in the past, we only want to compare the month and date, not the year. If month and date combination is with this range (date() + NoD) then it should be picked up regardless of the year. In my example December 26, 2005 will be picked up because date is in the past (like a birthdate) and day/month combination (December 26) is within the range (Today: December 24 + NoD=16) If the date field has a date in the future, then you also consider the year to see if that date falls within range. In this case something like this would work: If date = (Date() + NoD) and date = Date() Then True I hope it makes sense now. Thanks. "strive4peace" wrote in message ... Hi Dragon, I did not really follow what you wanted -- just looked at the first 3 records, which you said you wanted and ignored what you wrote because it seemed to contradict that so you want everything between Today and whatever day is will be when the NumDays comes around? like this: WHERE DateField BETWEEN Date() and (Date() + NumDays) ?? Your test date is Dec 24, 2008 -- why would that pick up December 26, 2005 (FIVE)? -- or is that a typo? that is what was confusing me... Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Dragon wrote: Thank you Crystal for your reply. My sample didn't have the actual field names. I used these words (Date, # of Days etc) just to make it easier to explain :-) Having said that I do not believe your solution will work. It will pickup all date prior to (Date() + NumDays) while I only want date where month/day combination is within range. To explain this a bit mo Today's Date: Dec 24, 2008 # of Days (NoD): 16 Date1 : December 26, 2005 Date 2: April 05, 2008 Date 3: Jan 03, 2009. Query should pickup only Date 1 and Date 3 and not Date 2 because month/date combination is not within the specified range (date() + NoD). Thanks. "strive4peace" wrote in message ... Hi Dragon, SELECT * FROM Tablename WHERE DateField = (Date() + NumDays) I used NumDays instead of [# of Days] since it is not a good idea to use spaces or special characters (except underscore) in fieldnames btw, do not use DATE as a fieldname or controlname, it is a reserved word Problem names and reserved words in Access, by Allen Browne http://www.allenbrowne.com/Ap****ueBadWord.html Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Dragon wrote: Hi, I am trying to create a query which performs some date calculations and pulls records based on the calculation. I think it will be simpler to explain with an example; Data: Task Date # of Days Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 If today is December 24, 2008, Query I am trying to write will display the fir three records from this data. Essentially calculate the date/month to see if the date/month are within the (# of Days) period from today. You can say it is similar to figuring our if your birthday is coming up with x number of days. If the date is in the past, it will act as it telling you if your birthday is coming up within x number of days. If the date is in future, it will act as if it is a reminder for that date if that date is within x number of days. thanks. Thank you. |
#9
|
|||
|
|||
Having trouble with Date calculation Query logic
OK. *IF* I understand correctly the where clause would look like the following.
WHERE DateSerial(Year(Date()),Month(MyDate),Day(MyDate)) Between Date() and DateAdd("d",NumDays,Date()) OR DateSerial(Year(Date())+1,Month(MyDate),Day(MyDate )) Between Date() and DateAdd("d",NumDays,Date()) For Task1 that equates to 1/1/2008 between 12/24/08 and 1/9/2009 or 1/1/2009 between 12/24/08 and 1/9/2009 For Task5 that equates to 4/9/2008 between 12/24/2008 and 1/8/2009 or 4/9/2009 between 12/24/2008 and 1/8/2009 Bench testing indicated you should get the desired results. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Dragon wrote: John, We could be talking about the same thing but from different angle but I am a bit confused so I will use your approach to clarify things. Given Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 And Today being December 24, 2008 Then Return record for Task 1 If Date [Jan 01] is between Today (Dec 24, 2008) and 16 Days from Today (Jan 09, 2009) = True in this case Task 2 If Date [Dec 25] is between Today (Dec 24, 2008) and 14 Days from Today (Jan 7, 2009) = True in this case Task 3 If Date [Jan 05, 2009] is between Today (Dec 24, 2008) and 20 Days from Today (Jan 13, 2009) = True in this case Task 4 If Date [Jan 25, 2010] is between Today (Dec 24, 2008) and 5 Days from Today (Dec 29, 2008) = False in this case Task 5 If Date [Apr 09] is between Today (Dec 24, 2008) and 15 Days from Today (Jan 08, 2009) = False in this case The Login I can come up with is: If MyDate Date () Then MyMonth = Month (MyDate) MyDay = Day(MyDate) If (MyMonthMyDay) = MonthDay(Date()) and (MyMonthMyDay) = MonthDay(Date()) + NoD Then Select Record = True Else If MyDate Date () + NoD then Select Record = True Else Select record = False If it still doesn't make sense and I am confusing everyone, then I think I am simply not capable of explaining this right :-) One way to think if this problem is that it has two parts. - Find out if a birthday is coming up within a given period. Typically you will assume the given period to be fixed, say 14 days, but in this case each birthday has a variable period.And since birthday dates are in the past, you have to ignore the year when calculating. - Find out if there is an upcoming event coming up within a given period for each event. Since events are in the future, you have to make sure look at the year as well and not display event for someting that isn't due for a few years. "John Spencer (MVP)" wrote in message ... Let me rephrase the problem to confirm my understanding. Given Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 Then Return record for Task 1 If today is between Jan 1 and Jan 17 Task 2 If today is between Dec 25 and Dec 31 or between Jan 1 and Jan 8 Task 3 If today is between Jan 5 and Jan 25 Task 4 if today is between Jan 25 and Jan 30 Task 5 if today is between Apr 9 and Apr 24 I think the following may work for you. SELECT * FROM Table WHERE Date() Between DateSerial(Year(Date()),Month(DateField),Day(DateF ield)) and DateSerial(Year(Date()),Month(DateField),Day(DateF ield)+ NumDays) OR Date() Between DateSerial(Year(Date())+1,Month(DateField),Day(Dat eField)) and DateSerial(Year(Date())+1,Month(DateField),Day(Dat eField)+ NumDays) The second criteria is to handle dates at the end of the year (such as Dec 25) and could also be written as DateAdd("yyyy",1,Date()) Between DateSerial(Year(Date()),Month(DateField),Day(DateF ield)) and DateSerial(Year(Date()),Month(DateField),Day(DateF ield)+ NumDays) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Dragon wrote: I am sorry if I am not making much sense here. I will try again. If the date field has a date in the past, we only want to compare the month and date, not the year. If month and date combination is with this range (date() + NoD) then it should be picked up regardless of the year. In my example December 26, 2005 will be picked up because date is in the past (like a birthdate) and day/month combination (December 26) is within the range (Today: December 24 + NoD=16) If the date field has a date in the future, then you also consider the year to see if that date falls within range. In this case something like this would work: If date = (Date() + NoD) and date = Date() Then True I hope it makes sense now. Thanks. "strive4peace" wrote in message ... Hi Dragon, I did not really follow what you wanted -- just looked at the first 3 records, which you said you wanted and ignored what you wrote because it seemed to contradict that so you want everything between Today and whatever day is will be when the NumDays comes around? like this: WHERE DateField BETWEEN Date() and (Date() + NumDays) ?? Your test date is Dec 24, 2008 -- why would that pick up December 26, 2005 (FIVE)? -- or is that a typo? that is what was confusing me... Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Dragon wrote: Thank you Crystal for your reply. My sample didn't have the actual field names. I used these words (Date, # of Days etc) just to make it easier to explain :-) Having said that I do not believe your solution will work. It will pickup all date prior to (Date() + NumDays) while I only want date where month/day combination is within range. To explain this a bit mo Today's Date: Dec 24, 2008 # of Days (NoD): 16 Date1 : December 26, 2005 Date 2: April 05, 2008 Date 3: Jan 03, 2009. Query should pickup only Date 1 and Date 3 and not Date 2 because month/date combination is not within the specified range (date() + NoD). Thanks. "strive4peace" wrote in message ... Hi Dragon, SELECT * FROM Tablename WHERE DateField = (Date() + NumDays) I used NumDays instead of [# of Days] since it is not a good idea to use spaces or special characters (except underscore) in fieldnames btw, do not use DATE as a fieldname or controlname, it is a reserved word Problem names and reserved words in Access, by Allen Browne http://www.allenbrowne.com/Ap****ueBadWord.html Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * Dragon wrote: Hi, I am trying to create a query which performs some date calculations and pulls records based on the calculation. I think it will be simpler to explain with an example; Data: Task Date # of Days Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 If today is December 24, 2008, Query I am trying to write will display the fir three records from this data. Essentially calculate the date/month to see if the date/month are within the (# of Days) period from today. You can say it is similar to figuring our if your birthday is coming up with x number of days. If the date is in the past, it will act as it telling you if your birthday is coming up within x number of days. If the date is in future, it will act as if it is a reminder for that date if that date is within x number of days. thanks. Thank you. |
#10
|
|||
|
|||
Having trouble with Date calculation Query logic
Dragon wrote:
John, We could be talking about the same thing but from different angle but I am a bit confused so I will use your approach to clarify things. Given Task1 Jan 01, 1980 16 Task 2 Dec 25, 2007 14 Task 3 Jan 05, 2009 20 Task 4 Jan 25, 2010 5 Task 5 Apr 09, 1995 15 And Today being December 24, 2008 Then Return record for Task 1 If Date [Jan 01] is between Today (Dec 24, 2008) and 16 Days from Today (Jan 09, 2009) = True in this case Task 2 If Date [Dec 25] is between Today (Dec 24, 2008) and 14 Days from Today (Jan 7, 2009) = True in this case Task 3 If Date [Jan 05, 2009] is between Today (Dec 24, 2008) and 20 Days from Today (Jan 13, 2009) = True in this case Task 4 If Date [Jan 25, 2010] is between Today (Dec 24, 2008) and 5 Days from Today (Dec 29, 2008) = False in this case Task 5 If Date [Apr 09] is between Today (Dec 24, 2008) and 15 Days from Today (Jan 08, 2009) = False in this case Here is an alternate WHERE condition: Based on: http://groups.google.com/group/comp....3f5063bdd83451 Try: SELECT * FROM Table WHERE Int(Format(DateAdd("d", -1, Date()), "yyyy.mmdd") - Format(DateField, "yyyy.mmdd")) Int(Format(DateAdd("d", NumDays, Date()), "yyyy.mmdd") - Format(DateField, "yyyy.mmdd")); That is, if the age in years from the anniversary/birthday date is the same for the start (minus 1) and end dates, then there's no change within the period in question. James A. Fortune |
Thread Tools | |
Display Modes | |
|
|