syntax error in function

[ClaraBarton]

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

With the above function, I cannot get GetDateRange (below) to compile
The MsgBox returns CkDate & 48 which is exactly what I want but somehow I'm not entering the variable in the function properly.
I've tried " & fld & " and (fld) and [fld] and....
What am I doing wrong? the frm!CkDate.Name is surely a string, is it not?

Private CallingForm     As String

Private Sub cboDate_AfterUpdate()
    Dim fld           As String
    Dim frm         As Form

Select Case CallingForm
    Case Is = "frmAccounts"
        Set frm = Forms(CallingForm).Register.Form
        fld = frm!CkDate.Name
End Select
    
GetDateRange(fld, cboDate)
MsgBox fld & " & " & Me.cboDate.Value
    
End Sub

 

[Gasman]

So what is the error?
Show us all of that function.

 

[ClaraBarton]

I use that function all over like this:

strRange = GetDateRange("CkDate", cboDate.Value)

and it works fine.
But now I'm trying to make it work on a popup form and I need to use a variable for various forms that call it. There is only one select case because I'm stumped before I've barely started.
The above won't compile... stays red, whatever. It says syntax error. That's all I know.

 

[tvanstiphout]

> strRange = GetDateRange("CkDate", cboDate.Value)

You're sending cboDate.Value to RangeType As Integer
Is that really what you wanted? Note that today's date expressed as a number is 46005, which would overflow the integer data type.
(in the immediate window)
?CInt(Date)
Overflow

?CLng(Date)
46005

EDIT: I just noticed that in #1 you said cboDate.Value is 48, so it is presumably not a date. Perhaps the control name of "cboDate" is a misnomer.

 

[tvanstiphout]

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

With the above function, I cannot get GetDateRange (below) to compile
The MsgBox returns CkDate & 48 which is exactly what I want but somehow I'm not entering the variable in the function properly.
I've tried " & fld & " and (fld) and [fld] and....
What am I doing wrong? the frm!CkDate.Name is surely a string, is it not?

Private CallingForm     As String

Private Sub cboDate_AfterUpdate()
    Dim fld           As String
    Dim frm         As Form

Select Case CallingForm
    Case Is = "frmAccounts"
        Set frm = Forms(CallingForm).Register.Form
        fld = frm!CkDate.Name
End Select
   
GetDateRange(fld, cboDate)
MsgBox fld & " & " & Me.cboDate.Value
   
End Sub

Yes that is a string.
I read this code as: we have a form frmAccounts, with a subform Register. The subform has a control CkDate. We're setting fld to CkDate's name.
That seems equivalent to:
fld = "CkDate"

When you hit Debug > Compile, which line of code is highlighted, and what is the error message?

 

[Gasman]

I use that function all over like this:

strRange = GetDateRange("CkDate", cboDate.Value)

and it works fine.
But now I'm trying to make it work on a popup form and I need to use a variable for various forms that call it. There is only one select case because I'm stumped before I've barely started.
The above won't compile... stays red, whatever. It says syntax error. That's all I know.

You are not setting the returned value to anything?
What do you expect it to do?

 

[ClaraBarton]

Function GetDateRange(DateFieldName As String, RangeType As Integer) As String
    'Last Month
