UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multiple "or"s In Dsum Function., Any Version    
 
   
Kamulegeya
post Feb 8 2018, 07:06 AM
Post#1



Posts: 1,779
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


Hello Community,

I want a criteria in dsum similar to the n() criteria in SQL queries.

I thought i can use Or as shown below.

CODE
totalgrossPrev = Nz(DSum("ConsolidatedPay", "qryReport", "PeriodID=" & Me.listperiod2.Column(0) & " and PayrollTypeID=3" & " or PayrollTypeID=4" & " or PayrollTypeID=5" & " or PayrollTypeID=6" & " or PayrollTypeID=7" & " or PayrollTypeID=8" & " or PayrollTypeID=10"), 0)


Of course it compiles but...is there another way of doing it?

I am also afraid that it might be returning incorrect results.

Ronald
Go to the top of the page
 
BruceM
post Feb 8 2018, 07:57 AM
Post#2


UtterAccess VIP
Posts: 7,167
Joined: 24-May 10
From: Downeast Maine


Can you apply criteria to the query to limit the records to payroll types 3 - 10? If so, no need to specify any criteria for payroll type.

Other than that, I assume the logical test is that PeriodID = Me.listperiod2.Column(0), and the payroll type is between 3 and 10 inclusive. If so, I think you can take advantage of the criteria in a domain function being a query Where condition without the "WHERE", which means you can use Between:

totalgrossPrev = Nz(DSum("ConsolidatedPay", "qryReport", "PeriodID=" & Me.listperiod2.Column(0) & " and PayrollTypeID Between 3 And 10), 0)
Go to the top of the page
 
Kamulegeya
post Feb 8 2018, 08:14 AM
Post#3



Posts: 1,779
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


Thank you BruceM.

It is shorter and understandable.

Ronald
Go to the top of the page
 
BruceM
post Feb 8 2018, 08:23 AM
Post#4


UtterAccess VIP
Posts: 7,167
Joined: 24-May 10
From: Downeast Maine


Glad to help. Sometimes it just takes another set of eyes.
Go to the top of the page
 
RJD
post Feb 8 2018, 10:28 AM
Post#5


UtterAccess VIP
Posts: 8,083
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but is there a PayrollTypeID equal to 9? If so, the Between 3 and 10 is not the same logic as was tried in the original, where 9 was not included.

Just wondering ...

HTH
Joe


--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
BruceM
post Feb 8 2018, 11:12 AM
Post#6


UtterAccess VIP
Posts: 7,167
Joined: 24-May 10
From: Downeast Maine


Missed that! Like I said, another set of eyes...

If so, like this, I suppose:

" and (PayrollTypeID Between 3 And 8 Or PayrollTypeID = 10)"
Go to the top of the page
 
RJD
post Feb 8 2018, 11:15 AM
Post#7


UtterAccess VIP
Posts: 8,083
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st February 2018 - 02:31 AM