DSum Error

[geebee]

Hi,

I have the following in my form:

Dim varselected As Variant
Dim strSQL As String
For Each varselected In Me.programvalue.ItemsSelected
strSQL = strSQL & "'" & Me.programvalue.ItemData
(varselected) & "',"
Next varselected
If strSQL <> "" Then
strSQL = "[obligation] IN (" & Left(strSQL, Len
(strSQL) - 0) & ")"

With CodeContextObject
Me.looktest = DSum("Allocation", "Program",
strSQL)

End With



When I added the following, I get an error that says that
the object doesn't contain the Automation object 'strSQL':

Me.looktest2 = Nz(DSum
("[VouchAmt]", "FY02_BASETABLE", "strSQL and datepart('m',
[Start Date])= 10 and [FY] = [forms]![password]![FY]"), 0)


I suppose it has something to do with the additional
criteria along with the strSQL. How can I resolve this.
Is my syntax wrong? Because Me.looktest = DSum
("Allocation", "Program", strSQL) works. But in this
case the DSum is much more complex.

Thanks in advance,
geebee

 

[Roger Carlson]

You can help discover what is going on by adding a
Debug.Print strSQL
line just after you complete creating the variable. Put a break point on
the With statement and run the code. The string variable will be evaluated
and shown in the immediate window. This will show you what is actually
happening an you can modify your code to fix it.

 

[Dirk Goldgar]

Hi,

I have the following in my form:

Dim varselected As Variant
Dim strSQL As String
For Each varselected In Me.programvalue.ItemsSelected
strSQL = strSQL & "'" & Me.programvalue.ItemData
(varselected) & "',"
Next varselected
If strSQL <> "" Then
strSQL = "[obligation] IN (" & Left(strSQL, Len
(strSQL) - 0) & ")"

With CodeContextObject
Me.looktest = DSum("Allocation", "Program",
strSQL)

End With



When I added the following, I get an error that says that
the object doesn't contain the Automation object 'strSQL':

Me.looktest2 = Nz(DSum
("[VouchAmt]", "FY02_BASETABLE", "strSQL and datepart('m',
[Start Date])= 10 and [FY] = [forms]![password]![FY]"), 0)


I suppose it has something to do with the additional
criteria along with the strSQL. How can I resolve this.
Is my syntax wrong? Because Me.looktest = DSum
("Allocation", "Program", strSQL) works. But in this
case the DSum is much more complex.

Thanks in advance,
geebee

You've got the reference to strSQL inside the quotes for the criteria
string. Try this:

Me.looktest2 = _
Nz(DSum("VouchAmt", "FY02_BASETABLE", _
strSQL & _
" and datepart('m',[Start Date])= 10" & _
" and [FY] = [forms]![password]![FY]"), _
0)

Incidentally, I don't see any purpose in this With statement:

With CodeContextObject

and its corresponding "End With" statement. You're using the Me keyword
to qualify looktest.