• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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.
0
adm-computing
Asked:
adm-computing
  • 4
  • 2
1 Solution
 
rockiroadsCommented:
U want to

try using dateadd-  logic should be

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

0
 
rockiroadsCommented:
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
0
 
adm-computingAuthor Commented:
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 :)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rockiroadsCommented:
does MMColParam definitely have a value?

or is this being passed into a string?

if so, we may need single quotes. Im guessing this based on your example code


SELECT *
FROM [Main - Personal Details]
WHERE NextCalDate <= DateAdd('d',MMColParam,Now())
0
 
adm-computingAuthor Commented:
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?
0
 
rockiroadsCommented:
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()

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now