|
| 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
September 12th, 2005, 06:24 PM
|
|||
|
|||
Subtract days from Left(Date(),5)
I need a spreadsheet to generate 5 days before the anniversary date of when
the contract was issued. Example I have an Issue_Date of 10/10/2002 and I want to be notified this year on 10/05/2005 The code I came up with is below: SELECT Left([Issue Date],5) AS Expr1 FROM Master WHERE (((Left([Issue Date],5))=Left(Date(),5))); However, I still need to subtract my 5 days but the below is not working: SELECT Left([Issue Date],5) AS Expr1 FROM Master WHERE (((Left([Issue Date],5))=(Left(Date(),5))-5)); Any ideas on how I can subtract the five days? Thank you in advance. 
|
|
#2
September 12th, 2005, 07:01 PM
|
|||
|
|||
|
You can not do LEFT of a date as the date is stored as a number counting from
1/1/1900. Use something like this -- [Issue Date]-5 "consjoe" wrote: I need a spreadsheet to generate 5 days before the anniversary date of when the contract was issued. Example I have an Issue_Date of 10/10/2002 and I want to be notified this year on 10/05/2005 The code I came up with is below: SELECT Left([Issue Date],5) AS Expr1 FROM Master WHERE (((Left([Issue Date],5))=Left(Date(),5))); However, I still need to subtract my 5 days but the below is not working: SELECT Left([Issue Date],5) AS Expr1 FROM Master WHERE (((Left([Issue Date],5))=(Left(Date(),5))-5)); Any ideas on how I can subtract the five days? Thank you in advance.
|
|
#3
September 12th, 2005, 07:36 PM
|
|||
|
|||
|
If I do [Issue Date]-5 and my Issue Date is 10/10/2002 the query would only
pick this record up on 10/05/2002 which doesn't do me any good in 2005. Is there a way that I could use [Issue Date]-5 and then take the Left 5? Thanks Again. "KARL DEWEY" wrote: You can not do LEFT of a date as the date is stored as a number counting from 1/1/1900. Use something like this -- [Issue Date]-5 "consjoe" wrote: I need a spreadsheet to generate 5 days before the anniversary date of when the contract was issued. Example I have an Issue_Date of 10/10/2002 and I want to be notified this year on 10/05/2005 The code I came up with is below: SELECT Left([Issue Date],5) AS Expr1 FROM Master WHERE (((Left([Issue Date],5))=Left(Date(),5))); However, I still need to subtract my 5 days but the below is not working: SELECT Left([Issue Date],5) AS Expr1 FROM Master WHERE (((Left([Issue Date],5))=(Left(Date(),5))-5)); Any ideas on how I can subtract the five days? Thank you in advance.
|
|
#4
September 12th, 2005, 09:22 PM
|
|||
|
|||
|
Use this to get the anniversary --
Anniversary: DateSerial(DatePart("yyyy",Date()),Right("0" & DatePart("m",[BIRTHDAY]),2),DatePart("d",[BIRTHDAY])) Use this for criteria -- =Date()-5 "consjoe" wrote: If I do [Issue Date]-5 and my Issue Date is 10/10/2002 the query would only pick this record up on 10/05/2002 which doesn't do me any good in 2005. Is there a way that I could use [Issue Date]-5 and then take the Left 5? Thanks Again. "KARL DEWEY" wrote: You can not do LEFT of a date as the date is stored as a number counting from 1/1/1900. Use something like this -- [Issue Date]-5 "consjoe" wrote: I need a spreadsheet to generate 5 days before the anniversary date of when the contract was issued. Example I have an Issue_Date of 10/10/2002 and I want to be notified this year on 10/05/2005 The code I came up with is below: SELECT Left([Issue Date],5) AS Expr1 FROM Master WHERE (((Left([Issue Date],5))=Left(Date(),5))); However, I still need to subtract my 5 days but the below is not working: SELECT Left([Issue Date],5) AS Expr1 FROM Master WHERE (((Left([Issue Date],5))=(Left(Date(),5))-5)); Any ideas on how I can subtract the five days? Thank you in advance.
|
|
#5
September 12th, 2005, 09:54 PM
|
|||
|
|||
|
On Mon, 12 Sep 2005 10:24:10 -0700, "consjoe"
wrote: I need a spreadsheet to generate 5 days before the anniversary date of when the contract was issued. Example I have an Issue_Date of 10/10/2002 and I want to be notified this year on 10/05/2005 The code I came up with is below: SELECT Left([Issue Date],5) AS Expr1 FROM Master WHERE (((Left([Issue Date],5))=Left(Date(),5))); However, I still need to subtract my 5 days but the below is not working: SELECT Left([Issue Date],5) AS Expr1 FROM Master WHERE (((Left([Issue Date],5))=(Left(Date(),5))-5)); Any ideas on how I can subtract the five days? Thank you in advance. Try WHERE DateSerial(Year(Date()), Month([Issue Date]), Day([Issue Date]) - 5) = Date() John W. Vinson[MVP]
|
|
#6
September 13th, 2005, 06:34 PM
|
|||
|
|||
|
Thanks John, I have it working now.
"John Vinson" wrote: On Mon, 12 Sep 2005 10:24:10 -0700, "consjoe" wrote: I need a spreadsheet to generate 5 days before the anniversary date of when the contract was issued. Example I have an Issue_Date of 10/10/2002 and I want to be notified this year on 10/05/2005 The code I came up with is below: SELECT Left([Issue Date],5) AS Expr1 FROM Master WHERE (((Left([Issue Date],5))=Left(Date(),5))); However, I still need to subtract my 5 days but the below is not working: SELECT Left([Issue Date],5) AS Expr1 FROM Master WHERE (((Left([Issue Date],5))=(Left(Date(),5))-5)); Any ideas on how I can subtract the five days? Thank you in advance. Try WHERE DateSerial(Year(Date()), Month([Issue Date]), Day([Issue Date]) - 5) = Date() John W. Vinson[MVP]
|
|
#7
September 13th, 2005, 06:35 PM
|
|||
|
|||
|
Thanks Karl, John's was a little bit easier for me to understand. (I am by no
means a Pro) Thank you for your help. "KARL DEWEY" wrote: Use this to get the anniversary -- Anniversary: DateSerial(DatePart("yyyy",Date()),Right("0" & DatePart("m",[BIRTHDAY]),2),DatePart("d",[BIRTHDAY])) Use this for criteria -- =Date()-5 "consjoe" wrote: If I do [Issue Date]-5 and my Issue Date is 10/10/2002 the query would only pick this record up on 10/05/2002 which doesn't do me any good in 2005. Is there a way that I could use [Issue Date]-5 and then take the Left 5? Thanks Again. "KARL DEWEY" wrote: You can not do LEFT of a date as the date is stored as a number counting from 1/1/1900. Use something like this -- [Issue Date]-5 "consjoe" wrote: I need a spreadsheet to generate 5 days before the anniversary date of when the contract was issued. Example I have an Issue_Date of 10/10/2002 and I want to be notified this year on 10/05/2005 The code I came up with is below: SELECT Left([Issue Date],5) AS Expr1 FROM Master WHERE (((Left([Issue Date],5))=Left(Date(),5))); However, I still need to subtract my 5 days but the below is not working: SELECT Left([Issue Date],5) AS Expr1 FROM Master WHERE (((Left([Issue Date],5))=(Left(Date(),5))-5)); Any ideas on how I can subtract the five days? Thank you in advance.
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| switch statement to calculate field | Nancy | Using Forms | 12 | May 18th, 2005 04:08 AM |
| What is wrong w/ this query? | Tom Ellison | Running & Setting Up Queries | 3 | November 19th, 2004 12:30 AM |
| Counting Consecutive Days | Mel | Worksheet Functions | 1 | April 28th, 2004 11:13 PM |
| counting days vers 2 | brad | Worksheet Functions | 3 | April 20th, 2004 06:24 PM |
| Displaying a figure as days, hours and minutes | Arvi Laanemets | Worksheet Functions | 1 | January 16th, 2004 02:27 PM |
Linear Mode
