If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using DLOOKUP with totals (aggregate) queries?
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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200807/1 |
#2
|
|||
|
|||
Using DLOOKUP with totals (aggregate) queries?
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 "Dominic Greco via AccessMonster.com" u43566@uwe wrote in message news:86d735b709898@uwe... 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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200807/1 |
#3
|
|||
|
|||
Using DLOOKUP with totals (aggregate) queries?
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! Michel Walsh wrote: 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? -- Thanks, Dominic Greco Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200807/1 |
Thread Tools | |
Display Modes | |
|
|