limit date formula as variable

C

ClaraBarton

Registered User.
Local time
Today, 01:49
Joined
Oct 14, 2019
Messages
665
I have a combobox that I want to limit dates in a listbox:
Code: 
Function DateCalculation(IDate As Integer)

    Select Case IDate
        Case 48 'All Dates
            [CkDate]
        Case 49 'This Month
            Year([CkDate]) = Year(Date) And Month([CkDate]) = Month(Date)
        Case 50 'Last Month
            [CkDate] = Date - 30
        Case 51 'Last 30 Days
            [CkDate] Between(Date - 30) And Date
        Case 52 'Last 60 Days
            [CkDate] Between(Date - 60) And Date
        Case 53 'Last 90 Days
            [CkDate] Between(Date - 90) And Date
        Case 54 'Last 12 Months
            
        Case 55 'This Quarter
            Year([CkDate]) = Year(Date) And DatePart("q", [CkDate]) = DatePart("q", Date)
        Case 56 'Last Quarter
            IIf(DatePart("q", [CkDate]) = 1, Year([CkDate]) = (Year(Date)-1) And DatePart("q", [CkDate]) = 4, _
            Year([CkDate]) = Year(Date) And DatePart("q", [CkDate]) = (DatePart("q", Date)-1))
        Case 57 'This Year
            Year([CkDate]) = Year(Date)
        Case 58 'Last Year
            Year([CkDate]) = Year(Date) - 1
   End Select
    
End Function
The listbox has a rowsource:
Code: 
Dim ID As Long
Dim IDate As Variant
ID = lngInfoXchg

IDate = DateCalculation cboDate.Value

        Me.lstUsage.RowSource = "SELECT TransactionID, fCategoryID, CkDate, Num, Category, Memo, CAmount" & _
                                " FROM tblNames RIGHT JOIN (tblTransactions LEFT JOIN (tblCategory" & _
                                " RIGHT JOIN tblCheckCat ON tblCategory.CategoryID = tblCheckCat.fCategoryID)" & _
                                " ON tblTransactions.TransactionID = tblCheckCat.fTransactionID)" & _
                                " ON tblNames.NameID = tblTransactions.fNameID" & _
                                " WHERE NameID = " & ID & _
                                " AND " & IDate & _
                                " ORDER BY CkDate DESC"
Obviously this thing does not work. To start with the function does not compile. It stops at the first Between. Can you give me a little direction?
 
For starters, your function is not returning any value nor is it modifying the argument referenced by the calling procedure. Furthermore, I think you meant to construct Strings in your function since you're building a SQL statement.
 
Yes, you are right. I want the formula... So it should be a string that I'm adding to a string? And then want to calculate? And it should be a sub and not a function?
 
See if this helps at all.

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...
www.access-programmers.co.uk

Edit: I am intrigued now, what are the other 47 options?
 
Last edited:
Make fun of my formulae but they all work in queries. :p I just can't figure how to get them into a sql where statement (rowsource)
 
Not making fun, genuinely curious as to what the other 47 are.?
You need to make all those entries actual literal strings.
I do not think the first one is going to work as is now.
Debug.print the whole sql statement before even trying to use it.
I also would not name a Variant with an i prefix. To me, that would be an Integer. I would have made it a string anyway, as I know it will be a string, always. Never Null.
 
Disregarding the issues pointed out before, here's all the issues I see with your date logic:

Case 48 'All Dates
[CkDate]

The above will actually work but not for the reasons you think. It's going to resolve to "...WHERE 5/27/2025..." and whenever you don't have an actual comparsion (using =, >, <, etc.) anything that is not 0 will be true and anything that is 0 will be false. So since 5/7/2025 doesn't equal 0 all records will pass through. A better way would to just not do anything in Case 48.

Case 50 'Last Month
[CkDate] = Date - 30

Not every month has 30 days. Sometimes you are going to include records that actually occured this month (May 31 - 30 days = May 1). Sometimes its going to go back 2 months (March 2 - 30 days = January 31)
 
My guess it is something like this.
Code: 
Public Enum DateRange
 drAllDates = 48
 drThisMonth = 49
 drLastMonth = 50
 drLast30Days = 51
 
 drThisYear = 57
 drLastYear = 58
End Enum


Function GetDateRange(DateFieldValue As Date, DateFieldName As String, RangeType As DateRange) As String

    Select Case RangeType
        Case drAllDates 'All Dates
           GetDateRange = " 1 = 1"
        Case drThisMonth
            GetDateRange = "Year([" & DateFieldName & "]) = " & Year(Date) & " And Month([" & DateFieldName & "]) = " & Month(DateFieldValue)
        Case drLastMonth
            'I assume you mean from 1st day of last month to last day of last month
            Dim lastOfMonth As Date
            Dim firstofmonth As Date
            lastOfMonth = DateSerial(Year(Date), Month(Date), 0)
            firstofmonth = DateSerial(Year(lastOfMonth), Month(lastOfMonth), 1)
            GetDateRange = "[" & DateFieldName & "] between #" & Format(firstofmonth, "mm/dd/yyyy") & "# AND #" & Format(lastOfMonth, "mm/dd/yyyy") & "#"
       '     [ckDate] Between(Date - 30) And Date
