434,654 Members | 1,840 Online
Need help? Post your question and get tips & solutions from a community of 434,654 IT Pros & Developers. It's quick & easy.

# Another DLookup problem

 P: n/a I'm trying to calculate for a report the previous year to date value of a control I'm using this: =DLookUp("[Sum of txtDomfactot]","qryFDASalesV2","Year([txtmonthlabel])=Year([txtprevyr])") The report is based on a query "qryFDASalesV2" and the value I'm trying to calculate is the year to date total of "txtdomfactot" The control "txtmonthlabel" is the current month as a mmmm yyyy date/time field and "txtprevyr", also formatted mmmm yyyy, is a calculated control which gives me the previous year calculated using: =DateAdd("m",-12,[txtmonthlabel]) But I don't get the correct value showing. It does return a figure but it doesn't equate with the total caluculated manually. What am I doing wrong? TIA Tony Williams Nov 13 '05 #1
7 Replies

 P: n/a Unless I'm missing something, it seems that the problem is the criteria expression "Year([txtmonthlabel])=Year([txtprevyr])". This would only return the total for the current calendar year. Nov 13 '05 #2

 P: n/a Hi Julie, the control txtprevyr is a calculated control to give me a date 12 months ago from =DateAdd("m",-12,[txtmonthlabel]) so what I thought the DLookup was doing was finding the sum of txtdomfactot where the year in the control txtmonthlabel was 12 months ago. Am I getting this wrong? Tony "jv" wrote in message news:11**********************@z14g2000cwz.googlegr oups.com... Unless I'm missing something, it seems that the problem is the criteria expression "Year([txtmonthlabel])=Year([txtprevyr])". This would only return the total for the current calendar year. Nov 13 '05 #3

 P: n/a I guess I don't know whether txtmonthlabel and txtprevyr are both controls on a form or query fields. It seems that they are both controls on a form. Does your query have a date field? Use that field in the criteria. As in: DLookUp("[Sum of txtDomfactot]","qryFDASalesV2","year([DateFieldName])=Year(" & [txtprevyr] & ")") Nov 13 '05 #4

 P: n/a Both are controls on my report txtmonthlabel is a control based on a field of the same name in my table, txtprevyr is a calculated control on my report. Both are Date/time controls with the format mmmm yyyy. I 've put a control on the report with this =DLookUp("[txtDomfactot]","tblmaintabs Query1","year([txtmonthlabel])=Year(" & [txtprevyr] & ")") but I don't get any value shown? The report is based on the query "tblmaintabs Query1" Any suggestions Julie? Thanks Tony "jv" wrote in message news:11**********************@f14g2000cwb.googlegr oups.com... I guess I don't know whether txtmonthlabel and txtprevyr are both controls on a form or query fields. It seems that they are both controls on a form. Does your query have a date field? Use that field in the criteria. As in: DLookUp("[Sum of txtDomfactot]","qryFDASalesV2","year([DateFieldName])=Year(" & [txtprevyr] & ")") Nov 13 '05 #5

 P: n/a Which query contains the calculated previous year-to-date figure ( qryFDASalesV2 or [tblmaintabs Query1])? That query is the one that needs to be the domain parameter of your DLookUp function. Are txtMonthLabel and txtDomfactot valid fields in that specify query? Give this a try: =DLookUp("[txtDomfactot]","qryFDASalesV2 ","Year([txtMonthLabel])=" & Year([txtprevyr])) Nov 13 '05 #6

 P: n/a Thanks Julie, The query is qryFDASalesV2, sorry got a bit confused here with my queries, yes the two fields are valid fields in the query. I've tried your suggestion but just get #Error Thanks for your patience but any other suggestions? Tony "jv" wrote in message news:11*********************@f14g2000cwb.googlegro ups.com... Which query contains the calculated previous year-to-date figure ( qryFDASalesV2 or [tblmaintabs Query1])? That query is the one that needs to be the domain parameter of your DLookUp function. Are txtMonthLabel and txtDomfactot valid fields in that specify query? Give this a try: =DLookUp("[txtDomfactot]","qryFDASalesV2 ","Year([txtMonthLabel])=" & Year([txtprevyr])) Nov 13 '05 #7

 P: n/a A DLookup is similar to a select statement in a query. I would suggest that create a new query in the query designer and select qryFDASalesV2 as the table name, the select txtDomfactot for the first column, Year([txtMonthLabel]) as the second column and put 2004 in the second column's criteria and see it that works ... and then figure out the problem from there. Nov 13 '05 #8

### This discussion thread is closed

Replies have been disabled for this discussion.