passing SQL commands as text to variable

  • Thread starter Thread starter questy
  • Start date Start date
Q

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
Click to expand...
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!
 
Instead of storing the strings in a table, try this:

On click of the button of the form:
Dim SQLstring as string

If checkbox1 = -1 then SQLstring = SQLstring & "parameters for this part of
the SQL"

If checkbox2 = -1 then SQLstring = SQLstring & "parameters for this part of
the SQL"

If checkbox3 = -1 then SQLstring = SQLstring & "parameters for this part of
the SQL"

...........etc.

Once your SQLstring has been created, you can use the DOCMD.OPENREPORT to
open a report with SQLstring as the WHERE part of the openreport statement.

Hope this helps.
Mark




questy said:
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
Click to expand...
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!
Click to expand...
 
I don't know if I'm understanding your issue correctly but it looks like
you're missing the field names in the table where you want your strings to
go.

here's a chunk of code that I wrote to solve a similar problem

sqlstr = "INSERT INTO [TableName] ([Field1],[Field2],[Field3]) VALUES ("
sqlstr = sqlstr & Chr(34) & sqlField1value & Chr(34) & ", " .......

note the use of chr(34)... it returns the " character.

Also, text fields are limited to 255 characters but you can up that limit by
changing the datatype to 'memo'.

questy said:
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
Click to expand...
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!
Click to expand...
 
Another issue might be that the quote characters contained in your sql string
variables are confusing the database engine. If that's the case then you
might want to try elimiminating your SELECT INTO statement altogether. I
suppose you could create an array of hidden textbox controls that are linked
to the table you want to update instead. When a user checks one of the
boxes, the value of the corresponding textbox could be altered with code like
this:

Private Sub Check1_Click()
If Check1.Value = -1 'value when checked
Textbox1.Value = yoursqlstring
Else
Textbox1.Value = ""
End Sub



questy said:
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
Click to expand...
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!
Click to expand...
 
Thanks All for your help.

I have stored the Text in string like magicdds has suggested.

I have written the following code
DoCmd.OpenReport stDocName, acPreview, , SQLstring

But This opens an empty report without the value in SQLstring. Am I missing
something in the command. ? Thanks
 
Thanks All For your help. I have found a solution for it. I have put the sql
code for each check box in a table. Ran the report based on thsi table. I
have constructed a where string for the Report based on the user selection.


ThankYou
 
No problem. Thanks for bringing it up because it gave me a great idea for my
own dynamic sql... For complicated sql statements like the ones you're
dealing with, it's possible to replace quotes with a unique expression like
qt or some other key term. Before the statement is executed, an algorithm
could run through the string and replace the keyword with an actual quote
character.
 
Back
Top