Microsoft Access 2000 How To's Series

Sunday Dec 1st 2002 by David Nishimoto
Share:

Listen Software Solutions and author David Nishimoto present a new series designed to help Microsoft Access developers discover the secrets of Access programming and empower the developer with the critical knowledge needed to build enterprise-quality applications.


Listen Software Solutions and author David Nishimoto present a new series designed to help Microsoft Access developers get started developing in one of the best software environments on the market today. MS Access has improved to the point it can be considered a serious development environment for large scaled projects. In this series, learn how to build company information infrastructure using MS Access as the backbone. Also learn how to integrate MS Access with Microsoft SQL Server to achieve scaleability, both cheaply and effectively.

Discover the secrets of Access programming and empower yourself with critical knowledge to build enterprise quality applications. Learn the secrets of MS Access and the Web, MS Access macros, VB coding best practices, OCX components, Menus, Modules, and classes. If you're a VB6 buff, test out MS Access development environment and transfer all your skills into one of the best software packages built, today. The MS Access series will help you make the transit. A new Access 2000 "How To" article will debut on the site every Thursday.

    Article One : Adding a Tree View Control and Filter

    Article Two : How To Build a Combo Box as a Data Filter

    Article Three : Building a Query Command Function

    Article Four : Creating Menus Using Access 2000

    Article Five : Access 2000 Data Validation

    Article Six : Access Tree View and List Box Row Source

    Article Seven : Calculating Work Days

    Article Eight : Controlling Report Data

David Nishimoto
For Listen Software


First Access "How To": Adding a Tree View Control and Filter

Back to Database Journal Home

 

 


Adding a Tree View Control and Filter

This Access 2000 How To's article explains how to load data into a treeview control. Nodes in a treeview have a parent key and a child key. In this example, the first level of nodes contains process ids and titles. In the second level, Activities represent dependant child nodes. One or more activies are associated as children to the a process.

When the user clicks on a node, its key is parsed and the contents determine the type of node: process or activity. Depending on the type of node, a filter criteria is applied to a join between the process and activity table.

 

Building the Control

Option Compare Database
Option Explicit

Private Sub FindTreeCtrl_NodeClick(ByVal Node As Object)
    'AT
    Dim iOffset
    Dim iLength
    Dim sId
    Dim i
    Dim ch
    
    iOffset = InStr(Node.Key, "AT")
    If (iOffset > 0) Then
        iLength = Len(Node.Key)
        sId = Mid(Node.Key, iOffset + 2, iLength - iOffset + 1)
        'Filter for a task
        Filter = "Id=" & sId
        FilterOn = True
    Else
        iOffset = InStr(Node.Key, "P")
        
        'Filter for a process
        If iOffset > 0 Then
            iLength = Len(Node.Key)
        
            For i = iOffset To iLength
                ch = Mid(Node.Key, iOffset + i, 1)
                If IsNumeric(ch) = True Then
                    sId = sId & ch
                Else
                    Exit For
                End If
            Next
         
            Filter = "ProcessId=" & sId
            FilterOn = True
        End If
        
    End If
        
End Sub

Private Sub Form_Load()
    Dim objNode As Node
    Dim dbs As Object
    Dim rs As Object
    Dim rs2 As Object
    Dim sProcessId As String
    Dim sProcessName As String
    Dim sActivityId As String
    Dim sActivityTitle As String
    
    Set dbs = CurrentDb
    
    Set rs = dbs.Openrecordset("select * from processes")
    
    With FindTreeCtrl
      .Nodes.Clear
      
      Set objNode = .Nodes.Add(, , "root", "Activities")
      objNode.Expanded = True
      
      Set objNode = .Nodes.Add("root", tvwChild, "TT", "Processes")
      objNode.Expanded = True
      
      Do While Not rs.EOF
            sProcessId = "P" & rs("processid")
            sProcessName = "" & rs("processName")
      
            Set objNode = .Nodes.Add("TT", tvwChild, sProcessId, sProcessName)
            
            Set rs2 = dbs.Openrecordset("select * from activities where processid=" & rs("processid"))
            Do While Not rs2.EOF
                sActivityId = sProcessId & "AT" & rs2("id")
                sActivityTitle = "" & rs2("activitytitle")
                Call .Nodes.Add(sProcessId, tvwChild, sActivityId, sActivityTitle)
                rs2.MoveNext
            Loop
        rs.MoveNext
      Loop
    End With
    
    If Not rs Is Nothing Then
        rs.Close
    End If
    Set rs = Nothing

    If Not rs2 Is Nothing Then
        rs2.Close
    End If
    Set rs2 = Nothing

