Only one parameter

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

Guest

I am using subqueries in my reports and I am looking for a way to pass one
parameter entry to the other fields without having to type in the same entry
2 or 3 times to get my results. I need the user to type in a PO Number but
Access request it 3 times because I am using that field a few times in my
report. Any way around this?
 
It sounds like each of your subreports is using its own query, and each
query is prompting for the criterion.

If it isn't feasible to put the criterion into the query that feeds the main
report, consider using a form in which your using enters (or selects) the
criterion, and then modifying each of your (sub-)queries to point to the
form for the criterion.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

Yes I am basing my totals query off my main query that is where it is
getting the second, third parameter. You mentioned something about doing it
from a form. What would be the procedure for this? I also have date parameter
queries in another report also but if I can figure out a way for it to stop
asking me each time it runs a query I could take it from their. Thanks again
 
Create a form that has a control for your parameter value. For example, a
text control into which the user could enter a date.

Modify the query(ies) that feed the report so that, instead of:
[Enter Date]
as a criterion, you have something like:
Forms!YourNewFormName!YourNewTextControlName
as the criterion.

Note: use this SAME criterion for every query that now gets the [Enter
Date] prompt.

Note2: this approach will NOT work if the form isn't open.

Now, back to the form -- add a command button that opens the report.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
That is a good suggestion but I have only one field that has the dates. The
user must enter the first date and second date from the same field to view
the the correct report date range. Any ideas?

Jeff Boyce said:
Create a form that has a control for your parameter value. For example, a
text control into which the user could enter a date.

Modify the query(ies) that feed the report so that, instead of:
[Enter Date]
as a criterion, you have something like:
Forms!YourNewFormName!YourNewTextControlName
as the criterion.

Note: use this SAME criterion for every query that now gets the [Enter
Date] prompt.

Note2: this approach will NOT work if the form isn't open.

Now, back to the form -- add a command button that opens the report.

Regards

Jeff Boyce
Microsoft Office/Access MVP

TKM said:
Jeff,

Yes I am basing my totals query off my main query that is where it is
getting the second, third parameter. You mentioned something about doing
it
from a form. What would be the procedure for this? I also have date
parameter
queries in another report also but if I can figure out a way for it to
stop
asking me each time it runs a query I could take it from their. Thanks
again
Click to expand...
Click to expand...
 
Karl,

Here is the SQL..

