Parameter syntax error

[Qtip23]

Hello all,

I am at a lost with troubleshooting my SQL string. I am receiving a “Syntax error in parameter clause”.

I do not think I have exceeded the number of characters for a SQL string.

Also ensured the semicolon after the Parameters line and inserted with “& _” on each end within my VBA code.

All parameters are text. When I tried in Access' Query tool without parameters, the query runs.

Do you see where I went wrong?

Thanks.

 
strSQL = "PARAMETERS [Forms]![frmB]![cboB] Text ( 255 ), [Forms]![frmB]![lstT] Text ( 255 ), [Forms]![frmB]![lstSolution] Text ( 255 ), [Forms]![frmB]![lstReporting] Text ( 255 ), [Forms]![frmB]![lstReporting] Text ( 255 ), [Forms]![frmB]![lstProjType] Text ( 255 ), [Forms]![frmB]![lstS] Text ( 255 ), [Forms]![frmB]![lstArea] Text ( 255 ); " & _
        "SELECT tbMastfinal.Funding, tbGP.GrProgram, tbT.TCLIDname, tbSA.SAName, tbStates.StateName, tbRP.RPname, tbProjectTypes.ProjectTypeName, tbRegions.RegionName, tbArea.AName  " & _
        "FROM tbT INNER JOIN (tbProjectTypes INNER JOIN (tbGP INNER JOIN (tbRegions INNER JOIN (tbArea INNER JOIN (tbSA INNER JOIN (tbRP INNER JOIN (tbStates INNER JOIN tbMastfinal ON tbStates.StateID = tbMastfinal.StateID) ON tbRP.RP_ID = tbMastfinal.RP_ID) ON tbSA.SA_ID = tbMastfinal.SA_ID) ON tbArea.AreaID = tbMastfinal.AreaID) ON tbRegions.RegionID = tbMastfinal.RegionID) ON tbGP.GrPrgID = tbMastfinal.GrPrgID) ON tbProjectTypes.ProjectTypeID = tbMastfinal.ProjectTypeID) ON tbT.TCLID = tbMastfinal.TCLID  " & _
        "WHERE (((tbGP.GrProgram)=[Forms]![frmB]![cboB]) AND ((tbT.TCLIDname)=[Forms]![frmB]![lstT]) AND ((tbSA.SAName)=[Forms]![frmB]![lstSolution]) AND ((tbStates.StateName)=[Forms]![frmB]![lstReporting]) AND ((tbRP.RPname)=[Forms]![frmB]![lstReporting]) AND ((tbProjectTypes.ProjectTypeName)=[Forms]![frmB]![lstProjType]) AND ((tbRegions.RegionName)=[Forms]![frmB]![lstS]) AND ((tbArea.AName)=[Forms]![frmB]![lstArea]));"
 

[Rabbit]

Take out the parameter stuff at the beginning. If you're trying to reference a control on a form, you don't need it.

[patjones]

Hi Qtip,

The concern I have has to do with the parameters themselves. What you're trying to do is pass text box values from your form directly into the query using the PARAMETERS clause, and I don't think that can work. You would need to call the parameters something else and then do an explicit assignment for each one using the Parameters collection of your QueryDef object:

Dim strSQL As String
Dim qdf As DAO.QueryDef
 
strSQL = "PARAMETERS [CriteriaValue] TEXT; " & _
         "SELECT * FROM SomeTable WHERE SomeColumn = [CriteriaValue];"
 
Set qdf = CurrentDb.CreateQueryDef("QueryName", strSQL)
qdf.Parameters("CriteriaValue") = Me.txtBoxValue

What we're doing here is defining the parameter name and type within the SQL string...but it's completely arbitrary at this point. The parameter does not take on the value from the text box on the form until the qdf.Parameters assignment.

Pat

[patjones]

Per Rabbit's suggestion, if you remove the PARAMETERS clause you can use the control values in the WHERE clause as you are trying to do, but you cannot include them inside the SQL string; they must be concatenated with the string. For example:

strSQL = "SELECT ...
          FROM ...
          WHERE (((tbGP.GrProgram)= " & [Forms]![frmB]![cboB] & ")  AND ((tbT.TCLIDname)= " & [Forms]![frmB]![lstT] & ")"

[patjones]

If you stick with the parameters, I just made a couple of small corrections to my syntax and added in how you would connect the query results to a recordset and string variable. This query pulls an employee's name based on an employee ID search from text box 'txtEmplID'.

Dim strSQL As String, strResult As String
Dim rst As DAO.Recordset
Dim qry As DAO.QueryDef
 
strSQL ="PARAMETERS EmplID CHAR; SELECT fldNameLast, fldNameFirst FROM tblEmployees WHERE fldID = EmplID;")
 
