passing SQL commands as text to variable

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!
 
M

magicdds

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...
 
J

JString

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...
 
J

JString

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...
 
Q

questy

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
 
Q

questy

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
 
J

JString

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report to display SQL code 2
SQL Result to a variable 1
Passing Variable Values from Current db to External Database 3
sql to pass value to variable to pass to text box 4
passing boolean variable value into SQL string 3
Help with SQL insert into 2 one to one relationship tables 1
Using variables as table names in an append sql statemnt 1
Creating string to use as dynamic Crosstab column headings 1

Top