'        Case 52 'Last 60 Days
'            [CkDate] Between(Date - 60) And Date
'        Case 53 'Last 90 Days
'            [CkDate] Between(Date - 90) And Date
'        Case 54 'Last 12 Months
'
'        Case 55 'This Quarter
'            Year([CkDate]) = Year(Date) And DatePart("q", [CkDate]) = DatePart("q", Date)
'        Case 56 'Last Quarter
'            IIf(DatePart("q", [CkDate]) = 1, Year([CkDate]) = (Year(Date)-1) And DatePart("q", [CkDate]) = 4, _
'            Year([CkDate]) = Year(Date) And DatePart("q", [CkDate]) = (DatePart("q", Date)-1))
        Case drThisYear
            GetDateRange = "([" & DateFieldName & "]) = " & Year(DateFieldValue)
        Case drLastYear
            GetDateRange = "([" & DateFieldName & "]) = " & Year(Date) - 1
       End Select
   
End Function

Public Sub testRange()
  Dim strSql As String
  Dim strRange As String
  Dim strOrderBy As String
  Dim ckDate As Date
 
  ckDate = #5/1/2025#
  strOrderBy = "  ORDER BY CkDate DESC"
  strSql = "select .... WHERE NameID = 5 AND " & strRange
 
  Debug.Print strSql & GetDateRange(ckDate, "CkDate", drAllDates) & strOrderBy
  Debug.Print strSql & GetDateRange(ckDate, "CkDate", drLastMonth) & strOrderBy
  Debug.Print strSql & GetDateRange(ckDate, "CkDate", drThisMonth) & strOrderBy
  Debug.Print strSql & GetDateRange(ckDate, "CkDate", drThisYear) & strOrderBy
  Debug.Print strSql & GetDateRange(ckDate, "CkDate", drLastYear) & strOrderBy
End Sub

With my test I get the following
Code: 
select .... WHERE NameID = 5 AND  1 = 1  ORDER BY CkDate DESC
select .... WHERE NameID = 5 AND [CkDate] between #04/01/2025# AND #04/30/2025#  ORDER BY CkDate DESC
select .... WHERE NameID = 5 AND Year([CkDate]) = 2025 And Month([CkDate]) = 5  ORDER BY CkDate DESC
select .... WHERE NameID = 5 AND ([CkDate]) = 2025  ORDER BY CkDate DESC
select .... WHERE NameID = 5 AND ([CkDate]) = 2024  ORDER BY CkDate DESC
 
I see I forgot to add "Year" to the string in
Code: 
Case drThisYear
            GetDateRange = "([" & DateFieldName & "]) = " & Year(DateFieldValue)
        Case drLastYear
            GetDateRange = "([" & DateFieldName & "]) = " & Year(Date) - 1
should read
Code: 
Case drThisYear
            GetDateRange = " Year([" & DateFieldName & "]) = " & Year(DateFieldValue)
        Case drLastYear
            GetDateRange = " Year([" & DateFieldName & "]) = " & Year(Date) - 1
 