End Sub


Next Access "How To": How To Build a Combo Box as a Data Filter

Back to Access 2000 How To's Series Home

 


How To Build a Combo Box as a Data Filter

This Access 2000 How To's article explains how to apply a filter to an Access 2000 form by using a combo box as a filter parameter. You can use the filter property to specify a subset of recordset to be displayed.

 

Create the People Table

  1. PeopleId, AutoNumber
  2. PeopleAffected, Text

Create the Process Table

  1. ProcessId, AutoNumber
  2. ProcessName, Text

Create the ProcessToPeople Table

  1. Id, Autonumber
  2. PeopleId, Number
  3. ProcessId, Number

Create a Query called qryPeopleAffected

  1. Join the Process table to the ProcessToPeople table on the ProcessId field.
  2. Join the People table to the ProcessToPeople table on the PeopleId field.

Create a Form called PeopleAffected

  1. Select the form wizard.
  2. Select the qryPeopleAffect query as the date source.
  3. Select all the fields.
  4. Select Columnar as the type of form.
  5. Title the form "People Affected".
  6. In the Form Header, drag and drop an unbound combo box and change the caption to "filter".
  7. Open the Properties dialog for the combo box. Name the combo box "cboSelectableProcess".
  8. Start the Query Builder (QB) from the "Record Source" item property.
  9. QB: Select the Processes table and drag and drop the ProcessId and ProcessName fields into the design grid. Click X to close the window and answer "yes" to updating the property with the SQL. Change column count property item to 2. This will allow you to see the processid and processname in the combo box.
  10. Launch the Code builder from "On Click" property item for the combo box. Choose the code builder option.
  11. Insert the following code fragment:
  12. Private Sub cboSelectableProcess_Click()
       Dim sProcessId
       With cboSelectableProcess
          If .ListIndex <> -1 Then
             'sProcessId = .SelText
             'Retrieves the value of the bound column which may more may not be
             'displayed in the list box
             sProcessId= .Column(.boundcolumn-1)
             Filter = "ProcessId=" & sProcessId
             FilterOn = True
             MsgBox "Filter Applied"
          End If
       End With
    End Sub
    
  13. Dataview: The final result is all records displayed during the initial load, and as the user selects a processid from the cboSelectableProcess combo box, the code parameterizes the filter's "Where" clause with the selected process id.


Next Access "How To": Building a Query Command Function

Back to Access 2000 How To's Series Home

 


Building a Query Command Function

This article explains how to create the ability to input filter criteria from an Access 2000 tabular form (where each row is a record) and press a query button for the filter to be applied.

The filter has four textboxs called txtFirstName, txtMiddleInitial, txtLastName, and txtSSN. The database table "member" has corresponding fields called First, Mi,Last, and SSN.

The AttachAnd function is used to create the SQL filter string: "First='David' and Last='Nishimoto'".

 

Building the Function

    Option Compare Database
    Dim ssql
1. User pressed the Query button
2. Or you could catch a textbox on exit event
    Private Sub cmdQuery_Click()
        BuildQueryCommand
    End Sub
1. BuildQueryCommand builds an filter critera by receiving the fieldname and critiera
2. The filter criteria is applied
    Private Function BuildQueryCommand()
        sSQL = ""
        Call AttachAnd("First", "'" & txtFirstName & "'")
        Call AttachAnd("Mi", "'" & txtMiddleInitial & "'")
        Call AttachAnd("Last", "'" & txtLastName & "'")
        Call AttachAnd("SSN", "'" & txtSSN & "'")
            
        Filter = sSQL
        FilterOn = True
    
    End Function
1. Check for single or multiple criteria
    Single: "first='David'"
    Multiple:"First='David' and Last='Nishimoto'"
    Private Function AttachAnd(sField, sValue)
    
        If sValue = "''" Or sValue = "" Then
            Exit Function
        End If
    
        If Occurances(sSQL, "=") = 0 Then
            sSQL = sSQL & sField & "=" & sValue
        Else
            sSQL = sSQL & " and " & sField & "=" & sValue
        End If
    
    End Function
