Question on Date Range in Query

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
 
B

Brian Camire

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

Richard Albrecht

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
 
B

Brian Camire

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...
 
R

Richard Albrecht

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...
 
R

Richard Albrecht

If I do that won't there be a problem when the Querie Spans into a new year?
 
B

Brian Camire

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...
 
R

Richard Albrecht

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...
 
B

Brian Camire

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Selecting Date Range in Queries 2
Compound IIF Statement 2
date query in the sub-report 1
Update To line in a Query -- incorrect results!! 4
date expression question 1
Return all dates for 'next month' irrespective of year or day 4
rolling 3 months query 3
IIF statement in a query 2

Top