Question on Date Range in Query

  • Thread starter Thread starter Richard Albrecht
  • Start date Start date
R

Richard Albrecht

Hi,

I'm using a Date field called: StartDt

I'm trying to do a query that would only return records
where (StartDt = current month ) or
(StartDt = Current Month + 1 month) or
(StartDt = Current Month + 2 month)

Like if Current Month is Nov 2003 the returned records would have a start
date in
Nov 2003, Dec 2003 and Jan 2004.

What's the best way to accomplish this?

TIA
 
You might try a WHERE clause that looks something like this:

WHERE
StartDt >= DateSerial(Year(Date()), Month(Date()), 1)
AND
StartDt < DateSerial(Year(Date()), Month(Date()) + 3, 1)
 
Thanks for the answer
Still having problems, If I execute in the imediate window:

print #8/1/2003# >= DateSerial(Year(Date()), Month(Date()), 1) and
#8/1/2003# < DateSerial(Year(Date()), Month(Date()) + 3, 1)

I Get a responce back True

If I Put

.[Field] >= DateSerial(Year(Date()), Month(Date()), 1) and
.[Field] < DateSerial(Year(Date()), Month(Date()) + 3, 1)

as an expression in the querie window I get compiler error. I've been
trying to get this to work for over an hour it's very frustrating.

Any Ideas

Rich
 
In query design view, you would set the value in the Criteria row for your
date field to something like
=DateSerial(Year(Date()), Month(Date()), 1) AND <DateSerial(Year(Date()),
Click to expand...
Month(Date()) + 3, 1)

Hope this helps.


Richard Albrecht said:
Thanks for the answer
Still having problems, If I execute in the imediate window:

print #8/1/2003# >= DateSerial(Year(Date()), Month(Date()), 1) and
#8/1/2003# < DateSerial(Year(Date()), Month(Date()) + 3, 1)

I Get a responce back True

If I Put

.[Field] >= DateSerial(Year(Date()), Month(Date()), 1) and
.[Field] < DateSerial(Year(Date()), Month(Date()) + 3, 1)

as an expression in the querie window I get compiler error. I've been
trying to get this to work for over an hour it's very frustrating.

Any Ideas

Rich





Brian Camire said:
You might try a WHERE clause that looks something like this:

WHERE
StartDt >= DateSerial(Year(Date()), Month(Date()), 1)
AND
StartDt < DateSerial(Year(Date()), Month(Date()) + 3, 1)
Click to expand...
Click to expand...
 
I pasted that in directly and am still getting compiler error in expression.



Brian Camire said:
In query design view, you would set the value in the Criteria row for your
date field to something like
=DateSerial(Year(Date()), Month(Date()), 1) AND <DateSerial(Year(Date()),
Click to expand...
Month(Date()) + 3, 1)

Hope this helps.


Richard Albrecht said:
Thanks for the answer
Still having problems, If I execute in the imediate window:

print #8/1/2003# >= DateSerial(Year(Date()), Month(Date()), 1) and
#8/1/2003# < DateSerial(Year(Date()), Month(Date()) + 3, 1)

I Get a responce back True

If I Put

.[Field] >= DateSerial(Year(Date()), Month(Date()), 1) and
.[Field] < DateSerial(Year(Date()), Month(Date()) + 3, 1)

as an expression in the querie window I get compiler error. I've been
trying to get this to work for over an hour it's very frustrating.

Any Ideas

Rich





Brian Camire said:
You might try a WHERE clause that looks something like this:

WHERE
StartDt >= DateSerial(Year(Date()), Month(Date()), 1)
AND
StartDt < DateSerial(Year(Date()), Month(Date()) + 3, 1)


Hi,

I'm using a Date field called: StartDt

I'm trying to do a query that would only return records
where (StartDt = current month ) or
(StartDt = Current Month + 1 month) or
(StartDt = Current Month + 2 month)

Like if Current Month is Nov 2003 the returned records would have a start
date in
Nov 2003, Dec 2003 and Jan 2004.

What's the best way to accomplish this?

TIA
Click to expand...
Click to expand...
Click to expand...
 
What is the exact error message you are getting?

Richard Albrecht said:
I pasted that in directly and am still getting compiler error in expression.



Brian Camire said:
In query design view, you would set the value in the Criteria row for your
date field to something like
=DateSerial(Year(Date()), Month(Date()), 1) AND
Click to expand...
<DateSerial(Year(Date()),
Month(Date()) + 3, 1)

Hope this helps.


Richard Albrecht said:
Thanks for the answer
Still having problems, If I execute in the imediate window:

print #8/1/2003# >= DateSerial(Year(Date()), Month(Date()), 1) and
#8/1/2003# < DateSerial(Year(Date()), Month(Date()) + 3, 1)

I Get a responce back True

If I Put

.[Field] >= DateSerial(Year(Date()), Month(Date()), 1) and
.[Field] < DateSerial(Year(Date()), Month(Date()) + 3, 1)

as an expression in the querie window I get compiler error. I've been
trying to get this to work for over an hour it's very frustrating.

Any Ideas

Rich





