SELECT Statement Challenge

[jt463]

I have a table that has an activity start date (actstart) and an activity end date (actend). My users have the ability to select a report start date (rptstart).

I want to query my recordset to extract all records where the rptstart falls within the actstart and actend range.

For example (activity, actstart, actend):
"Hang Drywall", #09/01/06#, #09/03/06#
"Install Cabinets", #09/02/06#, #09/02/06#
"Paint Drywall", #09/04/06#, #09/05/06#

rptstart = #09/02/06#

I would want my report to show: "Hang Drywall, Install Cabinets" for my 9/2/06 date.

So, for my select statement, I am stuck on how to code the criteria.

I am toying with a BETWEEN statement, but I am not coming up with one. Really, I want to pull all records where the rptstart is BETWEEN the actstart and actend dates, but that goes against the way I understand Select statements. I would need to go at it from the actual field names on my recordset and then try to tie it in to my rptstart criteria.

Help!

 

[jrbarnett]

SELECT activity, actstart, actend
FROM mytable
WHERE rptstart between actstart and actend

should do it, alternatively:

SELECT activity, actstart, actend
FROM mytable
WHERE rptstart >= actstart and rptstart <= actend

John

 

[Zion7]

As John said, but since your manipulating the dates from
a form, I usually hard code the controls, into
the SQL for the report. No need to reset Recordsource,
after the fact. Only thing, form always has to be open,
to view report.

SELECT activity, actstart, actend
FROM mytable
WHERE ((rptstart) between (Forms!frmReport!txtDateStart) and (Forms!frmReport!txtDateStart))

if my brackets are off, try pasting
between Forms!frmReport!txtDateStart and Forms!frmReport!txtDateStart
right into the criteria section, of the query pane. It
will surround with brackets on its own

 

[jt463]

Thank you John and Zion7.

I used the select statement from John where I simply used the rptstart record as my criteria with a between statement. It worked beautifully.

Thank you for your time.