Category: VBA Functions

VBA: How to Test if a String starts with certain letters There are many ways that we can test if the string which begin or start with the certain string or letter. Some time we want to perform the action only on certain field or records that begin or start with the specified letter. For example, we want to know how many customers have first name start with letter “P”. In this How To, I will show introduce the Like comparison operator, InStr, and Left built-in function. Like comparison operator…

How to Find the Average from Numbers in a List Per previous How To, I already demonstrated how to create the total (sum) of numbers (checks). In this How To, I will use the previous form to show how to find the average of numbers (checks). The Average Formula (Mean or Arithmetic Mean) is the sum of all of the numbers in a list divided by the numbers of items in that list. For example, the average of the numbers 2,3,7 is 4 since 2+3+7 =12 and 12 divided by…

Use DCount() Function in MS Access DCount function is a built-in function to get the number of records from the set of records or data from table, query, SQL, or Recordset. We can use the DCount function in Visual Basic and macro. DCount Syntax DCount(Expression, Domain, Criteria [optional]) Expression identifies the field that you want to count records. It can be a string expression identifying a filed in a table or query. Domain is a set of records. It can be a table name or a query name for a…

***Function DaysInMonth() *** This function will generate a number of days from the input month. For example, if 6/4/2016 is input into the function like DaysInMonth(6/4/2016) the result will be “30” as June has 30 days in the month. The LeapYear function also is used in the DaysInMonth function. VB Code: How to Use It: You can put the above functions on current form or in the Module. If you want to know the number of days of the month from the input date the function will be like: Input…

Function Check If Form is Loaded VBA Code: Public Function IsFormLoaded(strFrmName As String)As Boolean ‘ Determines if a form is loaded. Const conFormDesign = 0 Dim intNum As Integer IsFormLoaded = False For intX = 0 To Forms.Count – 1 If Forms(intNum).FormName = strFrmName Then If Forms(intNum).CurrentView <> conFormDesign Then IsFormLoaded = True Exit Function ‘ Quit function once form has been found. End If End If Next End Function How to Use It: Put the VBA code above in the Module and call it on the click button anywhere on…

This function is a utility function that used with other function or command that will take action to table if that table exists. If we taken an action to the table that does not exit then we will get an error message. VBA Code Public Function IsTableExists(ByVal strTableName As String) As Boolean On Error Resume Next IsTableExists = IsObject(CurrentDb.TableDefs(strTableName)) End Function How to Use It Use this function to test if a table you referred to exists then take action. The example below is used under the the click button…

Function Clear the Selected Items from the List Box VBA Code: Public Function ClearSelectedItem(lst As ListBox) As Boolean Dim varItem As Variant If lst.MultiSelect = 0 Then lst = Null Else For Each varItem In lst.ItemsSelected lst.Selected(varItem) = False Next End If End Function How to Use It: Put the VBA code above in the Module and call it on the click button anywhere on your Access file where you want to unselected items from the listbox. Example: if you have a list box names “lstCustomerType” then put…