Too Many Arguments in a Function?

[LarryP]

I wrote a function to be used in a query. The function receives two sets of
10 values from the query tables (10 "was" values and 10 "is now" values). It
is supposed to compare the was/is in each case, and if there has been a
change, append some text to a string that ultimately is returned by the
function. (E.g., if the price has changed, the text would be "Price was
2.29, is now 3.17 |||"; a similar statement is appended to the string for any
other variable that has changed.)

When my query runs, however, the field that should contain the text shows
#Error, with no indication that the function was even called (I put a break
in the function code, but the process apparently never even gets to that
point). Is 20 arguments too many? If not, can anybody think of another
reason why I get the #Error? If I take out my function and replace it with a
fixed text value my query runs fine, so whatever is going on is almost
certainly due to some problem with the function.

 

[Klatuu]

20 is a lot of arguments, but I don't know that there is such a limit. Your
problem is more likely how you are calling the function from within the
query. Post your SQL and let's have a look.

You can test to see if 20 arguments is okay by creating a function with that
many arugments and call it from the immediate window.

 

[Minton M]

I wrote a function to be used in a query. The function receives two sets of
10 values from the query tables (10 "was" values and 10 "is now" values). It
is supposed to compare the was/is in each case, and if there has been a
change, append some text to a string that ultimately is returned by the
function. (E.g., if the price has changed, the text would be "Price was
2.29, is now 3.17 |||"; a similar statement is appended to the string for any
other variable that has changed.)

When my query runs, however, the field that should contain the text shows
#Error, with no indication that the function was even called (I put a break
in the function code, but the process apparently never even gets to that
point). Is 20 arguments too many? If not, can anybody think of another
reason why I get the #Error? If I take out my function and replace it with a
fixed text value my query runs fine, so whatever is going on is almost
certainly due to some problem with the function.

The chances are that:

1. Your input parameters are set as something other than variant, so
they complain when an unexpected value is passed in. I hate variants
but invariably (ho ho ho) you have to use them when calling functions
this way.
2. There's a typo in a field name that you're passing.
3. One of the values is throwing an error.
4. There's an error in your code in the function.

To be honest, I would recommend not doing this in this way. It's a
freaking nightmare to debug and maintain. Why not create a VB function
that opens the query, iterates through the records, does the
comparisons and drops the change info into a log table?

Regards,
James

 

[Tom van Stiphout]

On Fri, 28 Dec 2007 13:01:43 -0800 (PST), Minton M

Really? What data type in an Access table conforms better to Variant
than to a more specific data type?

Unless with "unexpected value" you mean a Null value. You could have
pointed that out more clearly.

-Tom.

The chances are that:

1. Your input parameters are set as something other than variant, so
they complain when an unexpected value is passed in. I hate variants
but invariably (ho ho ho) you have to use them when calling functions
this way.

<clip>

 

[Tom van Stiphout]

On Fri, 28 Dec 2007 12:17:01 -0800, LarryP

If your function does not compile, that would explain the behavior.
Select Debug > Compile to ensure you have legal syntax.

You mention the breakpoint is not hit. Comment out all the code within
the function and try again.
This is a public function in a standard module, right?

20 is not too many.

Show us some SQL and code.

-Tom.

 

[LarryP]

For all who replied, the function is definitely not getting called by the
query. I threw in a messagebox as the first VBA line, nada, and also tried
commenting out all the VBA, nada again. Here's the SQL from the query -- the
offending function starts on the sixth line. The VBA for the function is
shown after the SQL, but all the evidence so far suggests the problem is with
the query rather than the function.

SELECT tblWorkingPRISMLIData.ContractNo,
tblWorkingPRISMLIData.MPN,
tblWorkingPRISMLIData.StorageUnit,
tblWorkingPRISMLIData.ContractCode,
tblWorkingPRISMLIData.DoNotAppend,

ShowChanges(
[tblContractLIData].[ContractCode],
[tblContractLIData].[VGroup],
[tblContractLIData].[Plant],
[tblContractLIData].[OrderNo],
[tblContractLIData].[SLoc],
[tblContractLIData].[ContractType],
[tblContractLIData].[StorageBin],
[tblContractLIData].[AvailStock],
[tblContractLIData].[Price],
[tblContractLIData].[Typ],
[tblWorkingPrismLIData].[ContractCode],
[tblWorkingPrismLIData].[VGroup],
[tblWorkingPrismLIData].[Plant],
[tblWorkingPrismLIData].[OrderNo],
[tblWorkingPrismLIData].[SLoc],
[tblWorkingPrismLIData].[ContractType],
[tblWorkingPrismLIData].[StorageBin],
[tblWorkingPrismLIData].[AvailStock],
[tblWorkingPrismLIData].[Price],
[tblWorkingPrismLIData].[StorageType]) AS Changes