Dim firstofmonth As Date
Dim lastofmonth As Date
firstofmonth = DateSerial(Year(Date), Month(Date), 0)
lastofmonth = DateSerial(Year(firstofmonth), Month(firstofmonth) - 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([" & DateFieldName & "]) = " & Year(Date) & " And Month([" & DateFieldName & "]) = " & Month(Date)
        Case 50 'Last Month
            GetDateRange = "[" & DateFieldName & "] between #" & Format(firstofmonth, "mm/dd/yyyy") & "# AND #" & Format(lastofmonth, "mm/dd/yyyy") & "#"
        Case 51 'Last 30 Days
            GetDateRange = "[" & DateFieldName & "] Between #" & (Date - 30) & "# And #" & Date & "#"
        Case 52 'Last 60 Days
            GetDateRange = "[" & DateFieldName & "] Between #" & (Date - 60) & "# And #" & Date & "#"
        Case 53 'Last 90 Days
            GetDateRange = "[" & DateFieldName & "] Between #" & (Date - 90) & "# And #" & Date & "#"
        Case 54 'Last 12 Months
            GetDateRange = "[" & DateFieldName & "] between #" & Format(firstofmonth, "mm/dd/yyyy") & "# AND #" & Format(lastofmonth, "mm/dd/yyyy") & "#"
        Case 55 'This Quarter
            GetDateRange = "[" & DateFieldName & "] between #" & Format(fdoQtr, "mm/dd/yyyy") & "# AND #" & Format(ldoQtr, "mm/dd/yyyy") & "#"
        Case 56 'Last Quarter
             GetDateRange = "[" & DateFieldName & "] between #" & Format(fdoPrevQtr, "mm/dd/yyyy") & "# AND #" & Format(ldoPrevQtr, "mm/dd/yyyy") & "#"
        Case 57 'This Year
            GetDateRange = "Year([" & DateFieldName & "]) = " & Year(Date)
        Case 58 'Last Year
            GetDateRange = "Year([" & DateFieldName & "]) = " & 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 = "[" & DateFieldName & "] Between #" & dtstart & "# And #" & dtend & "#"
         End Select
 End Function

I had a great deal of help on this forum to pull this all together some time ago.

 

[ClaraBarton]

If I mark out the GetDateRange it all compiles and the msgbox returns the values I need. But I can't compile with the formula in. I can't even get it to not turn red. I know I haven't done anything with the values yet. One step to work at a time! I was always told Rome wasn't built in a day.
The strRange was just an example from elsewhere that actually works.
This is what I can't get to work:

GetDateRange(fld, cboDate)

 

[DaveMD]

This is what I can't get to work:

GetDateRange(fld, cboDate)

As a few things to check given this is a compile time error:

1. Your call above is not capturing the return value. If you declare strRange and assign the result of the function to it, that may take care of the compile error:

Dim strRange As String
strRange = GetDateRange(fld, cboDate)

2. When calling a function with parameters in parenthesis without capturing the return value, you need to lead it with the "Call" keyword. However, I don't think this is the intent of your function call as it doesn't seem that would be of any use to not capture the return value. Therefore, I do not think this would solve your broader issue in this case.

Call GetDateRange(fld, cboDate)

3. Make sure the GetDateRange function is within the scope to be called from the module from which you are calling it. For example, is the function GetDateRange defined in a standard module (as a Public Function) where it would be within scope for it to be called from anywhere in your code. Or, is it defined within a different Form module where its scope would be limited.

 

[ClaraBarton]

Well, what do you know! I added call in front of it and everything calmed down. Now I'll look into the rest of your answer....
The function is in a standard module and I thought call was not necessary but you're saying that because I wasn't capturing the return value I needed it? I do intend to use the return value... Just haven't gotten there yet.
And yet I've used this repeatedly elsewhere without call. I am sorely confused. But sort of happy too
OK I'm now reworking the function. I'll be back...

 

[The_Doc_Man]

In theory you are allowed by VBA to put a function on a line with nothing else. However, there is a syntax issue that applies to functions and subs equally.

If you have function XYZ( A, B ) there are three legal ways to invoke it.

1. W = XYZ( A, B )
2. CALL XYZ( A, B )
3. XYZ A, B

Method 1 invokes a function and consumes the return value, but would fail for a sub because a sub returns no value. Method 2 invokes a function or a sub and if it IS a function, expressly discards the returned value. Method 3 invokes a function or a sub and implicitly discards a function's value. BUT this next way is illegal:

4. XYZ( A, B )

The complaint isn't that you have to have something to catch the returned value. It is that the statement analyzer doesn't want the parentheses in that case because it can't classify the statement by syntax alone when you do it that fourth way.

So... just for snorts and giggles, from your post #1 sample that gives you that SYNTAX error, change
GetDateRange(fld, cboDate)
to
GetDateRange fld, cboDate
and see if that works.

 

[Gasman]

I would have thought that you want whatever GetRange is going to produce?
You said you have used it everywhere before but as

A= Getrange(x,y)

Now you are just saying

Getrange(x,y)

For anything like that I would use a sub, as I do not expect anything to be coming back.

You said you could not get GetRange to compile?
Not true, you could not get a line somewhere else to compile, it just fell over when trying to use GetRange.

Read post #6.

So I would ask, what benefit do you get calling that function but ignoring the returned value?

 

[Gasman]

OK, now I am confused. Easily done I admit, but I just tried your logic and syntax, and it works fine, in that it compiles and runs?

Sub TestFunctionCall()
Dim strPeriod As String

strPeriod = "3-2025"
GetDaysSoFar (strPeriod)
End Sub

Function GetDaysSoFar(strPeriod As String) As Integer
Dim iMonth As Integer, iYear As Integer
Dim iPeriod() As String, strCriteria As String, strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset


Set db = CurrentDb

iPeriod = Split(strPeriod, "-")
'Debug.Print iPeriod(0) & " and "; iPeriod(1)

strCriteria = "Month(DailyDate) = " & Val(iPeriod(0)) & " And Year(DailyDate) = " & Val(iPeriod(1))
'Debug.Print strCriteria
strSQL = "Select DailyDate from tbldaily WHERE " & strCriteria & " GROUP by DailyDate"
Set rst = db.OpenRecordset(strSQL, dbReadOnly)
If Not rst.EOF Then
    rst.MoveLast
    GetDaysSoFar = rst.RecordCount
End If
rst.Close
Set rst = Nothing
Set db = Nothing

End Function

 

[MajP]

@Gasman
This is legal

getDaysSoFar (strPeriod)

But this would give a syntax error

getDaysSoFar (strPeriod, AnotherArgument)

Although this would be legal

getDaysSoFar (strPeriod),(AnotherArgument)

The parentheses rules in VBA seem kind of arbitrary. But the reason why yours work (single argument) and ClaraBarton's fails (multiple arguments) is explained here in the second quote.

What do you mean "cannot use parentheses?"

learn.microsoft.com

Here's the deal: parentheses mean several different things in VB and hence in VBScript. They mean:

1) Evaluate a subexpression before the rest of the expression: Average = (First + Last) / 2
2) Dereference the index of an array: Item = MyArray(Index)
3) Call a function or subroutine: Limit = UBound(MyArray)
4) Pass an argument which would normally be byref as byval: Result = MyFunction(Arg1, (Arg2)) ' Arg1 is passed byref, arg2 is passed byval

