Date Subtraction

[JohannIcon]

Dear all,

I have a field where the user can enter the number of days and then I have to search in the database to retreive the dates that are the same number of days or smaller than that number. What I am trying to do basically is calculate an expiry date, where the user enters the number of days remaining for a record to expire, for example 10 days, and I display all those records that will expire in 10 days or lesser. I tried to do it like this:-

if nodays <> &quot;''&quot; then
sqlBanner = sqlBanner & &quot;WHERE banner.validtodate <= &quot; & &quot;'&quot; & DateAdd(&quot;d&quot;, -nodays, Now()) & &quot;'&quot;
end if

But this is giving me errors.

What can I do?

Thanks for your help and time

 

[corneI]

Hi

Is validtodate in time/date formate?
If it is try

sqlBanner = sqlBanner & &quot;WHERE datepart(dd,banner.validtodate) <= &quot; & &quot;'&quot; & DateAdd(&quot;d&quot;, -nodays, Now()) & &quot;'&quot;

I'am just not to sure if the dd should be &quot;dd&quot;???????
If I where you I would use :
DATEDIFF(datepart, startdate, enddate)
instead of the Dateadd but they should work the same

Corne'
#-)

 

[onpnt]

there are a few syntax problems with the statement. a few extra &quot;&&quot; in there.
eg:
sqlBanner = sqlBanner & &quot;WHERE banner.validtodate <= &quot;'&quot; & DateAdd(&quot;d&quot;,nodays,Now()) & &quot;'&quot;

next time try writing the statement to the screen and then work your way back through it to take out syntax errors. I'll take a look at the logic of things again to see if this is wehat you need

____________________________________________________
[sub]The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-3811[/sub]

http://www.onpntwebdesigns.com

 

[onpnt]

I'm typing to slow this morning.

____________________________________________________
[sub]The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-3811[/sub]

http://www.onpntwebdesigns.com

 

[JohannIcon]

ok I solved it guys like this:-

sqlBanner = sqlBanner & &quot; WHERE banner.validtodate BETWEEN Date() AND &quot; & &quot;#&quot; & DateAdd(&quot;d&quot;, -nodays, Now()) & &quot;#&quot;

Thanks for your help!

 

[corneI]

onpnt : How wrong was I ?????
I missed all the syntax errors.
What else did I miss?????????

Please accept my apoligizes
Corne'