DSUM VBA and stored variables

[bstice]

I am trying to use VBA to change some the criteria in the DSUM function. As you see in the code below, a combobox value changes which field I want to use as a criteria and the value used as the criteria for that field is also in the combobox. These two values are stored as critfield and crit.

When I run the code, I get a "invalid use of null error", but I see the correct values passing through to critfield and crit. Would someone please take a look and let me know how I can fix this. Any help is much appreciated!!!

Here is my code:

Private Sub Combo191_AfterUpdate()
Dim crit As String
Dim critfield As String
Dim answer1 As Long

If Me.criteria1.value = "1" Then Me.CriteriaField.value = "region"
If Me.criteria1.value = "2" Then Me.CriteriaField.value = "Industry"
If Me.criteria1.value = "3" Then Me.CriteriaField.value = "Level 1"
If Me.criteria1.value = "4" Then Me.CriteriaField.value = "Level 2"
If Me.criteria1.value = "5" Then Me.CriteriaField.value = "Level 3"
critfield = Me.CriteriaField.value
crit = Me.Combo191.column(6)
answer1 = DSum("[sumofNet Amount - US]", "POSTSALECOSTv1", "[FML Account Code *] = 4428 and critfield = crit and [Accounting Period *]=" & [AccountingPeriod] & "")

BStice

 

[KenHigg]

To start with I would suggest putting parens around each expresion that is to be evaluated. This may or may not make a difference in this case but it does make the logic easier to follow.

DSum("[sumofNet Amount - US]", "POSTSALECOSTv1", "([FML Account Code *] = 4428) and (critfield = crit) and ([Accounting Period *]=" & [AccountingPeriod] & ")")

Second, I would suggest stepping through the code and examine the values or your variables just before you get to the dsum line.

Finally, I'm guessing that some strings need to be in quotes:

(critfield = crit)

Should be:

(critfield = 'crit')

And this:

[Accounting Period *]=" & [AccountingPeriod] & ""

Should be something like:

[Accounting Period *]='" & [AccountingPeriod] & "'"

???

Just some quickies -

 

[namliam]

1) Dont use spaces and special characters *- etc in field names...
2)You need to substitute the field in exactly like you are doing with AccountintPeriod...
answer1 = DSum("[sumofNet Amount - US]", "POSTSALECOSTv1", "[FML Account Code *] = 4428 and " & critfield & " = " & crit & " and [Accounting Period *]=" & [AccountingPeriod] & "")

Depending on the field type you need to enclose crit by ' ' for text and # # for dates

Good luck !

 

[bstice]

Little additional help

Thanks guys for all of your help. I got it to work, but some of my values that pass through cause it to error out.

Here is my code:
Private Sub update()
Dim rank As Integer
Dim field As String
Dim crit As String

rank = Me.Combo6
If rank = "1" Then field = "region"
If rank = "2" Then field = "Industry"
If rank = "3" Then field = "Level 1"
If rank = "4" Then field = "Level 2"
If rank = "5" Then field = "Level 3"
crit = Me.Combo6.column(6)
Me.example = DSum("[SumOfNet Amount - US]", "POSTSALECOSTv1", "[FML Account Code *] = 4435 and [Accounting Period *] = " & accountingperiods & " and " & field & "='" & crit & "'")

End Sub

This is the error -
Run-Timer error '3075':
Syntax error (missing operator) in query expression '[FML Account Code *] = 4435 and [Accounting Period *] = 200801 and Level 1 = 'MFG-East-Mideast-201140726'.

Any help is appreciated. Thanks!

Bstice

 

[namliam]

DONT use spaces and special characters in you field names, then you wouldnt have run into this problem....

Your problem is here:
[FML Account Code *] = 4435 and [Accounting Period *] = 200801 and Level 1 = 'MFG-East-Mideast-201140726'.
The space in "Level 1"... You need to enclose this with [] like you did with Accounting period.

Me.example = DSum("[SumOfNet Amount - US]", "POSTSALECOSTv1", "[FML Account Code *] = 4435 and [Accounting Period *] = " & accountingperiods & " and [" & field & "]='" & crit & "'")