bad bracketing (1 Viewer)

[The_Doc_Man]

You mean Table1.D Doc?

Darn, my lysdexia kicked in again!

 

[Gasman]

Well you can if it is only one table, but that function just returns criteria to be used elsewhere?, so that elsewhere is where you would use any table name, surely?

This was my line of thought.

? "Select * from tblpressure where " & getdaterange("PressureDateTime",51)
Select * from tblpressure where [PressureDateTime] Between #11/12/2025# And #10/01/2026#

 

[Gasman]

Function GetDateRange(DateField As String, RangeType As Integer) As String
    'Last Month
Dim firstmonth As Date
Dim lastmonth As Date
firstmonth = DateSerial(Year(Date), Month(Date), 0)
lastmonth = DateSerial(Year(firstmonth), Month(firstmonth) - 12, 0)
    'Quarterly
Dim begMo As Integer
Dim fdoQtr As Date
Dim ldoQtr As Date
Dim fdoPrevQtr As Date
Dim ldoPrevQtr As Date
begMo = (Month(Date) - 1) \ 3 * 3 + 1
fdoQtr = DateSerial(Year(Date), begMo, 1)
ldoQtr = DateAdd("q", 1, fdoQtr)
fdoPrevQtr = DateAdd("q", -1, fdoQtr)
ldoPrevQtr = DateAdd("q", 1, fdoPrevQtr) - 1
    'Custom
Dim dtstart As Date
Dim dtend As Date
   
    Select Case RangeType
        Case 48 'All Dates
            GetDateRange = "1=1"
        Case 49 'This Month
            GetDateRange = "Year([" & DateField & "]) = " & Year(Date) & " And Month([" & DateField & "]) = " & Month(Date)
        Case 50 'Last Month
            GetDateRange = "[" & DateField & "] between #" & Format(firstmonth, "mm/dd/yyyy") & "# AND #" & Format(lastmonth, "mm/dd/yyyy") & "#"
        Case 51 'Last 30 Days
            GetDateRange = "[" & DateField & "] Between #" & (Date - 30) & "# And #" & Date & "#"
        Case 52 'Last 60 Days
            GetDateRange = "[" & DateField & "] Between #" & (Date - 60) & "# And #" & Date & "#"
        Case 53 'Last 90 Days
            GetDateRange = "[" & DateField & "] Between #" & (Date - 90) & "# And #" & Date & "#"
        Case 54 'Last 12 Months
            GetDateRange = "[" & DateField & "] between #" & Format(lastmonth, "mm/dd/yyyy") & "# AND #" & Format(firstmonth, "mm/dd/yyyy") & "#"
        Case 55 'This Quarter
            GetDateRange = "[" & DateField & "] between #" & Format(fdoQtr, "mm/dd/yyyy") & "# AND #" & Format(ldoQtr, "mm/dd/yyyy") & "#"
        Case 56 'Last Quarter
             GetDateRange = "[" & DateField & "] between #" & Format(fdoPrevQtr, "mm/dd/yyyy") & "# AND #" & Format(ldoPrevQtr, "mm/dd/yyyy") & "#"
        Case 57 'This Year
            GetDateRange = "Year([" & DateField & "]) = " & Year(Date)
        Case 58 'Last Year
            GetDateRange = "Year([" & DateField & "]) = " & Year(Date) - 1
        Case 59 'Custom
            DoCmd.OpenForm "popDate", _
                WindowMode:=acDialog
                If CurrentProject.AllForms!popDate.IsLoaded Then
                    dtstart = Nz(Forms!popDate![begDate])
                    dtend = Nz(Forms!popDate![endDate])
                    DoCmd.Close acForm, "popDate"
                End If
            GetDateRange = "[" & DateField & "] Between #" & dtstart & "# And #" & dtend & "#"
         End Select
 End Function

Having been shown enums now, you might want to consider those in the future?