bad bracketing

ClaraBarton · 2026-01-08T20:44:35+0000

I have a function that requires a field name as a string variable.

Code:

Function GetDateRange(DateField As String, RangeType As Integer) As String

I created an allpurpose query that contains a field ckDate.
If I create

a variable string "qryAllPurpose.CkDate" it returns [qryAllPurpose.CkDate]
string "CkDate" is unknown
qryAllPurpose.[CkDate] returns [qryAllPurpose.[CkDate]] and then yells illegal bracketing
Currentdb.querydefs("qryAllPurpose").CkDate is not possible

I think this is basic knowledge that EVERYONE knows but me.
Should it be a field or a string?
How do I do this?

The_Doc_Man · 2026-01-08T20:52:03+0000

From the function definition, what you pass in must be a string. From VBA specifications, you can pass in that string from a constant, a variable, a function that returns a string, or a field. But in what context are you calling that function? A query or a VBA call?

ClaraBarton · 2026-01-08T21:10:37+0000

a vba call. It goes in a where string for a listbox row source.

Ken Sheridan · 2026-01-08T21:42:35+0000

ClaraBarton said:
a vba call. It goes in a where string for a listbox row source.

In that case you'd pass it as a string expresssion and then concatenate its value into a string expression, which you'd assign to the control's RowSource property e.g. with the following function in a form's module:

Code:

Private GetRowSource(strDateColumn AS String, strTable AS String) As String

    GetRowSource = "SELECT " & strDateColumn & _
        " FROM " & strTable & _
        " ORDER BY " & strDateColumn
     
End Function

In the form's module call the function like this:

Code:

Me.lstDates.RowSource = GetRowSource("calDate", "Calendar")

In reality you'd never do the above of course as you could simply set the control's RowSource in its properties sheet. As regards your own function I'd need to see the full code for the function and the 'all purpose' query's SQL statement to comment further.

ClaraBarton · 2026-01-08T21:57:29+0000

Code:

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

ClaraBarton · 2026-01-08T22:00:31+0000

using the string "tblTransaction.Ckdate" has worked for a long time but I need it for a combination of tables so I pulled together a generic query. I guess I've been away from it for awhile and it is not cooperating with my logic.

Ken Sheridan · 2026-01-08T22:20:36+0000

You'd pass the column name as a string into that function. No need to qualify it with a table or query name unless it would otherwise be ambiguous. You'd then concatenate the return value of the function into a string expression, e.g.

Code:

Me.YourListBoxName.Rowsource = "SELECT ckDate" & _
    " FROM qryAllPurpose" & _ 
    " WHERE " & GetDateRange("ckDate", 49) & _
    " ORDER BY ckDate"

I'm shutting down for the night now.

RonPaii · 2026-01-08T22:25:39+0000

ClaraBarton said:

Code:

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

Change all your cases from
"[" & DateField & "] ..."
to
& DateField &

The brackets are not needed unless DateField has spaces or special characters or is a keyword. Let the calling function deal with those issues.

Gasman · 2026-01-08T23:24:40+0000

Why would you supply a table name when all you are returning is some criteria?

Code:

? getdaterange("ckdate",53)
[ckdate] Between #10/10/2025# And #08/01/2026#

ClaraBarton · 2026-01-09T00:07:40+0000

Well you can't throw a field in there without telling it where it comes from.. Even I know that much. The table is not part of my list box. I went back and removed all my brackets and it's working a charm. Thank you all.

cheekybuddha · 2026-01-09T02:16:18+0000

ClaraBarton said:
Well you can't throw a field in there without telling it where it comes from.. Even I know that much.

Well that depends ... !

This will work with your function as you have it currently, but is not a good solution:

Code:

Me.lstYourlistBox.RowSource = "SELECT ... FROM qryAllPurpose WHERE " & GetDateRange("qryAllPurpose].[CkDate", 48)

Do as Ron suggests and remove the square brackets. Put them in when you pass the parameter.

If qryAllPurpose is the only table in the RowSource then you don't need to qualify the fields anyway:

Code:

Me.lstYourlistBox.RowSource = "SELECT ... FROM qryAllPurpose WHERE " & GetDateRange("CkDate", 48)

BUT CkDate must be in qryAllPurpose

arnelgp · 2026-01-09T03:02:22+0000

