Subtract days from Left(Date(),5)

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.

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

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.

 

[John Vinson]

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]