asked on

Date calculation problem

Hi all,

I am using MS Access DB and Dreamweaver to produce a front end web page. I have a column 'NextCallDate' in the database, and a text field on my web page 'MMColParam' in which a number of days can be entered. What I want to acheive is a list returned with all of the rows that have a 'NextCallDate' which is within the next 'MMColParam' number of days.

The statement I have at the moment is:

SELECT * FROM [Main - Personal Details] WHERE DateDiff('d', NextCallDate, now()) < 'MMColParam' & DateDiff('d', NextCallDate, now()) > -1

but this does not work as I get entries returned that have a NextCallDate which is in 2012!

Can anyone please help?

Thanks.

rockiroads

U want to

try using dateadd- logic should be

NextCalDate <= DateAdd("d",MMColParam,Now())

rockiroads

Is it a date field? u may need to wrap with CDATE?
e.g

CDate(NextCalDate) <= DateAdd("d",MMColParam.Now())

DateAdd is supposed to return a date, so the above comparison like that may work

but the idea is you understand the logic in what I was trying to do

If u need from today to the number of days specified
u can use Between or >=

Im so used to using Format, I will give u my example using that

WHERE Format(NextCalDate,"YYYYMMDD") >= Format(Now(),"YYYYMMDD")
AND Format(NextCalDate,"YYYYMMDD") <= Format(DateAdd("d",MMColParam,Now()),"YYYYMMDD")

Of course it shoud work with CDATE wrapped as well

adm-computing

ASKER

I have this now:

SELECT *
FROM [Main - Personal Details]
WHERE NextCalDate <= DateAdd("d",MMColParam,Now())

But I get the error message "Too few parameters. Expected 2.".

Any ideas?

Thanks for your help :)

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adm-computing

ASKER

Thanks.

I also want to check that the calculated answer is not in the past (ie. it is not negative).

I tried the following:

SELECT *
FROM [Main - Personal Details]
WHERE NextCalDate <= DateAdd('d',MMColParam,Now()) & NextCalDate - Now() > -1

but I get a type mismatch. Is it something to do with the way Now() works?

rockiroads

Now() is used to return the current date and time

But does this sql run server or client side (I guess it depends how u opened the recordset)

What u can try is Date(), or whatever the function is to return current date.

Failing, that just create your own variable holding current date and try using that instead of Now()