Another Dlookup problem

[Tony Williams]

Does DLookup work in an expression in a query? I have this expression
[txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlab
el]= [txtqtr1]"))
Which works fine as a calculated control on a form but when I try to use it
in a query as an expression I don't get any result. I want to use the Sum of
the result in a query on which I will base a report.

TIA
Tony

[Ed Robichaud]

You can't have an "=" in your criteria, as your criteria is only one side of
the equation. If [txtqtr1] is the field/query column, that you wish to
filter on, then use:

[txtDomfacsoleqtr]-DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabel]")

as the criteria.
-Ed
"Tony Williams" <[e-mail]> wrote in message
news:cu**********@titan.btinternet.com...

Does DLookup work in an expression in a query? I have this expression
[txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlab
el]= [txtqtr1]"))
Which works fine as a calculated control on a form but when I try to use
it
in a query as an expression I don't get any result. I want to use the Sum
of
the result in a query on which I will base a report.

TIA
Tony

[Tanis]

Tony Williams wrote:

Does DLookup work in an expression in a query? I have this expression
[txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlab el]= [txtqtr1]"))
Which works fine as a calculated control on a form but when I try to use it in a query as an expression I don't get any result. I want to use the Sum of the result in a query on which I will base a report.

TIA
Tony

I would use a calculated control in the query to hold the result of
your DLookup and then use that as part of your sum. You could use
something like this in the query grid: Result:DLookup(Whatever) then
SumForReport: [txtWhatever]-[Result]

[altacct]

Tony,

I don't know if it would work, but far easier, and more standard, is to
just include the related table, and the field from that table. That's
the great thing about queries, you can include more than one table.

Jeremy

[Tony Williams]

Thanks Ed. What I'm trying to do is to get the expression in the query to
calculate the difference between the value of txtDomfacsoleqtr in the
present quarter and the value in the previous quarter, hence the reason why
I'm using the DLookup to determine the value in the previous quarter,
txtMonthlabel is the current quarter and txtqtr1 is the previous quarter
which is calculated in another query as DateAdd("m",-3,[txtmonthlabel]) I've
joined the two queries together
I'm actually trying to create a report from a query
I've modified my expression and this is what it now looks like
[Sum of txtDomfactotqtr]-(DLookUp("[Sum of
txtDomfactotqtr]","qryFDASalesv3v2","[txtmonthlabel] = #" & [txtqtr1] &
"#"))
The report is based on a query qryFDASalesv3v2
The controls Sum of txtDomfactotqtr and txtqtr1 are in the query
The control Sum of txtDomfactotqtr is an expression in the query which is
Sum([txtDomfacsoleqtr]+[txtDomfacpartqtr]) which are fields in my table
The control txtqtr1 is an expression in the query which is
DateAdd("m",-3,[txtmonthlabel])
The control txtmonthlabel is a control based on a field in the table on
which the query is based.

That any help?
Tony

"Ed Robichaud" <ed*********@wdn.com> wrote in message
news:W6*******************@monger.newsread.com...

You can't have an "=" in your criteria, as your criteria is only one side of the equation. If [txtqtr1] is the field/query column, that you wish to
filter on, then use:

[txtDomfacsoleqtr]-DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlabe
l]")
as the criteria.
-Ed
"Tony Williams" <[e-mail]> wrote in message
news:cu**********@titan.btinternet.com...

Does DLookup work in an expression in a query? I have this expression
[txtDomfacsoleqtr]-(DLookUp("[txtDomfacsoleqtr]","tblmaintabs","[txtMonthlab el]= [txtqtr1]"))
Which works fine as a calculated control on a form but when I try to use
it
in a query as an expression I don't get any result. I want to use the Sum of
the result in a query on which I will base a report.

TIA
Tony

[Tony Williams]

Thanks Jeremy I've done something like that would you have a look at my
reply to Ed?
Thanks for your input
Tony
<al*****@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Tony,

I don't know if it would work, but far easier, and more standard, is to
just include the related table, and the field from that table. That's
the great thing about queries, you can include more than one table.

Jeremy

[altacct]

Tony,

I've never done anything like that (comparing one record to another in
a single query). But I've seen people write about it here. I think
there may be an article on it at The Access Web
(http://www.mvps.org/access/), though I'm not sure. I would have a look
there, and if you don't find anything, try searching google's archives
for things like "compare one record to another" and various
modifications of that.

Sorry I can't help more.

Jeremy Wallace

[Tony Williams]

Thanks Jeremy I'll look at that site.
Regards
Tony
<al*****@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Tony,

I've never done anything like that (comparing one record to another in
a single query). But I've seen people write about it here. I think
there may be an article on it at The Access Web
(http://www.mvps.org/access/), though I'm not sure. I would have a look
there, and if you don't find anything, try searching google's archives
for things like "compare one record to another" and various
modifications of that.

Sorry I can't help more.

Jeremy Wallace