December 2015 ~ LEARN MS-ACCESS TIPS AND TRICKS

Tuesday, December 15, 2015

Calculating Work Days from Date Range

Introduction.

How to find the number of workdays (excluding Saturdays and Sundays) from a date range in Microsoft Access?

The logic is simple, first find out how many whole weeks are there in the date range. Multiplying whole weeks by 5 gives the number of workdays from whole weeks. From the remaining days find and exclude Saturdays and Sundays, if any. Add the remaining days to the total workdays.

DateDiff(), and DateAdd() functions are used for calculations, and the Format() function gets day-of-the-week in the three-character form to find Saturday and Sunday to exclude from the remaining days.

Find the VBA Code segments for the above steps below, and the full VBA Work_Days() Function Code at the end of this Article.

Find the number of Whole Weeks between Begin-Date and End-Date:
```
WholeWeeks = DateDiff("w", BeginDate, EndDate)
```
The WholeWeeks * 5 (7 - Saturdays & Sundays) will give the number of working days in whole weeks. Now, all that remains to find is how many working days are left in the remaining days if any?

Find the date after the whole weekdays:

DateCnt = DateAdd("ww", WholeWeeks, BeginDate)

Find the number of workdays in the remaining days by checking and excluding Saturdays and Sundays:

Do While DateCnt <= EndDate
      If Format(DateCnt, "ddd") <> "Sun" And _
        Format(DateCnt, "ddd") <> "Sat" Then
         EndDays = EndDays + 1
      End If
      DateCnt = DateAdd("d", 1, DateCnt)'increment the date by 1
    Loop

Calculate the Total Workdays:
```
Work_Days = Wholeweeks * 5 + EndDays
```

The Whole Calculation in Work_Days Function.

The full VBA Code of the Work_Days() Function is given below:

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

   Dim WholeWeeks As Variant
   Dim DateCnt As Variant
   Dim EndDays As Integer
         
   On Error GoTo Err_Work_Days

   BegDate = DateValue(BegDate)
   EndDate = DateValue(EndDate)
'Number of whole weeks
   WholeWeeks = DateDiff("w", BegDate, EndDate)
'Next date after whole weeks of 7 days each
   DateCnt = DateAdd("ww", WholeWeeks, BegDate)
   EndDays = 0 'to count number of days except Saturday & Sunday

   Do While DateCnt <= EndDate
      If Format(DateCnt, "ddd") <> "Sun" And _
        Format(DateCnt, "ddd") <> "Sat" Then
         EndDays = EndDays + 1
      End If
      DateCnt = DateAdd("d", 1, DateCnt)'increment the date by 1
    Loop
'Calculate total work days and return the result
   Work_Days = WholeWeeks * 5 + EndDays

Exit Function

Err_Work_Days:

    ' If either BegDate or EndDate is Null, return a zero
    ' to indicate that no workdays passed between the two dates.

    If Err.Number = 94 Then
                Work_Days = 0
    Exit Function
    Else
' If some other error occurs, provide a message.
    MsgBox "Error " & Err.Number & ": " & Err.Description
    End If

End Function

The above VBA Code was taken from Microsoft Access Help Document.

Wednesday, December 9, 2015

Microsoft Access Form Move Size Action

Introduction.

We design Access Forms that fits into the existing Application Window Width (to display/edit records), or design popup Forms with specific size without borders or scroll bars (can be moved out of the Application Window area too) or Modal type form (popup type forms with its Modal property value set to True) that must be closed, after taking suggested action on it, before you are able to work with other forms.

This type of form opens one over the other (when more than one form is open) on the Application Window. You must enable Overlapping Windows by selecting Office Button - - > Access Options - - > Current Database - - > Document Window Options - - > Overlapping Windows to open the forms in this way, otherwise they will be opened in the Tabbed style in the Application Window.

The Pop-Up Forms.

Popup Forms will open on the exact location of the Application Window, from where you have saved it during design time. If you need more details on this topic visit this Article Link: Positioning popup Forms.

We can open a Microsoft Access Form and move it to a particular location of the Application Window is in the resized state, if necessary, with MoveSize Action on Docmd Object.

The MoveSize Action.

Here, we will learn the usage of MoveSize Action of the DoCmd Object in VBA.

View the YouTube Demo Video given below for reference. Select the 720p HD Option from the Settings for better quality viewing.

Demo Video

When the Supplier Code is selected on the Supplier List Form, the related Product List is displayed, above the Supplier Form to the right of the main form. The width of the Product List Form is not changed, but the height of it changes, depending on the number of items on it.

We need two tables, two Queries, and the Supplier List Form from the Northwind sample database to build this trick. You need to design a Form for the Product List. A Demo Database is given at the end of this Article to download and try out, right away.

The list of Tables, Queries, and Forms required, to build this database, is given below.

Tables:

Suppliers
Products

Queries:

Suppliers Extended
ProductListQ

SQL Code:

SELECT Products.[Supplier IDs], Right([Product Name],Len([product name])-17) AS Product, Products.[List Price], Products.[Quantity Per Unit]
FROM Products
WHERE (((Products.[Supplier IDs].Value)=[forms]![Supplier List]![id]));

Forms:

Supplier List
Product List

Copy and Paste the following VBA Code into the Supplier List Form's VBA Module and save the Form:

Private Sub Company_Click()
Dim frm As Form, ProductForm As String, items As Integer
Dim mainFormHeight As Integer
Dim intHeader As Integer, intFooter As Integer
Dim intH As Integer, frmchild As Form, oneInchTwips As Integer

On Error GoTo Company_Click_Err

ProductForm = "Product List"
oneInchTwips = 1440 'Form's internal value conversion factor

mainFormHeight = Me.WindowHeight

For Each frm In Forms
  If frm.Name = ProductForm Then
    DoCmd.Close acForm, ProductForm
    Exit For
  End If
Next
DoCmd.OpenForm ProductForm
Forms(ProductForm).Refresh
items = DCount("*", "ProductListQ")

Set frmchild = Forms(ProductForm)
'Calc the required height of the chid-form
'based on number of items for selected supplier
intHeader = frmchild.Section(acHeader).Height
intFooter = frmchild.Section(acFooter).Height
'0.272 inch - product item row height
intH = intHeader + items * 0.272 * oneInchTwips + intFooter
intH = intH + oneInchTwips '- one inch margin from bottom
'Move and resize the height of the child form
'4.275 inches to the right from left of the Application Window
'1.25 inches - arbitrary value taken for bottom margin
DoCmd.MoveSize 4.275 * oneInchTwips, mainFormHeight - intH, , (items * 0.272 + 1.25) * oneInchTwips

Company_Click_Exit:
Exit Sub

Company_Click_Err:
MsgBox Err & ": " & Err.Description, , "Company_Click()"
Resume Company_Click_Exit

End Sub

Private Sub Form_Current()
Me.Refresh
End Sub

Note: Don't forget to change the Overlapping Windows option in the Access Option settings mentioned in paragraph two from the top.

Open Supplier List Form.
Click on the Supplier ID Field (with the Company column heading) of any record, to open the Supplier products List to display, in Resized Product List Form, and Moved to its specified location.