1. Count the occurances for a pattern in the SQL phrase.
    Private Function Occurances(sSQL, sOperator)
    Dim offset
    Dim iCount

    	offset = 1
    	While offset <> 0
    		offset = InStr(offset + 1, sSQL, sOperator)
    		If offset > 1 Then
    		        iCount = iCount + 1
    		End If
    	Wend

    	Occurances = iCount
        
    End Function


Next Access "How To": Creating Menus Using Access 2000

Back to Access 2000 How To's Series Home

 


Creating Menus Using Access 2000


Overview

My experience with menus has been with Visual C++ and Visual Basic. Both Visual Basic and Visual C++ have wizards to help the developer easily create menu items and subitems attaching code for onclick events. Access 2000, on the other, unfortunately does not. But the good news is that Access 2000 menus are easy to create once you understand the necessary manual steps. This article will walk you step by step through creating a menu.

 

Create Your Application Menu

  1. Click "View", "Toolbars", "Customize".
    Customize has three tab buttons: toolbars, commands, and options.
  2. From the toolbars tab, click on the "new" button.
  3. A popup dialog box will appear requesting a toolbar name; enter "myApplicationMenu".
  4. A menu dialog will appear.

 

Create your Macros for Application Forms and Reports

  1. Create Macros for forms and reports next as the menu items or submenu items will run these macros.
  2. Use either OpenForm or OpenReport and initialize the command with the form name.

 

Adding Menu Items

  1. Click the "Commands" tab from the Customize Dialog.

  2. The Commands tab has two listboxes: Categories and Commands. The order of operation requires you to select a category and drag and drop a command into the menu dialog.

  3. Let's do this. Click Category: File and Command: Custom. Hold the right mouse button down and drag and drop the command into the menu dialog. You'll notice a menu item name "custom" appears.

  4. Change the Menu name by right clicking on the menu item. A popup dialog will appear; change the name to a form or report name. Another way to make the name change is to select the properties item.

  5. The properties dialog has the following textboxes:
        Caption: The display information of the menu item.
        ScreenTip: On hover tooltip.
        OnAction: Select the macro of the form or report you want to launch on a Onclick event.
        Style: Text Only or Image and Text.
        HelpFile: The filename of the helpfile.
        HelpContextId: Context Sensitive help.
        Parameter: Value passed by the menu item.
        Tag: Information that can be used later in the procedure event.

 

Adding Submenus

This requires dragging and dropping two category types: new menu and file categories. The new menu creates a parent menu item and the file category creates submenu types. This is a little tricky, so hopefully my steps will be clear enough for you to accomplish this task.

 

  1. Select new menu from the categories and then new menu from the command.

  2. Drag and Drop the menu item onto the menu dialog.

  3. Select file from the categories and then select custom from the command.

  4. Drag and Drop the submenu item onto the "New menu" item. The trick is not to drop the submenu item onto the menu item, but to instead put it in the drop down box below the menu item. This seems simple, but it initially took me about five minutes to figure that out.

  5. Using the properties popup, change the menu and submenu names and OnAction events.

  6. Close the Customize Dialog.

  7. Drag and Drop the menu dialog onto the toolbar.

 

Activitating or Deactivitating Custom Menus

  1. The custom dialog is used to activate or deactivate toolbar menus.
  2. The custom menu must be open in order to change menu items through the properties dialog.
That's it! Hope you found this walkthrough helpful and instructive.


Next Access "How To": Access 2000 Data Validation

Back to Access 2000 How To's Series Home

 


Access 2000 Data Validation


This article details how to create your own Access 2000 validation routines. While Access 2000 has its own validation rules that you can create for each control, I've found it easier to use Visual Basic techniques to validate data.

You may wonder why these validation routines were created in Access when the data types and bound controls could prevent most of the data errors. In truth, I use these routines to validate using Active Server Pages by converting the validation routines to Javascript. The Javascript was converted to vbascript and run in this Access 2000 demonstration.

You may find creating your own validation routines more flexible and functional than the built-in validation rules in Access.

Objective: To be able to determine if the user input is a valid number, a date, in a list, within a number range, or a field value in a table.

 

Basic Setup

  1. One command button named "cmdValidate"
  2. Five text boxes:

  3. a. txtNumber
    b. txtDate
    c. txtList
    d. txtRange
    e. txtInTable

 

Code

