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