Call Module from SQL

[Bill Phillips]

I have a module that returns a specified field from an array. I have used it
repeatedly so I know it works correctly. I am trying to call the module from
a SQL statement and it keeps prompting me for one of the module arguments and
I do not understand why.

The module code is as follows:
Public Function SplitItDbl(ByVal lngNdx As Long, ByVal strSplit As String)
As Double
Dim varSplit As Variant

varSplit = Split(strSplit, ";")
SplitItDbl = varSplit(lngNdx)

End Function

The SQL call is as follows:
strSQLSlsAmt = "INSERT INTo tblItemSlsDet (SalesAmt) " & _
"SELECT SplitItDbl(lintMonthEval,ZIndexSMSEW.salesamt) as
SalesAmt " & _
"FROM ZIndexSMSEW " & _
"WHERE ZIndexSMSEW.cono = '" & intCono & "' AND
ZIndexSMSEW.whse = '" & strWhse & "' AND ZIndexSMSEW.custno = '" & lintCustno
& "' AND ZIndexSMSEW.yr = '" & intSalesYear & "' AND ZIndexSMSEW.prod = '" &
strItemID & "'"

I am using the DoCmd.RunSQL to run the SQL statement. Whenever I try to call
this module I get prompted for lintMonthEval which is the index number of the
month I am evaluating.

Thanks in advance,

 

[Douglas J. Steele]

Are you sure you've spelled the name of the field correct? The field is
lintMonthEval, and it's actually in table ZIndexSMSEW, correct?

 

[Bill Phillips]

The field is not in ZIndexSMSEW. ZIndexSMSEW has a filed named salesamt.
salesamt has 12 elements reptresenting the sales $ for Jan-Dec (Jan = 1, Feb
= 2...)lintMonthEval is the array element I'm evaluating. The module has 2
arguments 1. element to evaluate (declared as long), 2. Fieldname to evaluate
(declared as double). I'm just trying to pass the argument to the module.

HTH

 

[Douglas J. Steele]

If linMonthEval is a VBA variable, then you need to put it outside of the
quotes:

strSQLSlsAmt = "INSERT INTo tblItemSlsDet (SalesAmt) " & _
"SELECT SplitItDbl(" & lintMonthEval & "," & _
"ZIndexSMSEW.salesamt) as SalesAmt " & _
"FROM ZIndexSMSEW " & _
"WHERE ZIndexSMSEW.cono = '" & intCono & "' " & _
"AND ZIndexSMSEW.whse = '" & strWhse & "' " & _
"AND ZIndexSMSEW.custno = '" & lintCustno & "' " & _
"AND ZIndexSMSEW.yr = '" & intSalesYear & "' " & _
"AND ZIndexSMSEW.prod = '" & strItemID & "'"

Realistically, though, the correct solution would be to normalize your
tables. Having repeating groups as you obviously do is definitely not the
best approach.

 

[Albert D. Kallal]

Is lintMonthEval a field, or a variable?

You can NOT use variable names in your sql.....

You would have to use:
strSQLSlsAmt = "INSERT INTo tblItemSlsDet (SalesAmt) " & _
"SELECT SplitItDbl(" & lintMonthEval &
",ZIndexSMSEW.salesamt) as SalesAmt " & _


If lintMonthEval is a field in the table, then use table qualifiers in front
of the fields.....

 

[Bill Phillips]

Thanks Albert & Doug. It was the syntax. I needed to encapsulate my variable
with " & variable name &". Once I did that it worked as expected.