That's confusing enough already. Unfortunately, VB and hence VBScript has some weird rules about when #3 applies. The rules are

3.1) An argument list for a function call with an assignment to the returned value must be surrounded by parens: Result = MyFunc(MyArg)
3.2) An argument list for a subroutine call (or a function call with no assignment) that uses the Call keyword must be surrounded by parens: Call MySub(MyArg)
3.3) If 3.1 and 3.2 do not apply then the list must NOT be surrounded by parens.

And finally there is the byref rule: arguments are passed byref when possible but if there are “extra” parens around a variable then the variable is passed byval, not byref.

These rules are confusing and silly, as the designers of Visual Basic .NET realized. VB.NET does away with this rule, and insists that all function and subroutine calls be surrounded by parens. This means that in VB.NET, the statement MySub(MyArg) has different semantics than it does in VBScript and VB6 -- this will pass MyArg byref in VB.NET, byval in VBScript/VB6. This was one of those cases where strict backwards compatibility and usability were in conflict, and usability won.

Now it should be clear why the statement MySub(MyArg) is legal but MyOtherSub(MyArg1, MyArg2) is not. The first case appears to be a subroutine call with parens around the argument list, but that would violate rule 3.3. Then why is it legal? In fact it is a subroutine call with no parens around the arg list, but parens around the first argument! This passes the argument by value. The second case is a clear violation of rule 3.3, and there is no way to make it legal, so we give an error.

 

[Gasman]

Wow, so complicated.
Surprised I have not fallen onto that trap.

Thank you for the explanation @MajP
Hopefully that helps the O/P as well for the next time.

 

[ClaraBarton]

Ok ok okokok.. I'm rethinking the whole thing. I forgot that the popup is only for the custom selection (didn't read to the bottom!) and was trying to use it for complete date grab. I'm messing with something that worked well.

 

[The_Doc_Man]

There is ANOTHER issue that has come up in the past.

You HAVE to remember that a space or tab character IS a valid token in VBA, acting as a delimiter, and parentheses are also special types of delimiter. In these samples, be aware of the spaces, which I will exaggerate for emphasis. But one space or many consecutive spaces all have the same effect - a semantic token delimiter.

Presume the declaration is Public Function XYZ( U As Variant, V As Variant) As Variant where U and V use the default passage method, ByRef.
These statements are legal.

1. A = XYZ( B, C )
2. XYZ B, C
3. XYZ (B), (C)
4. CALL XYZ( B, C )

Sample 1 is the "ideal" call which captures the return value. Samples 2 and 4 call the function but implicitly (2) or explicitly (4) discard the value. And then there is sample 3, which like #2 invokes the function but discards the result. BUT the B & C arguments are in effect no longer ByRef because those extraneous parentheses turn the formal arguments into expressions that get evaluated FIRST, and each value gets a "fake" variable to be passed ByRef. The "real" B & C could not be altered, so if the function DID try to return something to B&C it wouldn't get there.

These are not correct:

5. A = XYZ B, C
6. A = XYZ ( B ), ( C )
7. XYZ ( B, C )

Sample 5 has the problem that with that equals-sign, you now have to worry about the syntax of a call, and that means that XYZ looks like you called the function in-line with NO parameters (because of that blank immediately after XYZ). Then you have B in isolation, and the probable error is wrong number of parameters for XYZ. But if formal parameters U & V were Optional, then the error would be Missing Operator because of the space between XYZ and actual parameter B.

Sample 6 has the same problem as sample 5, plus it would count B as an expression.

Sample 7 would accept (B,C) as the first actual parameter, but it would complain about syntax because there is an extra element (following the comma in the first actual argument). AND the second argument is missing.

 

[ClaraBarton]

Thanks for all your help.