SELECT CMPSOWNER_CASE_DATA.CONT_NO AS [Plan ID], CMPSOWNER_CORP.CONAME AS
[Plan Name], CMPSOWNER_FUNDDESC.FD_DESC_CD AS [Fund Descriptor Code],
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT, CMPSOWNER_FUNDDESC.FD_REPORT_NAME
AS [Fund Name], CMPSOWNER_INDIV.SOCSECNUM AS [Participant Tax ID],
CMPSOWNER_HELPER2.HLP_TEXT AS [Transaction Type],
CMPSOWNER_TRANSACT_DETAIL.CYC_DT AS [Process Date],
CMPSOWNER_UNIT_VALUE_1.UVAL_AMT AS [Process Date Price],
[BUY_SELL_UNIT_CT]*Cmpsowner_Unit_Value_1.UVAL_AMT AS [Process Dte Amt],
CMPSOWNER_TRANSACT_DETAIL.EFF_DT AS [Effective Date],
CMPSOWNER_UNIT_VALUE.UVAL_AMT AS [Effective Date Price],
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT AS Shares,
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT AS [Total Shares],
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT*CMPSOWNER_UNIT_VALUE.UVAL_AMT AS
[Plan Proc Date Amt],
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT*CMPSOWNER_UNIT_VALUE.UVAL_AMT AS
[Plan Effec Date Amt],
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT*CMPSOWNER_UNIT_VALUE.UVAL_AMT AS
[Eff Date Amt], CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT AS [Fund Total
Shares],
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT*CMPSOWNER_UNIT_VALUE.UVAL_AMT AS
[Fund Proc Date Amt],
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT*[CMPSOWNER_UNIT_VALUE.UVAL_AMT] AS
[Fund Effec Date Amt]
FROM CMPSOWNER_HELPER2 INNER JOIN ((CMPSOWNER_CORP INNER JOIN
(CMPSOWNER_CASE_DATA INNER JOIN (((((CMPSOWNER_UNITS_BUY_SELL INNER JOIN
CMPSOWNER_TRANSACT_DETAIL ON CMPSOWNER_UNITS_BUY_SELL.TR_REF_NO =
CMPSOWNER_TRANSACT_DETAIL.TR_REF_NO) INNER JOIN CMPSOWNER_UNIT_VALUE ON
CMPSOWNER_TRANSACT_DETAIL.EFF_DT = CMPSOWNER_UNIT_VALUE.EFF_DT) INNER JOIN
CMPSOWNER_UNIT_VALUE AS CMPSOWNER_UNIT_VALUE_1 ON
CMPSOWNER_TRANSACT_DETAIL.CYC_DT = CMPSOWNER_UNIT_VALUE_1.EFF_DT) INNER JOIN
CMPSOWNER_CASE_FUND_DATA ON (CMPSOWNER_TRANSACT_DETAIL.FD_NO =
CMPSOWNER_CASE_FUND_DATA.FD_NO) AND (CMPSOWNER_TRANSACT_DETAIL.PLANID =
CMPSOWNER_CASE_FUND_DATA.PLANID)) INNER JOIN CMPSOWNER_FUNDDESC ON
(CMPSOWNER_UNIT_VALUE_1.FD_DESC_CD = CMPSOWNER_FUNDDESC.FD_DESC_CD) AND
(CMPSOWNER_CASE_FUND_DATA.FD_DESC_CD = CMPSOWNER_FUNDDESC.FD_DESC_CD) AND
(CMPSOWNER_UNIT_VALUE.FD_DESC_CD = CMPSOWNER_FUNDDESC.FD_DESC_CD)) ON
CMPSOWNER_CASE_DATA.PLANID = CMPSOWNER_TRANSACT_DETAIL.PLANID) ON
CMPSOWNER_CORP.NAMEID = CMPSOWNER_CASE_DATA.NAMEID) INNER JOIN
CMPSOWNER_INDIV ON CMPSOWNER_TRANSACT_DETAIL.NAMEID = CMPSOWNER_INDIV.NAMEID)
ON CMPSOWNER_HELPER2.HLP_VALUE = CMPSOWNER_TRANSACT_DETAIL.TR_NO
GROUP BY CMPSOWNER_CASE_DATA.CONT_NO, CMPSOWNER_CORP.CONAME,
CMPSOWNER_FUNDDESC.FD_DESC_CD, CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT,
CMPSOWNER_FUNDDESC.FD_REPORT_NAME, CMPSOWNER_INDIV.SOCSECNUM,
CMPSOWNER_HELPER2.HLP_TEXT, CMPSOWNER_TRANSACT_DETAIL.CYC_DT,
CMPSOWNER_UNIT_VALUE_1.UVAL_AMT, CMPSOWNER_TRANSACT_DETAIL.EFF_DT,
CMPSOWNER_UNIT_VALUE.UVAL_AMT, CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT,
CMPSOWNER_UNITS_BUY_SELL.BYPASS_TRANS_FLAG, CMPSOWNER_HELPER2.HLP_CODE,
CMPSOWNER_TRANSACT_DETAIL.TR_NO, Left([TR_NO],1),
CMPSOWNER_UNIT_VALUE_1.UVAL_AMT, CMPSOWNER_TRANSACT_DETAIL.PLANID
HAVING
(((CMPSOWNER_TRANSACT_DETAIL.CYC_DT)=[Forms]![AccountingReports]![1ST] And
(CMPSOWNER_TRANSACT_DETAIL.CYC_DT)=[Forms]![AccountingReports]![2ND]) AND
((CMPSOWNER_UNITS_BUY_SELL.BYPASS_TRANS_FLAG)="Y") AND
((Left([TR_NO],1))=Right([HLP_CODE],1)))
ORDER BY CMPSOWNER_CASE_DATA.CONT_NO, CMPSOWNER_FUNDDESC.FD_DESC_CD;
 
