DMAx Question for Report - DMax <= Dtae

[crimedog]

I have a report with a TextBox formatted as Short Date - the TextBox [Txt12] contains:

=DateSerial(Year(Date()),12,1)

in the Data Control Source
for My DMAX In another TextBox I have:

=DMax("[Revquota]","[Qury1TestQuotaby5]","[DateOfChange]<=" & [Txt12])

There is a Record for [DateOfChange] @ 11/1/2014 this is the record that should be returning the [RexQuota] - the Report shows blank

I used Month() to return the Month Numbers - and I get a result, but the problem is: there will be more than one year of data - so in the previous example, if there was a record on 12/1/2013 I would not want that returned.
([DateOfChange] is a Date/hour format in the Table)

Hope this is clear - I will of course have a follow up after this.

[June7]

What data do you want to consider - all for a specific year?

=DMax("[Revquota]", "[Qury1TestQuotaby5]", "Year([DateOfChange])=" & Year(Date())

[crimedog]

the last date that is before the date in Txt12 - there is a summary for the year with January - December ([Txt1]-[Txt2]) there is not a query entry for each month - just when the info changes. so it needs to return the data for the last time the information changed (before the date in Txt1 etc...)

[June7]

You don't want the maximum Revquota, you want the Revquota value from the latest date prior to 12/1/2014.

=DLookup("[Revquota]", "[Qury1TestQuotaby5]", "[DateOfChange]=#" & DMax("DateOfChange", "[Qury1TestQuotaby5]", "DateOfChange<=#" & [Txt12] & "#") & "#")

[crimedog]

Man that looks right. I copied and pasted - it kicked out error, think I fixed it

=DLookUp("[Revquota]","[Qury1TestQuotaby5]","[DateOfChange]=" & DMax("[DateOfChange]","[Qury1TestQuotaby5]","[DateOfChange]<=#" & [Txt12] & "#"))

Now it just shows blank. [Txt12]= 12/1/2014 there is a [Revquota] with [DateOfChange]=11/1/2014 - so it should have returned that entry (75,000)

[June7]

Looks like you read my post before my last edit. Check it again.

[crimedog]

Working like a charm! One more question, now to Sum all the entries that are in month & year of [Txt12]

=DSum("[RepRevenue]","[QuryTestRepDeals]"," Format([OrderDate], 'mm/yyyy') = " & Format([Txt12],"mm/yyyy"))

What am I missing? What was the purpose of the #'s from before?

Thanks for the help - the lookup is just what I wanted.

[June7]

Text fields require apostrophe delimiters for parameters, data/time requires # character, number doesn't use any.

Format function returns a string value.

=DSum("[RepRevenue]","[QuryTestRepDeals]", "Format([OrderDate], 'mm/yyyy') = '" & Format([Txt12],"mm/yyyy") & "'")

[crimedog]

Perfect - Thanks again!