Subtract days from Left(Date(),5)

C

consjoe

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.
 
K

KARL DEWEY

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
 
C

consjoe

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
K

KARL DEWEY

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
Click to expand...


consjoe said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
J

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.
Click to expand...

Try

WHERE DateSerial(Year(Date()), Month([Issue Date]), Day([Issue Date])
- 5) <= Date()

John W. Vinson[MVP]
 
C

consjoe

Thanks John, I have it working now.

John Vinson said:
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.
Click to expand...

Try

WHERE DateSerial(Year(Date()), Month([Issue Date]), Day([Issue Date])
- 5) <= Date()

John W. Vinson[MVP]
Click to expand...
 
C

consjoe

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 said:
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
Click to expand...


consjoe said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Top