Category Archives: Code Snippets

Return Last Date Of Month

Got this from a post by Allan Bunch (RuralGuy) on Access World Forums. Public Function ReturnLastDateOfMonth() ReturnLastDateOfMonth = DateSerial(Year(#2/1/2008#), Month(#2/1/2008#) + 1, 0) End Function

Return All ODBC Linked Table and View Names to Excel

Function SendToExcelODBCTableNames() Dim objXL As Object Dim xlWB As Object Dim xlWS As Object Dim intCount As Integer Dim rst As Dao.Recordset Dim strName As String Dim strSQL As String Dim fld As Dao.Field strName = Left(CurrentProject.Name, Len(CurrentProject.Name) – 4) … Continue reading →

Open Another Database

This code opens another instance of Access and the database that the full path and name is sent to the function. Function OpenNewDatabase(strDatabase As String) Dim appAcc As Access.Application Set appAcc = New Access.Application appAcc.Visible = True appAcc.OpenCurrentDatabase (strDatabase) appAcc.UserControl … Continue reading →

Lock Select Controls

There are times where you might want to lock select controls so that you can still use something on the form. Setting the AllowEdits property to NO will lock all of the controls that could be edited, including (for example) … Continue reading →

How To Check If A Form Is Open (loaded)

‘ Substitute the real form name in the code where it says FORMNAMEHERE. Currentproject.AllForms(“FORMNAMEHERE”).IsLoaded ‘ So, you can use this premade function if you want: Function IsLoaded(strFormName As String) As Boolean IsLoaded = CurrentProject.AllForms(strFormName).IsLoaded End Function ‘ And you can … Continue reading →

Get Week Number of Date in Month (1-5)

‘ Paste into a Standard Module ‘ Example: ‘ GetMonthWeek(#7/29/2012#) returns 5 Function GetMonthWeek(dteDate As Date) As Integer Dim intAdjuster As Integer If Day(dteDate) Mod 7 <> 0 Then intAdjuster = 1 End If GetMonthWeek = (Day(dteDate) \ 7) + … Continue reading →

Get Month Number From Month Name

Here are two ways of getting that information. The second is more compact than the first. Function RetMonthNum(strMonthName As String) As Integer Select Case strMonthName Case “January”, “Jan” RetMonthNum = 1 Case “Februrary”, “Feb” RetMonthNum = 2 Case “March”, “Mar” … Continue reading →

Find Previous / Next Sunday

Code courtesy of Bob Askew (raskew on Access World Forums). Find previous Sunday Function GetPrevSunday(dteDate As Date) As Date GetPrevSunday = DateAdd(“d”, -Weekday(dteDate) + 1, dteDate) End Function Find next Sunday Function GetNextSunday(dteDate As Date) As Date GetNextSunday = DateAdd(“d”, -Weekday(dteDate) … Continue reading →

Find Next/Previous Specified Day

This code was generously donated by Bob Askew (raskew) from Access World Forums and modified by Bob Larson to be able to look either forward or back. NOTE: If you use this in a QUERY or Control Source, you have … Continue reading →

Find Earliest Date From Input

Function ReturnEarliestDate(strInput As String, strDelimiter As String) As Date ‘ Usage: ‘ In a query pass the dates in as a string with whatever delimiter you want. ‘ For example, with a pipe it would be: ‘ ReturnEarliestDate([DateField1] & “|” … Continue reading →