sql query and forms

[pasqwal]

I have a query

SELECT champ2 FROM Table2
WHERE champ1=' & [Forms]![form1]![champ1] & ';

sql doesn't understand this parameter
'& [Forms]![form1]![champ1] &'

is there another method ?

 

[tlbroadbent]

Are you trying to query MS SQL Server using an Access or VB form? SQL Server can't understand the query. The form is on the client machine. The query runs on the server. They have no connection but through the query string you send. The client must interpret the value in the form and send that to SQL Server. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[AWithers]

[Forms]![form1]![champ1]

refers to a Microsoft Access form called form1 which has a control on called champ1

you could try saving the following stored procedure to work in a similar manner


CREATE PROCEDURE [sp_YourProcedure]

@champ1 varchar(255)='%'

as
SELECT champ2 FROM Table2
WHERE champ1 like @champ1


Then by executing the stored procedure you, by default, get all records. If you then supply a parameter then you only get the values corresponding to the value supplied

Hope this helps

Andy

 

[SQLSister]

To continue what AWithers sent.

After creating the stored procedure you could run it from Access by creating a pass-through query. We dynamically create the pass though query though the VBA code on the form. an example is below:

intReportNumber = Forms!frmClientReports!cboReportList
numProposalNumber = Forms!frmProposal!subfrmProposalGeneral!ProposalNumber
strSQL = "Execute USP_CustRptSectionsIUO '" & numProposalNumber & "', " & intReportNumber & ""
Set qrySections = db.QueryDefs("ViewRptSectionsIUO&quot
qrySections.sql = strSQL

You may have to play around with the quotes when building the strSQL, that's what we did til we found one that worked.