DSUM - changing criteria values with VBA

[bstice]

I posted a similar question the other day on the forms forum, but no one responded, so I will try this group.

I have a form which displays financial results for our company by month. I use the DSUM function to sort data right out of our General Ledger. Right now this is a sample of the DSUM that used.

Me.CR = Val(Nz(DSum("[sumofNet Amount - US]", "SummarizedGLDATA", "[FML Account Code *] = '1111' and [" & field & "] = '" & crit & "' and [Accounting Period *]='" & [AccountingPeriod] & "'"), 0))

field and crit are fields that change dynanically based on the group that I am looking at and accountingperiod comes from a combo box on the form.

Right now, I can only sort by month and I would like to add the ability to sort by quarter as well. I have tried an IF statement like:

if me.accountingperiod = "Q1" then accountingperiod = "200801 and 200802 and 200803"
else accountingperiod = me.accountingperiod
endif

This does not seem to work. I would appreciate any help. In fact, if you are able to help me solve this, you can say that you are smarter than the guys at the forms forum! Thanks

Brennan

 

[CyberLynx]

hmmm...maybe try this:

Dim DSumCriteria As String
Dim AcntPeriod As String

DSumCriteria = "[FML Account Code *] = '1111' AND [" & field & "] = '" & crit & "' AND "

Select Case Ucase(Me.AccountingPeriod)
   Case "Q1"
     AcntPeriod = "([Accounting Period *]='200801' OR [Accounting Period *]='200802' OR [Accounting Period *]='200803')"     
   Case "Q2"
      AcntPeriod = "([Accounting Period *]='200804' OR [Accounting Period *]='200805' OR [Accounting Period *]='200806')"
   Case "Q3"
      AcntPeriod = "([Accounting Period *]='200807' OR [Accounting Period *]='200808' OR [Accounting Period *]='200809')"
   Case "Q4"
      AcntPeriod = "([Accounting Period *]='200810' OR [Accounting Period *]='200811' OR [Accounting Period *]='200812')"
   Case Else
      AcntPeriod = "[Accounting Period *]='" & Me.AccountingPeriod & "'"
End Select

DSumCriteria = DSumCriteria & AcntPeriod

Me.CR = CCur(Nz(DSum("[sumofNet Amount - US]", "SummarizedGLDATA", DSumCriteria), 0))

.