Option Explicit
Option Compare Database
Purpose: The Validate button has been pressed by the user. Each validation type is run.
The IsIntable validation function assumes you have a table called processes with a field named processname.

 

Private Sub cmdValidate_Click()
    Dim errorMessage As String
    Dim List(3) As String
    
    List(0) = "Hello"
    List(1) = "World"
    List(2) = "Utah"
    
    Call IsaNumber(txtNumber, errorMessage)
    Call IsaDate(txtDate, errorMessage)
    Call IsaListItem(txtList, errorMessage, List)
    Call IsInRange(txtRange, errorMessage, 3, 5)
    Call IsInTable(txtInTable, errorMessage, "processes", "processname", "STRING")
    msgbox errorMessage
    
End Sub

Purpose: Validates the user input is a number.
Public Sub IsaNumber(objText As TextBox, errormsg As String)
    On Error GoTo IsANumber_Error
    objText.SetFocus

    If IsNumeric(objText.Text) = False Then
        errormsg = errormsg & objText.name & ":" & objText.Text & " is not numeric " & Chr(13) & Chr(10)
        objText.BackColor = &HFF&
    Else
            objText.BackColor = &HFFFFFF
    End If
    
Exit_IsaNumber:
   Exit Sub
IsANumber_Error:
    
    #If gnDebug Then
        Stop
        Resume
    #End If

    msgbox Err.Description & ":" & Err.Number
    Resume Exit_IsaNumber

End Sub

Purpose: Validates the user input is a date.
Public Sub IsaDate(objText As TextBox, errormsg As String)
    On Error GoTo IsaDate_Error
    objText.SetFocus
    If IsDate(objText.Text) = False Then
        errormsg = errormsg & objText.name & ":" & objText.Text & " is not a date " & Chr(13) & Chr(10)
        objText.BackColor = &HFF&
    Else
            objText.BackColor = &HFFFFFF
    End If
    
Exit_IsaDate:
   Exit Sub
IsaDate_Error:
    
    #If gnDebug Then
        Stop
        Resume
    #End If

    msgbox Err.Description & ":" & Err.Number
    Resume Exit_IsaDate

End Sub

Purpose: A list of valid choices are checked against the user's input. The text comparison is not case sensitive.
Public Sub IsaListItem(objText As TextBox, errormsg As String, List() As String)
    On Error GoTo IsaListItem_Error
    
    Dim sValue
    Dim i
    Dim bFound
    
    objText.SetFocus
    sValue = objText.Value
    
    bFound = False
    For i = 0 To UBound(List) - 1
        If ucase(List(i)) = ucase(sValue) Then
            bFound = True
            Exit For
        End If
    Next
    If bFound = False Then
        errormsg = errormsg & objText.name & ":" & objText.Text & " is not a valid entry " & Chr(13) & Chr(10)
            objText.BackColor = &HFF&
    Else
            objText.BackColor = &HFFFFFF
    End If

Exit_IsaListItem:
   Exit Sub
IsaListItem_Error:
    
    #If gnDebug Then
        Stop
        Resume
    #End If

    msgbox Err.Description & ":" & Err.Number
    Resume Exit_IsaListItem

End Sub

Purpose: Validates the user input is a numeric value within a certain upper and lower range.
Public Sub IsInRange(objText As TextBox, errormsg As String, _
lowerlimit As Integer, upperlimit As Integer)
    On Error GoTo IsInRange_Error
    
    Dim sValue
    objText.SetFocus
    Call IsaNumber(objText, errormsg)
    If IsNull(objText.Value) Then
        sValue = 0
    Else
        sValue = objText.Value
    End If
    
    If sValue < lowerlimit Or sValue > upperlimit Then
        errormsg = errormsg & objText.name & ":" & objText.Text & " is not in range " & Chr(13) & Chr(10)
            objText.BackColor = &HFF&
    Else
            objText.BackColor = &HFFFFFF
    End If
    
Exit_IsInRange:
   Exit Sub
IsInRange_Error:
    
    #If gnDebug Then
        Stop
        Resume
    #End If

    msgbox Err.Description & ":" & Err.Number
    Resume Exit_IsInRange
    
End Sub

