separate by shift.

[JWT]

Hello,

Lets see if anyone has had this problem before. In a Query that is set up to pull a downtime report and is set to key on by dates, weekly or monthly. The report works fine and the [reportdate] is in the system as #mm/dd/yyyy hh:nn:ss# AM/PM all in one column.

Now this is what I would like to do, be able to pull the same information and separate by shifts. It will work with this in the criteria; Between #7/7/2003 7:00:00 AM# And #7/7/2003 3:00:00 PM# and just show 7am to 3pm for that day. If I enter dates for a week I get this pop-up; Date Type Mismatch In Criteria Expression.

Has someone had this problem and did you fix it?
Thank you, JWT (Access2000)

 

[Fizzio]

Split the field into 2, dates and times by using the FormatDateTime or Datepart functions eg
ShiftDate:FormatDateTime([YourField],vbShortDate)
ShiftTime:FormatDateTime([YourField],vbShortTime)

then set your criteria as previously

Between Date1 and Date2
Between Time1 and Time2

hth

 

[JWT]

Not Working

Fizzio,

Did not get it to work maybe the way I set-up the two columns.

Expr 2: reportdate (set to show Long Time) Between #7:00AM# and #3:00PM#

Expr 3: reportdate (set to show Short Date) Between #7/7/03# and #7/13/03#

With both in place the query came up blank, with just date it did work. I tried to set up another column that was to list shift as A, B, C. This was my goal to show on a report the downtime by shift and when this ran all the times had the same return.

This was my Expr.

Expr 4: IIF ([reportdate] Between #7:00AM# and #3:00PM#, “A”, “B”)

This returned all times listed as “B”. I do not know why any other help you may have with this one, Thanks JWT