?
Solved

Date calculation problem

Posted on 2006-06-08
6
Medium Priority
?
287 Views
Last Modified: 2010-08-05
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
Comment
Question by:adm-computing
  • 4
  • 2
6 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16862685
U want to

try using dateadd-  logic should be

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

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16862715
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
 

Author Comment

by:adm-computing
ID: 16862837
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 16862971
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
 

Author Comment

by:adm-computing
ID: 16868265
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16881414
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question