Complex Query? 1

[BabyPowder2u]

I want to create a query that will select records baed on multiple criteria. I have multiple dates and hours that loop over to the next day. So, I think I need something like this:
If endhour < start hour

I want records from startday to endday-1 where hour between start hour and 2300

and
records from startday+1 to endday where hour between 0 and endhour

can I accomplish this in a single query? and if so, could you provide an example?

Thanks,
T

 

[BabyPowder2u]

I set this up:

Dim dtTmpLastday As Date
Dim dtTmpStartday As Date
Dim dtTmpEndday As Date
Dim dtTmpFirstday As Date

dtTmpLastday = DateAdd("d", -1, Me![cboEndDate1])
dtTmpStartday = Me![cboStartDate1]
dtTmpLastday = Me![cboEndDate1]
dtTmpFirstday = DateAdd("d", -1, Me![cboStartDate1])


MkSchedList = "Select * into tblSchedList from tblScheduleHours Where " & _
"((DayInUse Between dtTmpStartday and dtTmpLastday) " & _
"And (HourInUse Between [Forms]!frmSchedReqNoSubForm![cboStartHour1] and 2300)) " & _
"Or ((DayInUse Between dtTmpFirstday and [Forms]!frmSchedReqNoSubForm![CboEndDate1]) " & _
"And (HourInUse Between 0 and [Forms]!frmSchedReqNoSubForm![cboEndHour1]))"
DoCmd.RunSQL MkSchedList

However, when the DoCmd.RunSQL executes, I am prompted to enter dtTmpStartDay.

Why isn't it accepting it as the variable I defined above it? How should I change it?

 

[PHV]

Try this:
MkSchedList = "Select * into tblSchedList from tblScheduleHours Where " & _
"((DayInUse Between #" & dtTmpStartday & "# and #" & dtTmpLastday & "#) " & _
"And (HourInUse Between [Forms]!frmSchedReqNoSubForm![cboStartHour1] and 2300)) " & _
"Or ((DayInUse Between #" & dtTmpFirstday & "# and [Forms]!frmSchedReqNoSubForm![CboEndDate1]) " & _
"And (HourInUse Between 0 and [Forms]!frmSchedReqNoSubForm![cboEndHour1]))"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BabyPowder2u]

I modified the query:

MkSchedList = "Select * into tblSchedList from tblScheduleHours Where " & _
"((DayInUse Between #"& dtTmpStartday & "# and #" & dtTmpLastday "#) " & _
"And (HourInUse Between [Forms]!frmSchedReqNoSubForm![cboStartHour1] and 2300)) " & _
"Or ((DayInUse Between dtTmpFirstday and [Forms]!frmSchedReqNoSubForm![CboEndDate1]) " & _
"And (HourInUse Between 0 and [Forms]!frmSchedReqNoSubForm![cboEndHour1]))"


but now I get a compile error...

 

[PHV]

Lack of & in the second line.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BabyPowder2u]

Thanks very much.

T