FROM tblWorkingPRISMLIData INNER JOIN tblContractLIData ON
(tblWorkingPRISMLIData.ContractNo = tblContractLIData.ContractNo)
AND (tblWorkingPRISMLIData.MPN = tblContractLIData.MPN)
AND (tblWorkingPRISMLIData.StorageUnit = tblContractLIData.StorageUnit)

WHERE
(((ShowChanges([tblContractLIData].[ContractCode],[tblContractLIData].[VGroup],[tblContractLIData].[Plant],[tblContractLIData].[OrderNo],[tblContractLIData].[SLoc],[tblContractLIData].[ContractType],[tblContractLIData].[StorageBin],[tblContractLIData].[AvailStock],[tblContractLIData].[Price],[tblContractLIData].[Typ],[tblWorkingPrismLIData].[ContractCode],[tblWorkingPrismLIData].[VGroup],[tblWorkingPrismLIData].[Plant],[tblWorkingPrismLIData].[OrderNo],[tblWorkingPrismLIData].[SLoc],[tblWorkingPrismLIData].[ContractType],[tblWorkingPrismLIData].[StorageBin],[tblWorkingPrismLIData].[AvailStock],[tblWorkingPrismLIData].[Price],[tblWorkingPrismLIData].[StorageType]))>""));


Public Function ShowChanges(strCCode As String, strVGrp As String, strPlant
As String, strOrderNo As String, strSLoc As String, strCType As String,
strStorageBin As String, lngAvailStock As Long, dblPrice As Double,
strStorageType As String, strWasCCode As String, strWasVGrp As String,
strWasPlant As String, StrWasOrderNo As String, strWasSLoc As String,
strWasCType As String, strWasStorageBin As String, lngWasAvailStock As Long,
dblWasPrice As Double, strWasStorageType As String) As String
ShowChanges = ""
If strCCode <> strWasCCode Then ShowChanges = ShowChanges & "Contract Code
was " & strWasCCode & ", is now " & strCCode & " ||| "
If strVGrp <> strWasVGrp Then ShowChanges = ShowChanges & "VGroup was " &
strWasVGrp & ", is now " & strVGrp & " ||| "
If strPlant <> strWasPlant Then ShowChanges = ShowChanges & "Plant was " &
strWasPlant & ", is now " & strPlant & " ||| "
If strOrderNo <> StrWasOrderNo Then ShowChanges = ShowChanges & "Order No
was " & StrWasOrderNo & ", is now " & strOrderNo & " ||| "
If strSLoc <> strWasSLoc Then ShowChanges = ShowChanges & "SLoc was " &
strWasSLoc & ", is now " & strSLoc & " ||| "
If strCType <> strWasCType Then ShowChanges = ShowChanges & "Contract Type
was " & strWasCType & ", is now " & strCType & " ||| "
If strStorageBin <> strWasStorageBin Then ShowChanges = ShowChanges &
"Storage Bin was " & strWasStorageBin & ", is now " & strStorageBin & " ||| "
If lngAvailStock <> lngWasAvailStock Then ShowChanges = ShowChanges &
"Available Stock was " & lngWasAvailStock & ", is now " & lngAvailStock & "
||| "
If dblPrice <> dblWasPrice Then ShowChanges = ShowChanges & "Price was " &
dblWasPrice & ", is now " & dblPrice & " ||| "
If strStorageType <> strWasStorageType Then ShowChanges = ShowChanges &
"Storage Type was " & strWasStorageType & ", is now " & strStorageType & "
||| "
End Function

 

[LarryP]

After posting my SQL a bit ago I took another look at all the replies, and
yours finally got through my thick head. The problem was that in some
cases, the fields I was passing as arguments were nulls.

Rather than using variants, though, and then dealing with nulls in my
function's VBA, I modified the SQL to prepend either < "" & > (for text
arguments) or < 0 + > (for numeric arguments) when calling the function, and
presto, all worked as it should. The prepended fragment ensured that each
argument had at least SOME value in all cases.

Thanks to you and all others for your help and interest.