By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,654 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
7 Replies


P: n/a
jv
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" <ju***********@hotmail.com> 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
jv
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" <ju***********@hotmail.com> 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
jv
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" <ju***********@hotmail.com> 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
jv
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.