Report to display SQL code
in [Reports]
|
From: questy on 3 Jul 2008 23:56 Hi There I have a form with 17 checkbox options for the user to select. Once the user selects the checkboxes he then clicks a button that is suppose to run a report. based on the options selected this report is to display sql syntax that goes with that particular option. This report is to assist our Reporter in extracting data by storing the code that goes with these extra restrictions. I am newbie to forms and have tried few ways in doing the task with no success. Right now, I am trying to insert the SQL code as text into a table and run the report by querying the table. To achieve this i am passing the sql text to 17 variables with quotations around the code but it is still reading the code as a SQL statement.!!! The idea is to use 17 variables in the INSERT statement as below Dim strSQL_TOTAL_GIVING as string (declared 17 variables like this) Assigned the SQL code to 17 variables as below strSQL_TOTAL_GIVING = " Include (entity.id_number not in (select gift.gift_donor_id from gift where gift.gift_account like 'Y%' and gift.gift_transaction_type not like '[BFISM2ZR]%' and gift.gift_receipt_date >='07/01/2006' group by gift.gift_donor_id having sum(gift.gift_associated_amount) >= 20000 )) and" Ran the INSERT as below SQL = "INSERT INTO SQLSyntax values ( '" & strJobNo & "','" & strSQL_AF_SCredit & "','" & strSQL_AF_ORG & "','" & strSQL_AF_USUK & "','" & strSQL_AF_PLEDGES & "','" & strSQL_AF_SENATE & "','" & strSQL_AF_MEDICAL & "','" & strSQL_AF_SmallMEDFNDN & "','" & strSQL_AF_MGA & "','" & strSQL_AF_CON & "','" & strSQL_AF_SCMFNDN & "','" & strSQL_AF_DONORS & "','" & strSQL_TOTAL_GIVING & "','" & strSQL_CON_PROSPECTS & "','" & strSQL_CAMANDHELY & "','" & strSQL_CLIENTGRP & "');" DoCmd.RunSQL SQL Is there anything I can add to the code(other than the quotations "") that will supress it from reading it as a SQL ?? And even if i am able to store the code in the variables, Can I actually store a very long text (around 500 characters) in a table column??? If the above both are not doable. Is there any other easier way of running this report. ??? Your help is much appreciated!
From: Ken Snell (MVP) on 4 Jul 2008 07:39 Am I correct that you want the report to show the SQL statement that was used to generate the report, i.e., its RecordSource? If yes, just put a textbox on the report, set its CanGrow property to Yes, and set its ControlSource to this expression: =[RecordSource] -- Ken Snell <MS ACCESS MVP> "questy" <questy(a)discussions.microsoft.com> wrote in message news:EFDB00EC-F179-4D8B-BEB0-DA208AABAF01(a)microsoft.com... > Hi There > > I have a form with 17 checkbox options for the user to select. Once the > user > selects the checkboxes he then clicks a button that is suppose to run a > report. based on the options selected this report is to display sql > syntax > that goes with that particular option. This report is to assist our > Reporter > in extracting data by storing the code that goes with these extra > restrictions. > > I am newbie to forms and have tried few ways in doing the task with no > success. > > Right now, I am trying to insert the SQL code as text into a table and > run > the report by querying the table. To achieve this i am passing the sql > text > to 17 variables with quotations around the code but it is still reading > the > code as a SQL statement.!!! The idea is to use 17 variables in the INSERT > statement as below > > > Dim strSQL_TOTAL_GIVING as string (declared 17 variables like this) > > Assigned the SQL code to 17 variables as below > > > strSQL_TOTAL_GIVING = " Include (entity.id_number not in (select > gift.gift_donor_id from gift where gift.gift_account like 'Y%' and > gift.gift_transaction_type not like '[BFISM2ZR]%' and > gift.gift_receipt_date >>='07/01/2006' group by gift.gift_donor_id having > sum(gift.gift_associated_amount) >= 20000 )) and" > > Ran the INSERT as below > > SQL = "INSERT INTO SQLSyntax values ( '" & strJobNo & "','" & > strSQL_AF_SCredit & "','" & strSQL_AF_ORG & "','" & strSQL_AF_USUK & "','" > & > strSQL_AF_PLEDGES & "','" & strSQL_AF_SENATE & "','" & strSQL_AF_MEDICAL & > "','" & strSQL_AF_SmallMEDFNDN & "','" & strSQL_AF_MGA & "','" & > strSQL_AF_CON & "','" & strSQL_AF_SCMFNDN & "','" & strSQL_AF_DONORS & > "','" > & strSQL_TOTAL_GIVING & "','" & strSQL_CON_PROSPECTS & "','" & > strSQL_CAMANDHELY & "','" & strSQL_CLIENTGRP & "');" > > > DoCmd.RunSQL SQL > > Is there anything I can add to the code(other than the quotations "") that > will supress it from reading it as a SQL ?? > > And even if i am able to store the code in the variables, Can I actually > store a very long text (around 500 characters) in a table column??? > > If the above both are not doable. Is there any other easier way of running > this report. ??? > > Your help is much appreciated! >
|
Pages: 1 Prev: Case statement Problem Next: saving reports no more possible |