Syntax error

[sargentrs]

When I try to utilize this function in a query, I get a syntax error. I've looked and I can't pinpoint it. Can somebody point it out?

In the query: RunBal: fncRunBal([ITEM_ID],[QTY],[PierceBal])

Function fncRunBal(strItemId As String, lngQty As Long, lngPierceBal As Long) As Long

'Variables that retain their values.
Static strPartNo As String 'This is the Part Number (strItemId)'
Static lngNeed As Long 'This is the s/o quantity (lnqQty)'
Static lngBal As Long 'This is the running sum of what is required to fab (lngPierceBal)'

If strPartNo <> strItemId Then 'If the current ID does not match the last ID, then (re)initialize.

strPartNo = strItemId 'Reset the part number to the new part number'
lngNeed = lngQty 'Reset the s/o quantity'
lngBal = lngPierceBal 'Reset the PierceBal'

If lngBal <= 0 Then 'If the 1st PierceBal record of the new part number is <= 0'
lngRun = Abs(lngBal) 'Then run the absolute value of PierceBal'
Else
lngRun = 0 'If positive, don't run any'

End If
Else 'If the current ID matches the last and the PierceBal is negative show the s/o quantity'
If lngBal <= 0 Then 'If PierceBal is negative'
lngRun = lngNeed 'Run the s/o qty'
Else 'If PierceBal is positive'
lngRun = 0 'Don't run any'
End If

End If

'Pass the balance to run back to the query.
fncRunBal = lngRun

End Function

 

[pbaldy]

On what line? I don't see lngRun declared anywhere.

 

[MSAccessRookie]

When I try to utilize this function in a query, I get a syntax error. I've looked and I can't pinpoint it. Can somebody point it out?

Function fncRunBal(strItemId As String, lngQty As Long, lngPierceBal As Long) As Long

[COLOR=black][FONT=Verdana]  'Variables that retain their values.[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]    Static strPartNo As String 'This is the Part Number (strItemId)'[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Static lngNeed As Long 'This is the s/o quantity (lnqQty)'[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Static lngBal As Long 'This is the running sum of what is required to fab (lngPierceBal)'[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana][COLOR=red][B]    Dim lngrun As Long[/B][/COLOR][/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]    If strPartNo <> strItemId Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        'If the current ID does not match the last ID, then (re)initialize.[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        strPartNo = strItemId 'Reset the part number to the new part number'[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        lngNeed = lngQty 'Reset the s/o quantity'[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        lngBal = lngPierceBal 'Reset the PierceBal'[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]        If lngBal <= 0 Then 'If the 1st PierceBal record of the new part number is <= 0'[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            lngRun = Abs(lngBal) 'Then run the absolute value of PierceBal'[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        Else[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            lngRun = 0 'If positive, don't run any'[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Else[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        'If the current ID matches the last and the PierceBal is negative show the s/o quantity'[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        If lngBal <= 0 Then 'If PierceBal is negative'[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            lngRun = lngNeed 'Run the s/o qty'[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        Else 'If PierceBal is positive'[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            lngRun = 0 'Don't run any'[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    End If[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]    'Pass the balance to run back to the query.[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    fncRunBal = lngRun[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]End Function[/FONT][/COLOR]

I have reformatted the code and nothing jumps out at me except for the fact that although you can get away with it, you did not declare the variable lngrun., and this could cause issues. One way to avoid this is to add "Option Explicit at the top of your VBA Module. This forces you to define all variables, or the program will not compile and run.

When the VBA procedure fails, what error is displayed? In addition, when you click "Debug", one of the lines should be displayed in red (or at least in a different color), which one is it?

I notice that pbaldy also made a similar comment. Define the variable, try it again, and report the results if it still does not work.

 

[sargentrs]

DUH! Now I feel like an idiot. Couldn't see the forest for the trees, I guess. Declared lngRun and it works like a charm. Thanks!