You might try a WHERE clause that looks something like this:

WHERE
StartDt >= DateSerial(Year(Date()), Month(Date()), 1)
AND
StartDt < DateSerial(Year(Date()), Month(Date()) + 3, 1)


Hi,

I'm using a Date field called: StartDt

I'm trying to do a query that would only return records
where (StartDt = current month ) or
(StartDt = Current Month + 1 month) or
(StartDt = Current Month + 2 month)

Like if Current Month is Nov 2003 the returned records would have a
start
date in
Nov 2003, Dec 2003 and Jan 2004.

What's the best way to accomplish this?

TIA
Click to expand...
Click to expand...
Click to expand...
 
Sorry it took so long to get back, But here's the whole query and it
generates a compiler error in query expression.

SELECT tblMembership.*, tblMembership.[Valid To]
FROM tblMembership
Where tblMembership.[Valid To]>=DateSerial(Year(Date()), Month(Date()), 1)
AND tblMembership.[Valid To] <DateSerial(Year(Date()),
Month(Date()) + 3, 1);


Brian Camire said:
What is the exact error message you are getting?

Richard Albrecht said:
I pasted that in directly and am still getting compiler error in expression.



Brian Camire said:
In query design view, you would set the value in the Criteria row for your
date field to something like

=DateSerial(Year(Date()), Month(Date()), 1) AND <DateSerial(Year(Date()),
Month(Date()) + 3, 1)

Hope this helps.


Thanks for the answer
Still having problems, If I execute in the imediate window:

print #8/1/2003# >= DateSerial(Year(Date()), Month(Date()), 1) and
#8/1/2003# < DateSerial(Year(Date()), Month(Date()) + 3, 1)

I Get a responce back True

If I Put

.[Field] >= DateSerial(Year(Date()), Month(Date()), 1) and
.[Field] < DateSerial(Year(Date()), Month(Date()) + 3, 1)

as an expression in the querie window I get compiler error. I've been
trying to get this to work for over an hour it's very frustrating.

Any Ideas

Rich





You might try a WHERE clause that looks something like this:

WHERE
StartDt >= DateSerial(Year(Date()), Month(Date()), 1)
AND
StartDt < DateSerial(Year(Date()), Month(Date()) + 3, 1)


Hi,

I'm using a Date field called: StartDt

I'm trying to do a query that would only return records
where (StartDt = current month ) or
(StartDt = Current Month + 1 month) or
(StartDt = Current Month + 2 month)

Like if Current Month is Nov 2003 the returned records would
Click to expand...
Click to expand...
have
a
Click to expand...
Click to expand...
 
I cannot reproduce the error in either Access 97 or 2000 -- the query works
for me.

You might try removing the redundant tblMembership.[Valid To] from the
SELECT clause, as in:

SELECT tblMembership.*
FROM tblMembership
Where tblMembership.[Valid To]>=DateSerial(Year(Date()), Month(Date()), 1)
AND tblMembership.[Valid To] <DateSerial(Year(Date()),
Month(Date()) + 3, 1);



Richard Albrecht said:
Sorry it took so long to get back, But here's the whole query and it
generates a compiler error in query expression.

SELECT tblMembership.*, tblMembership.[Valid To]
FROM tblMembership
Where tblMembership.[Valid To]>=DateSerial(Year(Date()), Month(Date()), 1)
AND tblMembership.[Valid To] <DateSerial(Year(Date()),
Month(Date()) + 3, 1);


Brian Camire said:
What is the exact error message you are getting?

Richard Albrecht said:
I pasted that in directly and am still getting compiler error in expression.



In query design view, you would set the value in the Criteria row
Click to expand...
for
your
date field to something like

=DateSerial(Year(Date()), Month(Date()), 1) AND <DateSerial(Year(Date()),
Month(Date()) + 3, 1)

Hope this helps.


Thanks for the answer
Still having problems, If I execute in the imediate window:

print #8/1/2003# >= DateSerial(Year(Date()), Month(Date()), 1) and
#8/1/2003# < DateSerial(Year(Date()), Month(Date()) + 3, 1)

I Get a responce back True

If I Put

.[Field] >= DateSerial(Year(Date()), Month(Date()), 1) and
.[Field] < DateSerial(Year(Date()), Month(Date()) + 3, 1)

as an expression in the querie window I get compiler error. I've been
trying to get this to work for over an hour it's very frustrating.

Any Ideas

Rich





You might try a WHERE clause that looks something like this:

WHERE
StartDt >= DateSerial(Year(Date()), Month(Date()), 1)
AND
StartDt < DateSerial(Year(Date()), Month(Date()) + 3, 1)


Hi,

I'm using a Date field called: StartDt

I'm trying to do a query that would only return records
where (StartDt = current month ) or
(StartDt = Current Month + 1 month) or
(StartDt = Current Month + 2 month)

Like if Current Month is Nov 2003 the returned records would
Click to expand...
have
a
start
date in
Nov 2003, Dec 2003 and Jan 2004.

What's the best way to accomplish this?

TIA
Click to expand...
Click to expand...
Click to expand...
 
Back
Top