Purpose: The user input is validated to be a field value for an Access 2000 table. Usually, a bound combo box is used to select a valid field value. However, you may have a need to check for valid database matching.
Public Sub IsInTable(objText As TextBox, errormsg As String, tablename As String, _
 fieldname As String, datetype As String)
    On Error GoTo IsInTable_Error
    Dim sValue
    Dim rs
    Dim sql
    Dim bFound

    objText.SetFocus
    sValue = objText
    If datetype = "STRING" Then
        sql = "select * from " & tablename & " where ucase(" & fieldname & ")=" & IsNVLString(UCase(sValue))
    ElseIf datetype = "DATE" Then
        Call IsaDate(objText, errormsg)
        sql = "select * from " & tablename & " where " & fieldname & "=" & IsNVLDate(sValue)
    ElseIf datetype = "NUMERIC" Then
        Call IsaNumber(objText, errormsg)
        sql = "select * from " & tablename & " where " & fieldname & "=" & IsNVLNumber(sValue)
    End If
    
    Set rs = CurrentDB().OpenRecordset(sql)
    bFound = False
    If Not rs.EOF Then
        bFound = True
    End If
    rs.Close
    Set rs = Nothing
    
    If bFound = False Then
        errormsg = errormsg & objText.name & ":" & objText.Text & " is not in table " & Chr(13) & Chr(10)
            objText.BackColor = &HFF&
    Else
            objText.BackColor = &HFFFFFF
    End If
    
Exit_IsInTable:
   Exit Sub
IsInTable_Error:
    
    #If gnDebug Then
        Stop
        Resume
    #End If

    msgbox Err.Description & ":" & Err.Number
    Resume Exit_IsInTable
    
End Sub

Purpose: Returns a single quote enclosed string, with embedded single quotes being converted into double single quotes. If the parameter is an empty string, than return a null.
Function IsNVLString(parameter)

    On Error GoTo IsNVLString_Error
    
    If IsNull(parameter) Or parameter = "" Then
        IsNVLString = "Null"
        GoTo Exit_IsNVLString
    End If
            
    IsNVLString = "'" & FixApostrophy(parameter) & "'"
    
    
Exit_IsNVLString:
   Exit Function
IsNVLString_Error:
    
    #If gnDebug Then
        Stop
        Resume
    #End If

    msgbox Err.Description & ":" & Err.Number
    Resume Exit_IsNVLString
    
End Function

Purpose: Return either a number or a null.
Function IsNVLNumber(parameter)

    On Error GoTo IsNVLNumber_Error
    
    If IsNull(parameter) Or parameter = "" Then
        IsNVLNumber = "Null"
        GoTo Exit_IsNVLNumber
    End If
            
    IsNVLString = parameter
    
    
Exit_IsNVLNumber:
   Exit Function
IsNVLNumber_Error:
    
    #If gnDebug Then
        Stop
        Resume
    #End If

    msgbox Err.Description & ":" & Err.Number
    Resume Exit_IsNVLNumber
    
End Function

Purpose: Return a # enclosed string if the user data is a date type or null if the parameter is empty.
Function IsNVLDate(parameter)

    On Error GoTo IsNVLDate_Error
    
    If IsNull(parameter) Or parameter = "" Then
        IsNVLDate = "Null"
        GoTo Exit_IsNVLDate
    End If
            
    IsNVLDate = "#" & parameter & "#"
    
    
Exit_IsNVLNumber:
   Exit Function
IsNVLNumber_Error:
    
    #If gnDebug Then
        Stop
        Resume
    #End If

    msgbox Err.Description & ":" & Err.Number
    Resume Exit_IsNVLNumber
    
End Function

Purpose: Replace each single quote with two single quotes.
Public Function FixApostrophy(ByVal sSQL As String) 

Dim sFront$, sBack$, nParamLen%
Dim sPhrase As String
Dim wLength As Integer
Dim i As Integer
On Error GoTo FixApostrophy_Error

    wLength = Len(sSQL)
    For i = 1 To wLength
        If Mid$(sSQL, i, 1) = "'" Then
            sPhrase = sPhrase + "''"
        Else
            sPhrase = sPhrase + Mid$(sSQL, i, 1)
        End If
    Next
    FixApostrophy = sPhrase
    
Exit_FixApostrophy:

Exit Function
FixApostrophy_Error:
    #If gnDebug Then
        Stop
        Resume
    #End If
    'Standard error handling statement
    msgbox Err.Description & ":" & Err.Number
    Resume Exit_FixApostrophy