Did you post the correct SQL? I did not find "PO Number" in it.

TKM said:
Karl,

Here is the SQL..

SELECT CMPSOWNER_CASE_DATA.CONT_NO AS [Plan ID], CMPSOWNER_CORP.CONAME AS
[Plan Name], CMPSOWNER_FUNDDESC.FD_DESC_CD AS [Fund Descriptor Code],
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT, CMPSOWNER_FUNDDESC.FD_REPORT_NAME
AS [Fund Name], CMPSOWNER_INDIV.SOCSECNUM AS [Participant Tax ID],
CMPSOWNER_HELPER2.HLP_TEXT AS [Transaction Type],
CMPSOWNER_TRANSACT_DETAIL.CYC_DT AS [Process Date],
CMPSOWNER_UNIT_VALUE_1.UVAL_AMT AS [Process Date Price],
[BUY_SELL_UNIT_CT]*Cmpsowner_Unit_Value_1.UVAL_AMT AS [Process Dte Amt],
CMPSOWNER_TRANSACT_DETAIL.EFF_DT AS [Effective Date],
CMPSOWNER_UNIT_VALUE.UVAL_AMT AS [Effective Date Price],
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT AS Shares,
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT AS [Total Shares],
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT*CMPSOWNER_UNIT_VALUE.UVAL_AMT AS
[Plan Proc Date Amt],
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT*CMPSOWNER_UNIT_VALUE.UVAL_AMT AS
[Plan Effec Date Amt],
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT*CMPSOWNER_UNIT_VALUE.UVAL_AMT AS
[Eff Date Amt], CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT AS [Fund Total
Shares],
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT*CMPSOWNER_UNIT_VALUE.UVAL_AMT AS
[Fund Proc Date Amt],
CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT*[CMPSOWNER_UNIT_VALUE.UVAL_AMT] AS
[Fund Effec Date Amt]
FROM CMPSOWNER_HELPER2 INNER JOIN ((CMPSOWNER_CORP INNER JOIN
(CMPSOWNER_CASE_DATA INNER JOIN (((((CMPSOWNER_UNITS_BUY_SELL INNER JOIN
CMPSOWNER_TRANSACT_DETAIL ON CMPSOWNER_UNITS_BUY_SELL.TR_REF_NO =
CMPSOWNER_TRANSACT_DETAIL.TR_REF_NO) INNER JOIN CMPSOWNER_UNIT_VALUE ON
CMPSOWNER_TRANSACT_DETAIL.EFF_DT = CMPSOWNER_UNIT_VALUE.EFF_DT) INNER JOIN
CMPSOWNER_UNIT_VALUE AS CMPSOWNER_UNIT_VALUE_1 ON
CMPSOWNER_TRANSACT_DETAIL.CYC_DT = CMPSOWNER_UNIT_VALUE_1.EFF_DT) INNER JOIN
CMPSOWNER_CASE_FUND_DATA ON (CMPSOWNER_TRANSACT_DETAIL.FD_NO =
CMPSOWNER_CASE_FUND_DATA.FD_NO) AND (CMPSOWNER_TRANSACT_DETAIL.PLANID =
CMPSOWNER_CASE_FUND_DATA.PLANID)) INNER JOIN CMPSOWNER_FUNDDESC ON
(CMPSOWNER_UNIT_VALUE_1.FD_DESC_CD = CMPSOWNER_FUNDDESC.FD_DESC_CD) AND
(CMPSOWNER_CASE_FUND_DATA.FD_DESC_CD = CMPSOWNER_FUNDDESC.FD_DESC_CD) AND
(CMPSOWNER_UNIT_VALUE.FD_DESC_CD = CMPSOWNER_FUNDDESC.FD_DESC_CD)) ON
CMPSOWNER_CASE_DATA.PLANID = CMPSOWNER_TRANSACT_DETAIL.PLANID) ON
CMPSOWNER_CORP.NAMEID = CMPSOWNER_CASE_DATA.NAMEID) INNER JOIN
CMPSOWNER_INDIV ON CMPSOWNER_TRANSACT_DETAIL.NAMEID = CMPSOWNER_INDIV.NAMEID)
ON CMPSOWNER_HELPER2.HLP_VALUE = CMPSOWNER_TRANSACT_DETAIL.TR_NO
GROUP BY CMPSOWNER_CASE_DATA.CONT_NO, CMPSOWNER_CORP.CONAME,
CMPSOWNER_FUNDDESC.FD_DESC_CD, CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT,
CMPSOWNER_FUNDDESC.FD_REPORT_NAME, CMPSOWNER_INDIV.SOCSECNUM,
CMPSOWNER_HELPER2.HLP_TEXT, CMPSOWNER_TRANSACT_DETAIL.CYC_DT,
CMPSOWNER_UNIT_VALUE_1.UVAL_AMT, CMPSOWNER_TRANSACT_DETAIL.EFF_DT,
CMPSOWNER_UNIT_VALUE.UVAL_AMT, CMPSOWNER_UNITS_BUY_SELL.BUY_SELL_UNIT_CT,
CMPSOWNER_UNITS_BUY_SELL.BYPASS_TRANS_FLAG, CMPSOWNER_HELPER2.HLP_CODE,
CMPSOWNER_TRANSACT_DETAIL.TR_NO, Left([TR_NO],1),
CMPSOWNER_UNIT_VALUE_1.UVAL_AMT, CMPSOWNER_TRANSACT_DETAIL.PLANID
HAVING
(((CMPSOWNER_TRANSACT_DETAIL.CYC_DT)=[Forms]![AccountingReports]![1ST] And
(CMPSOWNER_TRANSACT_DETAIL.CYC_DT)=[Forms]![AccountingReports]![2ND]) AND
((CMPSOWNER_UNITS_BUY_SELL.BYPASS_TRANS_FLAG)="Y") AND
((Left([TR_NO],1))=Right([HLP_CODE],1)))
ORDER BY CMPSOWNER_CASE_DATA.CONT_NO, CMPSOWNER_FUNDDESC.FD_DESC_CD;


