Report Text-box Expression

I've not tried this, but what happens when you try:

=Nz(DSum("Amount","PurchaseOrders","[id] IN (16, 22, 24)"),0)

When you try to expand that with:

"[FiscalY]='Q4_2018' and [OrderType]='CAPEX' and [id]=16 or [id]=22 or [id]=24"

What this evaluates to is:
"([FiscalY]='Q4_2018' and [OrderType]='CAPEX' and [id]=16)" OR [id]=22 or [id]=24"

Which is not what I think you are looking for.  I think you want:
"[FiscalY]='Q4_2018' and [OrderType]='CAPEX' and ([id]=16 or [id]=22 or [id]=24)"
or rewritten with an IN clause
"[FiscalY]='Q4_2018' and [OrderType]='CAPEX' and [id] IN (16, 22, 24)"

HTH
Dale

This is really weird, get the same results.  Can't figure out why or how it's not working.  It should, but isn't.  A bug???

Great!! It works and a lot less typing.

How would I add the additional criteria?  FY and OrderType

Thanks

Thank You!!!!

As far as I can tell, the original syntax should work. I wonder, if the field in question is not defined as numeric? Could you check that?

Thanks, Dale go me going.  However, there is yet another one.

=Nz(DSum("Q3_2013, Q4_2018","Asset Projections 2","[id] IN (12)"),0)

Produces an error.  

Trying to add the values in the fields Q3_2018 and Q4_2018 for ID 12

Thanks

Not quite sure what you are trying to do with that syntax.

The syntax for DSUM is :

DSUM("Fieldname", "TableOrQueryName", "Criteria")

so if you are trying to sum the Amount column, from "Asset Projections 2", where the FYQuarter is either 3rd Qtr or 4th Qtr of 2018, and the ID = 12, then the syntax would be:

=Nz(DSum("Amount","Asset Projections 2","[FiscalY] IN ('Q3_2018', 'Q4_2018) AND ID = 12"), 0)

Note that I only use the IN ( ) construct when I'm trying to select multiple values from the same field, othewise I use the equality comparison.

But this seems rather convoluted, and every time you want to change the quarter or year of the query, you are going to have to redesign these domain statements.  Not knowing what your data structure actually looks like, this becomes rather difficult, but if it were me, I would probably create a table with values like FiscalY, OrderType, and ID, and would populate that table with records which define the combinations I want to query

FiscalY            OrderType           ID
"Q3_2018"         NULL                 12
"Q4_2018"         NULL                 12

And then join that table to whatever table it is you are trying to summarized data for. on those three columns.  You could then run the same query for different values of those combinations.  You might even add a QueryName to that table, then you could do something like:

"[FiscalY]='Q4_2018' and [OrderType]='CAPEX' and [id] IN (16, 22, 24)"

QueryName      FiscalY            OrderType           ID
Query1             "Q4_2018"         "CAPEX"              16
Query1             "Q4_2018"         "CAPEX"              22
Query1             "Q4_2018"         "CAPEX"              24

Query2             "Q4_2018"         NULL                 12
Query2             "Q3_2018"         NULL                 12
Query2             "Q4_2018"         NULL                 12

And with a query that looks something like:

SELECT T2.QueryName, SUM(T1.Amount) as SumAmt
FROM yourTable as T1
INNER JOIN myTable T2 ON T1.FiscalY = T2.FiscalY AND T1.OrderType = T2.OrderType and T1.ID = T2.ID
GROUP BY T2.QueryName

you could save this as qrySum and do:

= DLOOKUP("SumAmt", "qrySum", "QueryName = 'Query1'")

This would give you the flexibility to create presaved query criteria, or create these criteria at run time.

HTH
Dale

Dale - kind of obvious your way ahead of me, but we're thinking the same.  I need to construct the db so as to run reports based on various FY dates.  I'll need to read through this a few times.

Thanks for your help.  Greatly appreciated..
Bob

First off, I would not have a column that contains both the year and the quarter, I would have a FYYear and a FYQtr column, both numeric.