Trouble getting Results based on Date AND Time

G

Guest

Thanks for taking the time to read my question.

I am trying to build a query that will return info based on a Start and End
Date as well as a Start and End Time.

If the user types in (on a form):
Start Date: 1-3-05
End Date: 2-3-05
Start Time: 08:00
End Time: 10:00

I only get info for both days between 8 and 10 am.

What I would like to get is info between 8:00 on March 1st and 10:00 on
March 2nd.

How do I build my query to get that?

Thanks again for your help.

Brad

My SQL stmt:

SELECT tblFeedRecords.MixerDTCode, tblFeedRecords.MixerDTMins,
tblFeedRecords.LotNumber, tblFeedRecords.ProductCode,
tblFeedRecords.ProductName, tblFeedRecords.CustomerName,
tblFeedRecords.Tonnes,
IIf(IsNull([tblFeedRecords]![BinNumTwo]),[tblFeedRecords]![BinNumOne],[tblFeedRecords]![BinNumOne]
& " / " & [tblFeedRecords]![BinNumTwo]) AS Bin,
tblFeedRecords.MixerStartTime, tblFeedRecords.MixerStopTime,
tblFeedRecords.Medication, tblFeedRecords.MixerInitial,
IIf(IsNull([tblFeedRecords]![LoadOutBinTwo]),[tblFeedRecords]![LoadOutBinOne],[tblFeedRecords]![LoadOutBinOne]
& " / " & [tblFeedRecords]![LoadOutBinTwo]) AS LoadOut,
tblFeedRecords.MixRecComplete, tblFeedRecords.MixerDate,
tblFeedRecords.AutoFlush
FROM tblFeedRecords
WHERE
(((tblFeedRecords.MixerStartTime)>=[Forms]![frmRPTGenerator]![StartTime]) AND
((tblFeedRecords.MixerStopTime)<=[Forms]![frmRPTGenerator]![EndTime]) AND
((tblFeedRecords.MixRecComplete)=True) AND ((tblFeedRecords.MixerDate)
Between [Forms]![frmRPTGenerator]![StartDate] And
[Forms]![frmRPTGenerator]![EndDate]));
 
G

Guest

Pardon my renaming your field names (I'm unclear as to whether there's a
single date/time entered on the form or two date/time pairs), but I think the
logic you want in the WHERE clause is:
((ActivityDate > CriteriaStartDate) or ((ActivityDate = CriteriaStartDate)
AND (ActivityTime >= CriteriaStartTime)) ) AND
((ActivityDate < CriteriaEndDate) or ((ActivityDate = CriteriaEndDate) AND
(ActivityTime <= CriteriaEndTime)) )

In words, first check whether the activity is after the start date/time
specified; this is true if the activity date is after the specified date or
if the dates are equal but the activity time is later. Then perform a
similary check against the end date/time. Accept records which pass both
checks.
 
D

Douglas J. Steele

Far easier to add the date and time together:

((ActivityDate + ActivityTime) >= (CriteriaStartDate + CriteriaStartTime))
AND
((ActivityDate + ActivityTime) <= (CriteriaEndDate + CriteriaEndTime))

or

(ActivityDate + ActivityTime) BETWEEN (CriteriaStartDate +
CriteriaStartTime) AND
(CriteriaEndDate + CriteriaEndTime)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



bpeltzer said:
Pardon my renaming your field names (I'm unclear as to whether there's a
single date/time entered on the form or two date/time pairs), but I think
the
logic you want in the WHERE clause is:
((ActivityDate > CriteriaStartDate) or ((ActivityDate = CriteriaStartDate)
AND (ActivityTime >= CriteriaStartTime)) ) AND
((ActivityDate < CriteriaEndDate) or ((ActivityDate = CriteriaEndDate) AND
(ActivityTime <= CriteriaEndTime)) )

In words, first check whether the activity is after the start date/time
specified; this is true if the activity date is after the specified date
or
if the dates are equal but the activity time is later. Then perform a
similary check against the end date/time. Accept records which pass both
checks.


Brad said:
Thanks for taking the time to read my question.

I am trying to build a query that will return info based on a Start and
End
Date as well as a Start and End Time.

If the user types in (on a form):
Start Date: 1-3-05
End Date: 2-3-05
Start Time: 08:00
End Time: 10:00

I only get info for both days between 8 and 10 am.

What I would like to get is info between 8:00 on March 1st and 10:00 on
March 2nd.

How do I build my query to get that?

Thanks again for your help.

Brad

My SQL stmt:

SELECT tblFeedRecords.MixerDTCode, tblFeedRecords.MixerDTMins,
tblFeedRecords.LotNumber, tblFeedRecords.ProductCode,
tblFeedRecords.ProductName, tblFeedRecords.CustomerName,
tblFeedRecords.Tonnes,
IIf(IsNull([tblFeedRecords]![BinNumTwo]),[tblFeedRecords]![BinNumOne],[tblFeedRecords]![BinNumOne]
& " / " & [tblFeedRecords]![BinNumTwo]) AS Bin,
tblFeedRecords.MixerStartTime, tblFeedRecords.MixerStopTime,
tblFeedRecords.Medication, tblFeedRecords.MixerInitial,
IIf(IsNull([tblFeedRecords]![LoadOutBinTwo]),[tblFeedRecords]![LoadOutBinOne],[tblFeedRecords]![LoadOutBinOne]
& " / " & [tblFeedRecords]![LoadOutBinTwo]) AS LoadOut,
tblFeedRecords.MixRecComplete, tblFeedRecords.MixerDate,
tblFeedRecords.AutoFlush
FROM tblFeedRecords
WHERE
(((tblFeedRecords.MixerStartTime)>=[Forms]![frmRPTGenerator]![StartTime])
AND
((tblFeedRecords.MixerStopTime)<=[Forms]![frmRPTGenerator]![EndTime]) AND
((tblFeedRecords.MixRecComplete)=True) AND ((tblFeedRecords.MixerDate)
Between [Forms]![frmRPTGenerator]![StartDate] And
[Forms]![frmRPTGenerator]![EndDate]));
Click to expand...
Click to expand...
 
Top