Using now() and now()+1 in a query...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I prompt for the value of now() and now()+1, the query results are what
I would expect. When the values of now() and now()+1 are generated via the
system, the query results are "inaccurate". Why?
Below is the simple query, prompting for parameter values, which returns
expected results. Thanks in advance... Chris

SELECT (Count(WorkOrd.[WO Type])) AS [Projects Issued]
FROM WorkOrd
WHERE (((WorkOrd.[WO Type])="Projects") AND ((WorkOrd.[WO Date]) Between
[Now()] And [Now()+1]));
 
chris said:
When I prompt for the value of now() and now()+1, the query results are what
I would expect. When the values of now() and now()+1 are generated via the
system, the query results are "inaccurate". Why?
Below is the simple query, prompting for parameter values, which returns
expected results. Thanks in advance... Chris

SELECT (Count(WorkOrd.[WO Type])) AS [Projects Issued]
FROM WorkOrd
WHERE (((WorkOrd.[WO Type])="Projects") AND ((WorkOrd.[WO Date]) Between
[Now()] And [Now()+1]));
Click to expand...

Hi chris,

Are you sure you want to use Now()? Both Now()+1 and Date()+1 increment
the output by 1 *day.* But Now() includes the system time, which of
course changes every time you perform the operation. Or perhaps your
system clock is set incorrectly? Just a thought.

hth,

LeAnne
 
LeAnne,

Thanks! I am using Date() and Date()+1 in query instead and getting results
wanted. Definitely helped.

Chris

LeAnne said:
chris said:
When I prompt for the value of now() and now()+1, the query results are what
I would expect. When the values of now() and now()+1 are generated via the
system, the query results are "inaccurate". Why?
Below is the simple query, prompting for parameter values, which returns
expected results. Thanks in advance... Chris

SELECT (Count(WorkOrd.[WO Type])) AS [Projects Issued]
FROM WorkOrd
WHERE (((WorkOrd.[WO Type])="Projects") AND ((WorkOrd.[WO Date]) Between
[Now()] And [Now()+1]));
Click to expand...

Hi chris,

Are you sure you want to use Now()? Both Now()+1 and Date()+1 increment
the output by 1 *day.* But Now() includes the system time, which of
course changes every time you perform the operation. Or perhaps your
system clock is set incorrectly? Just a thought.

hth,

LeAnne
Click to expand...
 
chris said:
When I prompt for the value of now() and now()+1, the query results are what
I would expect. When the values of now() and now()+1 are generated via the
system, the query results are "inaccurate". Why?
Below is the simple query, prompting for parameter values, which returns
expected results. Thanks in advance... Chris

SELECT (Count(WorkOrd.[WO Type])) AS [Projects Issued]
FROM WorkOrd
WHERE (((WorkOrd.[WO Type])="Projects") AND ((WorkOrd.[WO Date]) Between
[Now()] And [Now()+1]));
Click to expand...

Try .... AND WorkOrd.[WO Date] Between Date() AND Date()+1

I'm guessing that 1) you are trying to select WO Dates for today and tomorrow,
and 2) that some (or all) of the records you expected to be selected aren't,
and 3) that [WO Date] is a Date/Time type field, and 4) that what you enter at
the "Enter Parameter Value" prompts, when the query works, are dates alone
(without any time part). If that's the case, it's probable that the [WO Date]
field has stored just the date of entry (with the time set to 0, a.k.a., 12AM)
and comparing that to Now() (which includes the present time) is causing the
unintended mis-match. #12/9/04# is not between #12/9/04 4:00PM# and #12/10/04
4:00PM#, but #12/10/04# would be. Date() returns today's date alone (with the
time part set to 0 or 12AM).

BTW, those are really nifty parameter prompts you supplied! They're in fact
not a problem at all, but caused me quite a double-take!

-Greg.
 
Back
Top