End Function


Next Access "How To": Access Tree View and List Box Row Source

Back to Access 2000 How To's Series Home

 

Access Tree View and List Box Row Source

How to Load a Category Tree and Listbox RowSource Type "Field Value" Property


Overview

In this Access 2000 article, I will show you how to create a category tree and load it into a listbox. I will demonstrate the steps to create a static value list and associate it with a listbox. The algorithm can be easily ported to a treeview control or to HTML for an active server page.

 

Creating the Category Table

  1. Create a table called "category"
  2. Include the following fields:
    categoryid: autonumber
    parentid: number
    title: text
  3. Add the following data values to the category table:

  Parent ID Category ID (autonumber) Title
  0 1 Hardware
  0 2 Computers
  2 3 DeskTop
  2 4 Server


Add a Listbox

  1. Add a listbox to your form called "lbxCategory"
  2. Insert the following VB code to the form

 

  1. Static values can be added to a listbox
  2. Each field value is separated with a ";" delimiter
  3. Rows are determined by the listboxes columncount
  4. When the ColumnHeads property is set to true, the first row becomes the column headings
  5. The RowSourceType settings tell the listbox control whether the data is dynamically bound to a data table or static text. In this case, the listbox control is bound to static text
Option Explicit
Option Compare Database
Dim sFieldValues As String

Private Sub Form_Load()
  
    'Heading Column titles
    sFieldValues = "Parent Id;Category Id, Title;"
    Call LoadCategory(0)
    lbxCategory.RowSourceType = "Value List"
    lbxCategory.RowSource = sFieldValues
    lbxCategory.ColumnCount = 3
    lbxCategory.ColumnHeads = True
    
    
End Sub
  1. The Load Category procedure starts with the root parent id being "0"
  2. Each category is recursively checked to see if it has children.
    Children are concatenated to the sFieldValues string
  3. Each value list entry is concatenated to the sFieldValue string embedding the parentid, categoryid, and title information.
Private Sub LoadCategory(sId)
    Dim rs As Object
    Dim sql
    Dim sNewId
    
    'Check for the bottom of the tree
    If IsNull(sId) Then
        Exit Sub
    End If
    
    sql = "select * from category where parentid=" & sId
    Set rs = CurrentDb().OpenRecordset(sql)
    
    Do While Not rs.EOF
        sFieldValues = sFieldValues & sId & ";" & rs("categoryid") & ";" & rs("title") & ";"
        'Recursive call to check for children
        Call LoadCategory(rs("categoryid"))
        rs.MoveNext
    Loop
    
    If Not rs Is Nothing Then
        rs.Close
    End If
    Set rs = Nothing
    
End Sub

Active Server Page (Tree View)

 

  1. Generate a Category tree in HTML.


Next Access "How To": Calculating Work Days

Back to Access 2000 How To's Series Home

 

Calculating Work Days

This article explains how to use Access 2000 date functions when determining 1) the number of work days representing an interval between two dates, and 2) the projected end working date calculated from a start date and number of hours worked.

The user enters in a start date and actual hours as parameters in the GetEndWorkDay function. The function calculates the date in the future the work should be completed. So, if today is 12/2/2002 and the actual hours is 12, the projected end date would be 12/3/2002.

The second part uses the GetNumberOfWorkDays function to determine what percent of the estimated time actual time represents. Estimated work days is the time interval to complete a task based on a start and end date. Comparing estimated to actual time, we can provide the percent of work completed both under or over allocated percents.

 

GetEndWorkDay Function

 

  Public Function GetEndWorkDay(sStartDate, sHours)
    Dim iHoursToDays
    Dim iCount
    Dim bFlag
    Dim sEndDate
    Dim sCheckDate
    Dim iFoundCount
    Dim sDay
  
      'Assume an eight hour day
      iHoursToDays = round(sHours / 8,0)
      sEndDate = sStartDate
      If iHoursToDays > 1 Then
        bFlag = False
        iCount = 0
        iFoundCount = 0
        Do While bFlag = False
          iCount = iCount + 1
          sCheckDate = DateAdd("d", iCount, sStartDate)
          sDay = Weekday(sCheckDate)
          If sDay <> 1 And sDay <> 7 Then
            sEndDate = sCheckDate
            iFoundCount = iFoundCount + 1
          End If
          If iFoundCount >= iHoursToDays Then
            Exit Do
          End If
        Loop
      End If
      GetEndWorkDay = sEndDate
  End Function
  1. DateAdd returns a date to which a specific time interval has been added. In this sample the interval is "Day".

         List of Interval Settings:
                yyyy = year
                q = quarter
                m = month
                y = day of year
                d = day
                w = weekday
                ww = week
                h = hour
                n = minute
                s = second

     

  2. Increment through a range of possible work days to find the end work date. The number of work days is determined by dividing the hours by eight. This assumes an eight hour work day. Ignore saturday and sunday as work days. Once the number of found work days equals the work day interval, stop and return the date as the final work date.

     


