test

[ste4en]

The results of a cross tab query are shown below. I need to filter these results in a report and am trying the code beneath. I only want to see the rows where an employee's time is greater than 8 hours on any day AND when it is charged to code 3101. In the example below it should return 2 rows - for larry and mikeS. My filter is returning all records. Is my filter correct, how else should I be doing this. Thanks



Code Name Total MondayHRS TuesdayHRS WednesdayHRS ThursdayHRS FridayHRS
2001 John 28.5 8 10 8.5
2201 mike 22.5 8 8 8
2601 mark 48 8 8 8
3001 dean 28.5 8 8 8.5
3101 larry 30 8 8 10
3101 mikeS 29.5 8 10 9.5
3301 julian 29.25 9.75 8 8
3401 javier 28.5 8 10 8
3501 Mick 29 8 8 8
3601 dan 30 8 8 8
3901 sam 30 8 8 8
4101 tony 37 8 8 8
4201 todd 20 8 10 8

strWhere is String
strDocName is String

strWhere: Code= "3101" And MondayHRS > 8_
    Or Code= "3101" And TuesdayHRS > 8_
    Or Code= "3101" And WednesdayHRS > 8_
    Or Code= "3101" And ThursdayHRS > 8_
    Or Code= "3101" And FridayHRS > 8_
    
strDocName = “rptExeededHours”
    DoCmd.OpenReport stDocName, acPreview, , strWhere

 

[ste4en]

Sorry about the header; How can I change it

 

[PHV]

you may try this:

Dim strWhere As String
strWhere = "Code='3101' AND (MondayHRS>8 OR TuesdayHRS>8" _
  & " OR WednesdayHRS>8 OR ThursdayHRS>8 OR FridayHRS>8)"
DoCmd.OpenReport "rptExeededHours", acviewPreview, , strWhere

If code is defined as numeric then get rid of the single quotes.

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

 

[ste4en]

PH - Got it thanks.