Report to display SQL code

[questy]

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!

 

[Ken Snell \(MVP\)]

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>

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!

 

[krissco]

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 andrun
the report by querying the table. To achieve this i am passing  the sqltext
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!

How are you applying your criteria to the report? I assume you are
using the WhereClause of DoCmd.OpenReport. If so, simply assign
"=[Filter]" to the control source of a text box on your report to see
the criteria.

Additional ideas:

Bind the criteria form to a table. When the user checks/unchecks the
boxes, the value assigned to each control will be saved in a new
record of the table.

Use your current technique with a memo field (more than 255 characters
allowed).
If you still have issues w/ your criteria string reading as SQL, you
might try replacing the SQL keywords prior to inserting the string in
your table:
strInsertStatement = "insert into myTable (sqlCriteria) values ('" &
replace(strSqlCriteria, "SELECT", "") & "')"
In addition to "SELECT" you may want to replace other keywords. . .
I'm not even familiar with the "INCLUDE" keyword you referenced. . .

-Kris