Prev: Automated way of adding suffix on dates? i.e. th, st,rd
Next: you must define a sort field or expression for group header or
From: krissco on 4 Jul 2008 12:15
On Jul 3, 7:56 pm, questy <que...(a)discussions.microsoft.com> wrote:
> 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!


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
 | 
Pages: 1
Prev: Automated way of adding suffix on dates? i.e. th, st,rd
Next: you must define a sort field or expression for group header or