Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to program work days

How to program work days

How to program work days

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?

RE: How to program work days

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,

RE: How to program work days

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")
y = Date
D = [DMR]

XDATE = y - D


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

RE: How to program work days


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 :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close