Set qry = CurrentDb.CreateQueryDef("getEmplName", strSQL)
qry!EmplID = Me.txtEmplID
 
Set rst = qry.OpenRecordset(dbOpenSnapshot)
str = rst("fldNameLast") & ", " & rst("fldNameFirst"
 
Set rst = Nothing
Set qry = Nothing

[Qtip23]

@Rabbit

Thanks for the response. I tried removing just the Parameter statement first. Initially, I received a parameter pop up box when I opened the form. So I continued troubleshooting and tried the other recommendations. This proved to be a good first step.

@Zepphead80

Thanks for your response too. I am using ADO to connect my query results (should not matter though). I noticed that by not using any parameters in my WHERE clause, I was able to run a master query with no issue.

However, this is really not what I want. I would like to limit the query to the filter selection. So I started small and reduced to just one field to see what was happening.

I have tried various options on my form control which I did not mention are seven list boxes.
Instead of "WHERE (((tbGP.GrProgram)=[Forms]![frmB]![lstB]) " , I tried:

'""Me.lstB.ItemData(varItem)""'
'"" & Me.lstB.ItemData(varItem) & ""'
'"" & Me.lstB.Value & ""'
 
 

Do you think I have too many parentheses?

[patjones]

I'm not entirely sure what you wrote. You want the SQL to look something like this:

"SELECT ...
 FROM ...
 WHERE tbGP.GrProgram = " & [Forms]![frmB]![lstB] & " AND  
       ..."

Notice that the reference to the list box resides outside the SQL string. I'm assuming here that lstB contains a number. If it's a text value or date, the value needs to be encapsulated in ' ' or # # respectively.

Pat

[Qtip23]

Not having much luck with the parameters. So I am using a variable to store the value of the control.

I have my Immediate Window open and in debug mode on the string. I see the values that I select. However, the SQL string does not run. I am receive a weird error message -- Method "Command" of object 'View" failed. What could be causing this?

Here is the code I use to apply the SQL statement to the stored query

    cat.ActiveConnection = CurrentProject.Connection
    Set cmd = cat.Views("qryALL_B").Command
    cmd.CommandText = strSQL
    Set cat.Views("qryALL_B").Command = cmd
    Set cat = Nothing
 

[Qtip23]

After further research on Bytes and other sources, looks like there maybe an issue with the DLL's and Access 2007 when using ADO. So I am going to try DAO.

Let me see what happens.

Qtip23

[patjones]

You don't need to assign variables and you don't need parameters. You can embed the controls in the SQL just as I'm saying in post #7.

[NeoPa]

There are two main ways to reference such values using SQL :

1. A direct reference to the controls themselves within your SQL :
   

   SELECT [Forms]![frmB].[lstB]

2. Using VBA to insert the actual value of the control into the string :
   

   strSQL = "SELECT '" & [Forms]![frmB].[lstB] & "'"

I have very little experience of using the PARAMETERS clause in SQL, but I understand there are situations where it may be appropriate to use it (for option #1) because it is otherwise ambiguous as to how to treat the data referenced.

@Pat The SQL in post #7 seems to treat the item as numeric, but all the items in the original PARAMETERS clause were text. I would guess quotes are required.

@Qtip I would suggest, if you are still struggling with this, that you throw away most of the unrequired contents of your SQL and break it down into something much smaller with which to test out the concepts. When you have these sorted out would be a good time to build up your SQL based on your new, and thorough, understanding of the concepts required.

[Qtip23]

@NeoPa I did exactly as you suggested -- breakdown my SQL into smaller parts. What I am learning is the power of action queries.

Instead of focusing on the parameters in SQL string, I have taken them out completely. I have taken another completely different approach.

Having more success by using a combination of the control values and action queries (i.e. insert, append and delete queries) throughout my development.

Thanks for the feedback.

[patjones]

Your query is not an action query though. It's a SELECT query which simply pulls data from the tables rather than adding, deleting or modifying anything. So how is it that you're accomplishing your goal?

[Qtip23]

@zepphead80 you are right. what I meant was that I took a step back and re-evaluated my whole approach to building this particular Access application.

I used the SQL string as suggested above (without the parameters). Yet incorporated other action queries to manipulate data selected by the user, etc. Still working on this effort as we speak.

[patjones]

OK, well if you run into any other issues then let us know!

[NeoPa]

In view of what sounds like an eminently sensible approach to progress, I suggest we consider this done. We don't know precisely what changes you've made, but it sounds like they are fundamental enough to render this question, with the related information, obsolete.

[Qtip23]

@NeoPa your assessment is correct. case closed.