you may also try this.
Year() or Month() functions will not fully utilize your Indexes.
Code: 
Function DateCalculation(ByVal IDate As Integer, ByVal CkDate As String) As String
    Dim ret As String
    Dim dt1 As Date, dt2 As Date
    Select Case IDate
        Case 48 'All Dates
            ret = "1=1"
        Case 49 'This Month
            ret = "[" & CkDate & "]>=#" & Format$(DateSerial(Year(Date), Month(Date), 1), "mm/dd/yyyy") & "# And " & _
                "[" & CkDate & "]<=#" & Format$(DateSerial(Year(Date), Month(Date) + 1, 0), "mm/dd/yyyy") & "#"
        Case 50 'Last Month
            ret = "[" & CkDate & "]>=#" & Format$(DateSerial(Year(Date), Month(Date) - 1, 1), "mm/dd/yyyy") & "# And " & _
                "[" & CkDate & "]<=#" & Format$(DateSerial(Year(Date), Month(Date), 0), "mm/dd/yyyy") & "#"
        Case 51 'Last 30 Days
            ret = "[" & CkDate & "] Between #" & Format$(Date - 30, "mm/dd/yyyy") & "# And #" & Format$(Date, "mm/dd/yyyy") & "#"
        Case 52 'Last 60 Days
            ret = "[" & CkDate & "] Between #" & Format$(Date - 60, "mm/dd/yyyy") & "# And #" & Format$(Date, "mm/dd/yyyy") & "#"
        Case 53 'Last 90 Days
            ret = "[" & CkDate & "] Between #" & Format$(Date - 90, "mm/dd/yyyy") & "# And #" & Format$(Date, "mm/dd/yyyy") & "#"
        Case 54 'Last 12 Months
            ret = "[" & CkDate & "] Between #" & Format$(DateAdd("m", -12, Date), "mm/dd/yyyy") & "# And #" & Format$(Date, "mm/dd/yyyy") & "#"
            
        Case 55 'This Quarter
            Call GetQuarterDates(DatePart("q", Date), dt1, dt2)
            ret = "[" & CkDate & "] Between #" & Format$(dt1, "mm/dd/yyyy") & "# And #" & Format$(dt2, "mm/dd/yyyy") & "#"
            
        Case 56 'Last Quarter
            Call DatesOfPreviousQuarter(Date, dt1, dt2)
            ret = "[" & CkDate & "] Between #" & Format$(dt1, "mm/dd/yyyy") & "# And #" & Format$(dt2, "mm/dd/yyyy") & "#"
        
        Case 57 'This Year
            ret = "[" & CkDate & "] Between #" & Format$(DateSerial(Year(Date), 1, 1), "mm/dd/yyyy") & "# And #" & Format$(DateSerial(Year(Date), 12, 31), "mm/dd/yyyy") & "#"
        Case 58 'Last Year
            ret = "[" & CkDate & "] Between #" & Format$(DateSerial(Year(Date) - 1, 1, 1), "mm/dd/yyyy") & "# And #" & Format$(DateSerial(Year(Date) - 1, 12, 31), "mm/dd/yyyy") & "#"
   End Select
    DateCalculation = ret
End Function

Function GetQuarterDates(ByVal qtr As Integer, ByRef FirstDay As Date, ByRef LastDay As Date)
    Dim QuarterStartMonth As Integer
    Dim YearPart As Integer
    YearPart = Year(Date)
    Select Case qtr
        Case 1
            QuarterStartMonth = 1
        Case 2
            QuarterStartMonth = 4
        Case 3
            QuarterStartMonth = 7
        Case 4
            QuarterStartMonth = 10
    End Select

    ' First day of quarter
    FirstDay = DateSerial(YearPart, QuarterStartMonth, 1)
    ' Last day of quarter
    LastDay = DateSerial(YearPart, QuarterStartMonth + 3, 0)
End Function

Function DatesOfPreviousQuarter(ByVal dt As Date, ByRef FirstDay As Date, ByRef LastDay As Date)

    Dim PrevQuarterMonth As Integer
    Dim PrevQuarterYear As Integer
    Dim CurrentQuarter As Integer

    CurrentQuarter = ((Month(dt) - 1) \ 3) + 1 ' Current quarter: 1 to 4
    If CurrentQuarter = 1 Then
        PrevQuarterMonth = 10
        PrevQuarterYear = Year(dt) - 1
    Else
        PrevQuarterMonth = ((CurrentQuarter - 2) * 3) + 1
        PrevQuarterYear = Year(dt)
    End If

    FirstDay = DateSerial(PrevQuarterYear, PrevQuarterMonth, 1)
    LastDay = DateSerial(PrevQuarterYear, PrevQuarterMonth + 3, 0)
End Function

Code: 
Dim ID As Long
Dim IDate As String
ID = lngInfoXchg

IDate = DateCalculation cboDate.Value, "ChkDate"

        Me.lstUsage.RowSource = "SELECT TransactionID, fCategoryID, CkDate, Num, Category, Memo, CAmount" & _
                                " FROM tblNames RIGHT JOIN (tblTransactions LEFT JOIN (tblCategory" & _
                                " RIGHT JOIN tblCheckCat ON tblCategory.CategoryID = tblCheckCat.fCategoryID)" & _
                                " ON tblTransactions.TransactionID = tblCheckCat.fTransactionID)" & _
                                " ON tblNames.NameID = tblTransactions.fNameID" & _
                                " WHERE NameID = " & ID & _
                                " AND " & IDate & _
                                " ORDER BY CkDate DESC"
 
Last edited:
You must log in or register to reply here.

Similar threads

A
Change chart in subform
Replies
2
Views
559
AndyMD
A
B
Dates and Invalid Use of Null
Replies
8
Views
1,095
Pat Hartman
P
isladogs
Access Europe User Group - Wed 8 Jan: Developing COM Add-Ins for Access (Philipp Stiefel)
Replies
3
Views
381
isladogs
isladogs
isladogs
Access Europe - Wed 7 May: Update, update, the company is in a state (Karl Donaubauer)
Replies
2
Views
332
isladogs
isladogs
K
Query to to exclude records with a date within last 4 months, and show records where most recent date is older than that
Replies
9
Views
664
kevin_maher
K

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom