Date Range but for Current Year

[mchlle]

I have a form that allows the user to choose and open a report from a
drop down box based on the semester they choose from an option box.
Option 1-3 are Spring, Summer and Fall and each semester has their
specific dates. This is what I currently have on the preview button if
option one is selected and continues for the rest of the options with
the appropriate dates.

If (FraWIASemesters = 1) Then
' Preview all records.
DoCmd.OpenReport strReportName, acViewPreview, "", "[Date]
Between #12/16/2004# And #5/15/2005#"
End If

Summer: Between #5/16/2005# And #8/15/2005#"

Fall: Between #8/16/2005# And #12/15/2005#"

How can I set a date range but for current year so that I don't have to
go in and change it on a regular basis?

[pietlinden]

use DateSerial, Date()

something like BETWEEN DateSerial(Year(Date()),5,16) And
DateSerial(Year(Date()),8,15)

and then you have to go out and have a stiff drink or two.

[Bob Quintal]

"mchlle" <mc****@austin.rr.comwrote in
news:11*********************@i3g2000cwc.googlegrou ps.com:

I have a form that allows the user to choose and open a report
from a drop down box based on the semester they choose from an
option box. Option 1-3 are Spring, Summer and Fall and each
semester has their specific dates. This is what I currently
have on the preview button if option one is selected and
continues for the rest of the options with the appropriate
dates.

If (FraWIASemesters = 1) Then
' Preview all records.
DoCmd.OpenReport strReportName, acViewPreview, "",
"[Date]
Between #12/16/2004# And #5/15/2005#"
End If

Summer: Between #5/16/2005# And #8/15/2005#"

Fall: Between #8/16/2005# And #12/15/2005#"

How can I set a date range but for current year so that I
don't have to go in and change it on a regular basis?

Dim dtFrom as date, dtTo as date
Dim stWhereClause as string

select case FraWIASemesters
case 1
dtfrom = "12/16/" & year(now())-2
dtTo = "5/15/" & year(now()-1
case 2
dtfrom = "5/16/" & year(now())-1
dtTo = "8/15/" & year(now()-1
case else
dtfrom = "5/16/1492"
dtTo = "8/15/2525"
end select

stwhereclause = "Between #" & dtfrom & "# AND #" & dtTo & "#"

DoCmd.OpenReport strReportName, acViewPreview..stWhereClause

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[mchlle]

pi********@hotmail.com wrote:

use DateSerial, Date()

something like BETWEEN DateSerial(Year(Date()),5,16) And
DateSerial(Year(Date()),8,15)

and then you have to go out and have a stiff drink or two.

Thanks but this brings up everyone from my query.

I need this to look at my "Date" field to determine who has had
assistance during this semester based on the "date" the record was
entered.
Hope this makes sense.

Thanks again.

[mchlle]

Hi Bob

Thanks, I copied and pasted the code above but kept getting end of
statement errors.
Also, what are these years:

dtfrom = "5/16/1492"
dtTo = "8/15/2525"

[Bob Quintal]

"mchlle" <mc****@austin.rr.comwrote in
news:11**********************@e3g2000cwe.googlegro ups.com:

Hi Bob

Thanks, I copied and pasted the code above but kept getting
end of statement errors.
Also, what are these years:

>dtfrom = "5/16/1492"
dtTo = "8/15/2525"

1492 Columbus discovers America
2525 Title of Zager and Evans song.

I use them when I want to return all records because they are
before any reasonable date in the past and far enough into the
future to capture everything. I use them instead of having a
separate OpenReport statement with no filter
as to the error messages, I missed a ) in typing the dtto
year(now()-1 should be year(now())-1.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com