Using DLOOKUP with totals (aggregate) queries?

[Dominic Greco via AccessMonster.com]

I have an totals/aggregate query that looks at a table and returns the total
estimated structural engineering hours. I need to convert that SQL code to a
DLOOKUP in order to use the result in a computation.

Basically I have another totals/aggregate query that looks at a separate
table and returns the total actual structural engineering hours. I plan to
use DLOOKUP to get this result, and use it to find the % Difference between
the value for estimated versus actual hours.

One problem, I can use DLOOKUP with a standard query. But I have NO IDEA how
to use it with a Total (aggregate) query.

DLOOKUP (expresion, domain, [criteria])

I get about this far with it:

Dim intHours As Integer
intHours = DLookup("Mech_Hours", "tlbProjStruct", "proj_num= " & Me!
[txtProj_num] ........

My SQL for the total estimated structural hours looks like this:

SELECT Sum(tlbProjStruct.Struct_Hours) AS SumOfStruct_Hours
FROM tlbProjStruct
GROUP BY tlbProjStruct.proj_num
HAVING (((tlbProjStruct.proj_num)=[Forms]![frmReport_Division]![txtProj_num]))
;

Any help here?

 

[Michel Walsh]

A Dlookup works the same on table and on query, any kind of queries (I mean,
the SELECT type, not the DELETE/UPDATE/INSERT type), but you have to have
the fields present in the table/query.


Open

SELECT Sum(tlbProjStruct.Struct_Hours) AS SumOfStruct_Hours
FROM tlbProjStruct
GROUP BY tlbProjStruct.proj_num
HAVING
(((tlbProjStruct.proj_num)=[Forms]![frmReport_Division]![txtProj_num]))

in data view. See the problem? you only have ONE field, you cannot use
proj_num=... in the DLookup, as in

DLookup("Mech_Hours", "tlbProjStruct", "proj_num= " & Me![txtProj_num])


since that field does NOT exists. Nether can you use Mech_Hours, since the
only field is now called SumOfStruct_Hours. So, just use:

DLookup( "SumOfStruct_Hours", "your Saved Query Name Here ")


or


DSUM("Struct_Hours", "tlbProjStruct", "proj_num= " & Me![txtProj_num] )


since now, you are using the table, not the saved query, and the table owns
fields struct_hours and proj_num.


Vanderghast, Access MVP

I have an totals/aggregate query that looks at a table and returns the
total
estimated structural engineering hours. I need to convert that SQL code to
a
DLOOKUP in order to use the result in a computation.

Basically I have another totals/aggregate query that looks at a separate
table and returns the total actual structural engineering hours. I plan to
use DLOOKUP to get this result, and use it to find the % Difference
between
the value for estimated versus actual hours.

One problem, I can use DLOOKUP with a standard query. But I have NO IDEA
how
to use it with a Total (aggregate) query.

DLOOKUP (expresion, domain, [criteria])

I get about this far with it:

Dim intHours As Integer
intHours = DLookup("Mech_Hours", "tlbProjStruct", "proj_num= " & Me!
[txtProj_num] ........

My SQL for the total estimated structural hours looks like this:

SELECT Sum(tlbProjStruct.Struct_Hours) AS SumOfStruct_Hours
FROM tlbProjStruct
GROUP BY tlbProjStruct.proj_num
HAVING
(((tlbProjStruct.proj_num)=[Forms]![frmReport_Division]![txtProj_num]))
;

Any help here?

 

[Dominic Greco via AccessMonster.com]

You tip on using the query name in DLOOKUP worked perfectly! I was able to
get the number and send it right to the correct textbox. In addition, I was
able to use it in my calc just as I had wanted. Thank you very much!

A Dlookup works the same on table and on query, any kind of queries (I mean,
the SELECT type, not the DELETE/UPDATE/INSERT type), but you have to have
the fields present in the table/query.

Open

SELECT Sum(tlbProjStruct.Struct_Hours) AS SumOfStruct_Hours
FROM tlbProjStruct
GROUP BY tlbProjStruct.proj_num
HAVING
(((tlbProjStruct.proj_num)=[Forms]![frmReport_Division]![txtProj_num]))

in data view. See the problem? you only have ONE field, you cannot use
proj_num=... in the DLookup, as in

DLookup("Mech_Hours", "tlbProjStruct", "proj_num= " & Me![txtProj_num])

since that field does NOT exists. Nether can you use Mech_Hours, since the
only field is now called SumOfStruct_Hours. So, just use:

DLookup( "SumOfStruct_Hours", "your Saved Query Name Here ")

or

DSUM("Struct_Hours", "tlbProjStruct", "proj_num= " & Me![txtProj_num] )

since now, you are using the table, not the saved query, and the table owns
fields struct_hours and proj_num.

Vanderghast, Access MVP

I have an totals/aggregate query that looks at a table and returns the
total

[quoted text clipped - 30 lines]

Any help here?