KARL DEWEY said:
Post your SQL.
Click to expand...
Click to expand...
 
One form, two fields (neither bound). Ask for the From: date in the first
field, the To: date in the second.

Use the query selection criteria of
Between Forms!YourForm!YourFrom And Forms!YourForm!YourTo

Regards

Jeff Boyce
Microsoft Office/Access MVP

TKM said:
That is a good suggestion but I have only one field that has the dates.
The
user must enter the first date and second date from the same field to view
the the correct report date range. Any ideas?

Jeff Boyce said:
Create a form that has a control for your parameter value. For example,
a
text control into which the user could enter a date.

Modify the query(ies) that feed the report so that, instead of:
[Enter Date]
as a criterion, you have something like:
Forms!YourNewFormName!YourNewTextControlName
as the criterion.

Note: use this SAME criterion for every query that now gets the [Enter
Date] prompt.

Note2: this approach will NOT work if the form isn't open.

Now, back to the form -- add a command button that opens the report.

Regards

Jeff Boyce
Microsoft Office/Access MVP

TKM said:
Jeff,

Yes I am basing my totals query off my main query that is where it is
getting the second, third parameter. You mentioned something about
doing
it
from a form. What would be the procedure for this? I also have date
parameter
queries in another report also but if I can figure out a way for it to
stop
asking me each time it runs a query I could take it from their. Thanks
again



:

It sounds like each of your subreports is using its own query, and
each
query is prompting for the criterion.

If it isn't feasible to put the criterion into the query that feeds
the
main
report, consider using a form in which your using enters (or selects)
the
criterion, and then modifying each of your (sub-)queries to point to
the
form for the criterion.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I am using subqueries in my reports and I am looking for a way to
pass
one
parameter entry to the other fields without having to type in the
same
entry
2 or 3 times to get my results. I need the user to type in a PO
Number
but
Access request it 3 times because I am using that field a few times
in
my
report. Any way around this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top