you may also try this:

Code:

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
   
'arnelgp
'fix DateField string remove unnecessary brackets first
DateField = Replace$(Replace$(DateField, "[", ""), "[", "")
DateField = Replace$(DateField, ".", "].[")

    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

Gasman · 2026-01-09T08:43:59+0000

ClaraBarton said:
Well you can't throw a field in there without telling it where it comes from.. Even I know that much. The table is not part of my list box. I went back and removed all my brackets and it's working a charm. Thank you all.

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?

The_Doc_Man · 2026-01-09T17:37:23+0000

ClaraBarton said:
Well you can't throw a field in there without telling it where it comes from.. Even I know that much. The table is not part of my list box.

Ah, but Clara, the problem isn't that you know where the fields originates. It is that the .RowSource or .Recordsource driving that control must include the field in the underlying record.

If I use SELECT A, B, C FROM Table1; as part of a combo box, but then in the related code I reference field D, ... even if it IS a member of Table1, it is NOT available in that context. Even when referenced as D.Table1 it is not available because it wasn't declared in the source. It doesn't MATTER that it is in the table. The SELECT clause is telling the object using it that certain fields will be called out. If you call out something that wasn't already in the list of fields, it ain't there.

Gasman · 2026-01-09T18:28:08+0000

You mean Table1.D Doc?

Ken Sheridan · 2026-01-09T18:31:39+0000

The_Doc_Man said:
Ah, but Clara, the problem isn't that you know where the fields originates. It is that the .RowSource or .Recordsource driving that control must include the field in the underlying record.

If I use SELECT A, B, C FROM Table1; as part of a combo box, but then in the related code I reference field D, ... even if it IS a member of Table1, it is NOT available in that context. Even when referenced as D.Table1 it is not available because it wasn't declared in the source. It doesn't MATTER that it is in the table. The SELECT clause is telling the object using it that certain fields will be called out. If you call out something that wasn't already in the list of fields, it ain't there.

That's not the issue, which is whether, in building the string expression for the RowSource property, it is necessary to qualify the column names with the table names in the WHERE clause. The question posed by Gasman was 'Why would you supply a table name when all you are returning is some criteria?'. Clara's reply, 'Well you can't throw a field in there without telling it where it comes from..' is only correct where an unqualified reference to a column in a query which joins two or more tables would be ambiguous.' Gasman's response, '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?' sums it up nicely if I understand him correctly, i.e, the 'elsewhere' in this case being the assignment of the function's return value to a string expression as the RowSource property of a list box, the table names need only be included with the column name where necessary to avoid an ambiguous reference. As the function, as originally written wraps the value of the DateField argument in square brackets, however, it would not possible to pass the qualified column name into the function, so it would be necessary to exclude the square brackets from the function's return value, as Ron suggested. The qualified column name could then be passed into the function, with or without square brackets as necessary. My preference, however, would have been to keep the square brackets in the code, but to include a TableName argument and wrap both in square brackets, along with the dot operator of course, when building the return value. That way inexperienced developers do not have to worry about whether a reference might be ambiguous or not, or whether the column name might, unwisely, contain spaces or special characters.

Incidentally, I was interested to discover recently that the English word bracket is derived from the French word braquette, meaning a codpiece.

DHookom · 2026-01-09T18:38:48+0000

Ken Sheridan said:
Incidentally, I was interested to discover recently that the English word bracket is derived from the French word braquette, meaning a codpiece.

Now, I had to research “codpiece”. I’m fairly certain I can use this information in future social interactions

ClaraBarton · 2026-01-09T18:41:06+0000

Thanks, Doc. I got it.

Code:

Me.txtAmount = DSum("Amount", "qryAllPurpose", "AccountID = " & ID & strRange)

I was trying to do a date range from the table "in" the query and not the actual query.

ClaraBarton · 2026-01-09T18:41:29+0000

"codpiece"? Way more than I needed....

bad bracketing

Registered User.

Immoderate Moderator

Registered User.

Active member

Registered User.

Registered User.

Active member

Active member

Enthusiastic Amateur

Registered User.

AWF VIP

..forever waiting... waiting for jellybean!

Enthusiastic Amateur

Immoderate Moderator

Enthusiastic Amateur

Active member

Active member

Registered User.

Registered User.

Similar threads

Users who are viewing this thread