USE OF QUERY BUILT IN SQL IN A CALCULATED CONTROL FIELD .

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please Help!! I am tearing my hair out trying to understand how I can use
an sql query in a sub form but using the filter criteria shown on the
current subform. I have tried creating a calculated control and used a
version of the script below.

SELECT
Sum(STUDY_LEAVE_RECS.COUR_ACT_FEE)+Sum([STUDY_LEAVE_RECS].[COUR_ACT_TRAVEL])+Sum([STUDY_LEAVE_RECS].[COUR_ACT_SUBSISTANCE])+Sum([STUDY_LEAVE_RECS].[COUR_ACT_OTHER_EXPEN]) AS SUM
FROM STUDY_LEAVE_RECS
GROUP BY STUDY_LEAVE_RECS.BUDGET_HOLDER_ID
HAVING (((STUDY_LEAVE_RECS.BUDGET_HOLDER_ID)=2));

I tried replacing the filter

(((STUDY_LEAVE_RECS.BUDGET_HOLDER_ID)=2));

with ( STUDY_LEAVE_RECS.BUDGET_HOLDER_ID)=
[study_leave_recs].[Form]![budget_holder_id]"

and also

with (STUDY_LEAVE_RECS.BUDGET_HOLDER_ID)= [me].budget_holder_id]"

This is an example I want to use, but there are other uses if I can figure
it out. I have had some success with dsum but would like to know how to do
it this way as well. Many thanks!
 
First, since Sum is a reserved word, you shouldn't use it as the name of the
output field. If you are going to, then enclose it in brackets to prevent
problems, [Sum]. Next, where is this located. Is this the SQL in a query or
is it SQL that you're building in VBA code? The " in the SQL is what has me
curious about this.

If this is the SQL in a query, then
with ( STUDY_LEAVE_RECS.BUDGET_HOLDER_ID)=
[study_leave_recs].[Form]![budget_holder_id]"
Click to expand...

would probably be

(STUDY_LEAVE_RECS.BUDGET_HOLDER_ID)=
[Forms]![NameOfMainForm]![study_leave_recs].[Form]![budget_holder_id]

where [study_leave_recs] is the name of the subform control on the main
form. This may or may not be the same as the name of the form being used as
the subform.

--
Wayne Morgan
Microsoft Access MVP


PETER said:
Please Help!! I am tearing my hair out trying to understand how I can
use
an sql query in a sub form but using the filter criteria shown on the
current subform. I have tried creating a calculated control and used a
version of the script below.

SELECT
Sum(STUDY_LEAVE_RECS.COUR_ACT_FEE)+Sum([STUDY_LEAVE_RECS].[COUR_ACT_TRAVEL])+Sum([STUDY_LEAVE_RECS].[COUR_ACT_SUBSISTANCE])+Sum([STUDY_LEAVE_RECS].[COUR_ACT_OTHER_EXPEN])
AS SUM
FROM STUDY_LEAVE_RECS
GROUP BY STUDY_LEAVE_RECS.BUDGET_HOLDER_ID
HAVING (((STUDY_LEAVE_RECS.BUDGET_HOLDER_ID)=2));

I tried replacing the filter

(((STUDY_LEAVE_RECS.BUDGET_HOLDER_ID)=2));

with ( STUDY_LEAVE_RECS.BUDGET_HOLDER_ID)=
[study_leave_recs].[Form]![budget_holder_id]"

and also

with (STUDY_LEAVE_RECS.BUDGET_HOLDER_ID)= [me].budget_holder_id]"

This is an example I want to use, but there are other uses if I can figure
it out. I have had some success with dsum but would like to know how to
do
it this way as well. Many thanks!
Click to expand...
 
On rereading your initial post, are you placing the SQL in the Control
Source of a textbox? If so, it won't work. It will work as the Row Source of
a listbox, which would probably be better if more than one record is going
to be returned.

Please provide a more complete explanation of what you're trying to do.
 
Thanks twice over Wayne. Yes I was attempting to use the SQL as the control
source of a text box. I am trying to see how I can return the correct total
spend from a an SQL built query for the customer shown on the current form.
I know how to write a SQL query for a pre-specified customer id eg

SELECT
Sum(STUDY_LEAVE_RECS.COUR_ACT_FEE)+Sum([STUDY_LEAVE_RECS].[COUR_ACT_TRAVEL])+Sum([STUDY_LEAVE_RECS].[COUR_ACT_SUBSISTANCE])+Sum([STUDY_LEAVE_RECS].[COUR_ACT_OTHER_EXPEN])
AS SUM
FROM STUDY_LEAVE_RECS
GROUP BY STUDY_LEAVE_RECS.BUDGET_HOLDER_ID
HAVING (((STUDY_LEAVE_RECS.BUDGET_HOLDER_ID)=2));


What I am trying to find out is, is it possible to use a version of that SQL
in the form to display the total for the current customer in the form - and
if so, then how!

I guess my ultimate aim is to see how I can use sql within forms.


Thanks again
 
First, you may want to check out the Domain Aggregate Functions, such as
DSum, DLookup, and DCount. They can be used in the Control Source. If those
won't do what you want, then you could open your SELECT statement as a
recordset in code, then get the value of the field and place it in the
textbox.

All the examples of the functions show only one criteria being used, but
they will accept AND and OR statements in their criteria if you need them.

--
Wayne Morgan
MS Access MVP


PETER said:
Thanks twice over Wayne. Yes I was attempting to use the SQL as the
control
source of a text box. I am trying to see how I can return the correct
total
spend from a an SQL built query for the customer shown on the current
form.
I know how to write a SQL query for a pre-specified customer id eg

SELECT
Sum(STUDY_LEAVE_RECS.COUR_ACT_FEE)+Sum([STUDY_LEAVE_RECS].[COUR_ACT_TRAVEL])+Sum([STUDY_LEAVE_RECS].[COUR_ACT_SUBSISTANCE])+Sum([STUDY_LEAVE_RECS].[COUR_ACT_OTHER_EXPEN])
AS SUM
FROM STUDY_LEAVE_RECS
GROUP BY STUDY_LEAVE_RECS.BUDGET_HOLDER_ID
HAVING (((STUDY_LEAVE_RECS.BUDGET_HOLDER_ID)=2));


What I am trying to find out is, is it possible to use a version of that
SQL
in the form to display the total for the current customer in the form -
and
if so, then how!

I guess my ultimate aim is to see how I can use sql within forms.


Thanks again


Wayne Morgan said:
On rereading your initial post, are you placing the SQL in the Control
Source of a textbox? If so, it won't work. It will work as the Row Source
of
a listbox, which would probably be better if more than one record is
going
to be returned.

Please provide a more complete explanation of what you're trying to do.
Click to expand...
Click to expand...
 
Back
Top