Call Module from SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
Are you sure you've spelled the name of the field correct? The field is
lintMonthEval, and it's actually in table ZIndexSMSEW, correct?
 
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
 
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.
 
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.....
 
Thanks Albert & Doug. It was the syntax. I needed to encapsulate my variable
with " & variable name &". Once I did that it worked as expected.
 
Back
Top