Solved

Date calculation problem

Posted on 2006-06-08
Medium Priority
287 Views
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!

Thanks.
0
• 4
• 2

LVL 65

Expert Comment

ID: 16862685
U want to

try using dateadd-  logic should be

0

LVL 65

Expert Comment

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

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")

Of course it shoud work with CDATE wrapped as well
0

Author Comment

ID: 16862837
I have this now:

SELECT *
FROM [Main - Personal Details]

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

Any ideas?

0

LVL 65

Accepted Solution

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]
0

Author Comment

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

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

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
Course of the Month17 days, 2 hours left to enroll