How to program work days

[mmax98]

I am new to BRIO! How do I program a report to view work days only? The work calendar does not include Sat. and Sun. Will an if statement work or is there a more simple way to do it?

 

[cctonto]

This worked for me.

First, I got the span dates I wanted to use, I.E. 6/1/02 to 7/12/02. I ran an Append query into a table called WorkDays just used for this purpose. This table contained the fields TheDate, Day_Number, and Holiday. I ran a Update query on this table and assigned a number using Weekday(). It would look like this...Weekday([Thedate]). This would assign numbers for the days of the week... Sunday is 1, Monday is 2...and so on. Then I would run a Delete query and delete all numbers 1 and 7. This would leave only the work days wthin your span of dates.

I also used another table of holidays. A quick Update query would mark the holiday with the letter "H". I'd delete these too. Now, a list remains that is weekend and holiday free.

To automate all the queries you'll have use this code...

Dim stDocName As String

stDocName = "first query name here"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "second query name here"
DoCmd.OpenQuery stDocName, acNormal, acEdit



Good Luck,
Steve

 

[trystanhuwwilliams]

Private Sub dmr_lostfocus()

Dim hols As Variant
'Procedure to extract all Saturdays, Sundays & Statutory Holidays From Calculation
'which works out the number of 'working' days a letter has been logged
'A simple function in Excel 'NETWORKDAYS' can accomplish this in one expression, but
'it doesn't work in Access
hols = Array("29/03/2002", "01/04/2002", "06/05/2002", "03/06/2002", "04/06/2002", "26/08/2002", "27/08/2002", "23/12/2002", "24/12/2002", "25/12/2002", "26/12/2002", "27/12/2002", "03/01/2002&quot
y = Date
D = [DMR]

XDATE = y - D

COUNTER = 1



For x = 1 To XDATE

For xx = 1 To 12
tempx = StrComp(D, hols(xx), vbTextCompare)
If tempx = 0 Then COUNTER = COUNTER + 1
Next xx

wd = Weekday(D)
If wd = 1 Or wd = 7 Then COUNTER = COUNTER + 1
D = D + 1
Next x

xdate1 = XDATE - COUNTER

[Overdue].Value = xdate1


End Sub

 

[133108]

Hi,

create a computed column in the results section. add the following expression

ToChar(Full_Date, 'dddd')

where full_date is your standard calendar date column.

You'll see that this populates the column with Day names.

Now create a static limit on this column and select all days except sat and sun.


Now this limit does not pop-up every time you process the query - but will automatically filter out Sat and Sun from the result set

Think this the easiest way

cheers