GetNumberofWorkDays Function

  Public Function GetNumberOfWorkDays(sStartDate, sEndDate)
    Dim iDays
    Dim iWorkDays
    Dim sDay
    Dim i
    
    iDays = DateDiff("d", sStartDate, sEndDate)
   
    iWorkDays = 0
   
    For i = 0 To iDays
      'First day of the week is sunday
      sDay = Weekday(DateAdd("d", i, sStartDate))
      If sDay <> 1 And sDay <> 7 Then
        iWorkDays = iWorkDays + 1
      End If
    Next
    GetNumberOfWorkDays = iWorkDays
  End Function
  1. DateDiff specifics a number of time intervals between two dates.
  2. DateDiff (interal,date1,date2,firstdayofweek,firstweekofyear)

         Interval
                yyyy = year
                q = quarter
                m = month
                y = day of year
                d = day
                w = weekday
                ww = week
                h = hour
                n = minute
                s = second

         date1 and date2 are used to calculate the interval
         firstdayofweek is sunday unless specified
         firstweekofyear is jan 1 unless specified

     

  3. Weekday returns a number representing the day of the week.

         Return values are:
                Sunday = 1
                Monday = 2
                Tuesday = 3
                Wednesday = 4
                Thursday = 5
                Friday = 6
                Saturday = 7

     

  4. First determine the number of days between the two dates. Calculating the number of work days is done by not adding Saturday and Sunday dates. A query extract from a table containing all the holidays for the year could also be added for increased accuracy.

     


Next Access "How To": Controlling Report Data

Back to Access 2000 How To's Series Home

 

Controlling Report Data

It is often the case where a developer will need to create an Access 2000 reports application capable of data selection -- for example, all transactions for a member. The data is input as parameters within a form and then passed to the report as query criteria. This is the simplest method to control report output resulting from user-selected data criteria.

 

Table Transactions
Fields and Types
MemberId Number
PostedDate PostedDater
Amount Double
Code Number


Create a query called qryTransactions
    Include memberid, posteddate, amount, and code in the query field list

Create a columar report called rptTransactions
    Step 1: Use the report Wizard
    Step 2: Select qryTransactions as the data source
    Step 3: Select all fields
    Step 4: Group on memberid
    Step 5: Select Tabular type
    Step 6: Name the report rptTransactions

Create a form called frmReportParameters
    Step 1: Design View
    Step 2: Add a text field called txtMemberId
    Step 3: Change the label to read "Member Id"
    Step 4: Add a button named "cmdLaunchTransactionReport" and labeled "Launch Report"
    Step 5: Save the form as frmReportParameters

Launch the Code
    Step 1: Make sure you're in form design
    Step 2: Press the code button
    Step 3: Add a Click event for the cmdLaunchTransactionReport button


The following code will limit data selection to records matching a specific member id. The report will open in preview mode. Only transactions with a specific member id will be displayed.

  Option Explicit
  Option Compare Database
  Private Sub cmdLaunchTransactionReport_Click()
  On Error GoTo Err_cmdLaunchTransactionReport_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim sCriteria
    sCriteria = "[MemberId]=" & txtMemberId
    stDocName = "rptTransactions"
    DoCmd.OpenReport stDocName, acViewPreview, , sCriteria, acWindowNormal
  Exit_cmdLaunchTransactionReport_Click:
    Exit Sub
  Err_cmdLaunchTransactionReport_Click:
    msgbox Err.Description
    Resume Exit_cmdLaunchTransactionReport_Click
  End Sub

Run the Form
    Step 1: Press F5
    Step 2: Enter in a Member Id
    Step 3: Press the Launch Report Button
    Step 4: Review your report


Back to Access 2000 How To's Series Home

 

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved