DLookUp using DateAdd function

[bmhuettinger]

Good evening,
I have 3 tables of monthly usage data that is updated daily using "rolling 12 month" criteria. I'd like a summary form that shows MTD, as well as the previous 11 months usage for each of the 3 categories (tables). Since the month names will change every month (rolling 12), I need to use the dateadd function but I don't know how to use it properly with a dlookup. (I know the expression works because it returns the correct results if I use it as criteria in the query).

=Nz(DLookUp("[SumofLbs]","qry_NonProgramUsageSummary","[mth] =" & Month(DateAdd("m",-1,Date()))))

What am I doing wrong? I'm guessing I'm missing "" or & or something....

Thank you in advance for your help!

 

[Minty]

I think it should be

= Nz(DLookUp("[SumofLbs]","qry_NonProgramUsageSummary","[mth] =Month(DateAdd('m',-1,Date()))"),"NullReplacementGoeshere")

 

[bmhuettinger]

Thank you, Minty. Unfortunately, I'm still getting an error message. I even tried it without the NZ()

 

[CJ_London]

what is the error message?

 

[bmhuettinger]

Pardon me. I should have said I'm getting #error results.

 

[CJ_London]

so this is a control on a form or report?

 

[bmhuettinger]

For now, a report. But I would like to be able to use this for form controls, as well.

 

[CJ_London]

on the face of it, there is nothing wrong with your code

So that suggests your query qry_NonProgramUsageSummary is misspelt or does not contain fields called SumofLbs and/or Mth

or perhaps mth is not a numeric field

Suggest leave the Nz function off until you have it resolved - if the dlookup returns null, it should not matter

 

[bmhuettinger]

Good morning,
Yes, I agree re: the nz(). I took it off shortly after my initial post here for simplicity's sake.
I've doubled-checked my fields and properties. The only thing that I could think that may possibly be an issue is that [mth] is actually a calculated Month() field itself and may not be recognized as numeric so I wrapped it in Val().
That didn't help either, unfortunately.

Here's my query:

SELECT [qry_(Adjusted)ProgramUsage].MonthAndYear, Sum([qry_(Adjusted)ProgramUsage].SumOfLbs) AS SumOfSumOfLbs, Month([monthandyear]) AS Mth
FROM [qry_(Adjusted)ProgramUsage]
GROUP BY [qry_(Adjusted)ProgramUsage].MonthAndYear, Month([monthandyear]);

And here's my report control code:

=DLookUp("[Sumoflbs]","qry_NonProgramUsageSummary","[mth] =" & Month(DateAdd("m",-1,Date())))

 

[Minty]

Are you sure that query works ?

Month([monthandyear]) would make me think that [monthandyear] is a text field, also having a field name exactly the same as the source query looks decidedly weird / waiting for a problem to happen?

 

[Pat Hartman]

I think Minty may have identified the problem (Month() only works on a real date data type) but I also found reading your query to be very confusing because of the () in the object names. Best practice is to use only letters (upper/lower case), numbers, and the underscore. Never use special characters or embedded spaces. Also avoid using property and function names as column names.

 

[bmhuettinger]

Thank you for the replies...my query works fine. I use it for several other reports (with specific month criteria) with no issues. Aside from the "(Adjusted)" in the query name, all of the fields and formulas were taking from other solutions on this site. (Also, I don't have a field name the same as the source query??)
As I stated earlier, the expression that i'm trying to use in dlookup works perfectly fine as criteria in the query. I'm simply trying to avoid creating 24 separate queries and look THAT data up for my report.
But if I have to, which it seems I may, then I will.
I really appreciate everyone's help thus far....

 

[Minty]

I think without you uploading an sample of the data and preferably a stripped down database with enough data / queries forms to replicate the issue we're not going to get much further.

Try zipping a sample for us to play with.

 

[bmhuettinger]

So I feel dumb...after taking the time to create 24 separate queries of data (because I'd already spent 3 days trying to solve the issue), I inadvertently discovered that the query field name was actually "sumofSUMOFlbs" (because the query was a totals query of a subtotals query). Unbelievable. The good news is that the original reply from Minty worked. Problem solved.
Thank you both for your help and suggestions.