Query Expression with IIF and DLOOKUP

[bryan0]

I need to be able to set the criteria of one field of a query based on a table value. I have a table called CRITERIA, with a text field called DATE_CRITERIA. There is one record, with a value of 2013Q3. I will use a form to set this value to other strings.



In my query, I have this in as one of the fields:
PERIOD_1: "SUM(IIF([QUARTER]='" & DLookUp("[DATE_CRITERIA]","CRITERIA") & "',[AMOUNT],0))"

When I run the query, I don't get an error message, but my "PERIOD_1" column just has SUM(IIF([QUARTER]='2013Q3',[AMOUNT],0)) filled the whole way down.

If I enter it like this (without the Dlookup), there's no problem:
PERIOD_1: Sum(IIf([QUARTER]="2013Q3",[AMOUNT],0))

Can someone please let me know what I'm doing wrong? Thanks!

[pbaldy]

The quotes around the whole thing make it literal text. Seeing that as the result was the giveaway.

[June7]

That DLookup will return value from first record. Is that what you want?

PERIOD_1: SUM(IIf([QUARTER]=DLookUp("[DATE_CRITERIA]", "CRITERIA"), [AMOUNT], 0)

[bryan0]

I see what you're saying... However when I ran it like this, I got $0.00 all the way down the PERIOD_1 column, and there should be positive dollar amounts there. Does anything look wrong now?

Before:
PERIOD_1: "SUM(IIF([QUARTER]='" & DLookUp("[DATE_CRITERIA]","CRITERIA") & "',[AMOUNT],0))"

Now:
PERIOD_1: Sum(IIf([QUARTER]='" & DLookUp("[DATE_CRITERIA]","CRITERIA") & "',[AMOUNT],0))

[bryan0]

That DLookup will return value from first record. Is that what you want?

PERIOD_1: SUM(IIf([QUARTER]=DLookUp("[DATE_CRITERIA]", "CRITERIA"), [AMOUNT], 0)

YES!!! Adding the equals symbol did it. Thank you!

[bryan0]

One more question with the same idea. I tried entering multiple criteria in my table, but it just returns zeros. I tried to build off of the IIF statement, so if the QUARTER is 2013Q3 OR 2013Q4 (which would add 2013Q3 and 2013Q4). Here's what I put in the table:
2013Q3' Or [QUARTER]='2013Q4

And here's what's in the query expression:
PERIOD_1: SUM(IIf([QUARTER]=DLookUp("[DATE_CRITERIA]", "CRITERIA"), [AMOUNT], 0)

I've tried everything I can think of. Do you know if this is possible to do as well? Thanks again.

[June7]

I don't understand what you mean by 'put in the table' - put where in the table?

The DLookup still doesn't make sense to me. It will just return value from first record in table Criteria.

[bryan0]

I am trying to build off of a solution that you helped me find last week, here is the thread (I was using countries at the time, but have implemented it for something else):
https://www.accessforums.net/queries...ery-41006.html

This is the original expression, which works perfectly:
PREVIOUS_YTD: Sum(IIf([QUARTER]="2012Q1" Or [QUARTER]="2012Q2" Or [QUARTER]="2012Q3",[AMOUNT],0))

At the end of Q3, we want to compare all of the current year, versus the first 3 quarters of the previous year (which is what the above expression accomplishes). If I can have the expression reference the contents of a table, I can maintain that table with a simple userform, which will in turn set the query expression. This way we won't need to go into the query expression and update it every quarter.