Can not get Access 2016 queries to accept form value or TempVars! as criteria (1 Viewer)

[bcmarshall]

Thank you very much. I would like to pass on the results of my testing.

I completely removed the Crosstab Query from the source Select Query and the problem went away. You were right. It’s related somehow to the Crosstab Query.

However, I did exactly as suggested. I right-clicked on Query Design and chose Parameters. The window in the image opened and for every field drawn from the Crosstab Query I entered the field name and selected the Data Type from the list in the right column.

Now when I try to run the query I get a box for each field labeled Enter Parameter Value.

obviously something is wrong. Any suggestions would be appreciated.

 

[Minty]

You only need to define parameters if the crosstab needs them, however, if that crosstab uses another query as its source and that query has parameters, then you must define the parameters in that source query.

 

[bcmarshall]

I’m confused. I now understand how to find the parameters so it’s not a problem for me to do. I’m just trying to understand what my objective is.

Just to reiterate, the field whose data I am trying to limit is from one of the Select Queries and not from the Crosstab.

In this instance do I still need to go back to the source of the Crosstab and define parameters?

also, I don’t mean to sound stupid, but to me, the word parameter is some sort of a limit. When I look at the query parameter value and ask for the name of the field and the death type. That doesn’t seem like a limit to me. It’s just defining the data type that is used or expected.

i’m happy to do whatever is needed. I’m just trying to understand where I’m going with this.

 

[bcmarshall]

Yessssss! I played with the parameter values, but I was trying to use the field names in the query, and it failed.

when I selected the temporary variable name or the form field name then it worked just fine.

I believe the issue is resolved based on the parameters. Thank you all for all of your help.

 

[MajP]

When you type a criteria with [ ] in the QBE it adds it as a paramter to the parameters collection of the query definition. If it is a form reference it knows where to look for it. If not it prompts you. You can also set these values in code. This makes insert queries and update queries much easier since you do not have to worry about delimiters.

Here is a simple example. In the criteria of the EmployeeID field I typed "[Enter ID]". This is the first (and only parameter) and it has an index of "Enter ID". I can now pass the value to the query definition.

Public Sub Test()
  Dim qdf As QueryDef
  Dim db As Database
  Set db = CurrentDb
  Set qdf = db.QueryDefs("query1")
  db.QueryDefs.Refresh
  'the following two lines are identical
  qdf.Parameters(0) = 3
 ' qdf.parameters("Enter ID") = 3
    
  Dim rs As DAO.Recordset
  Set rs = qdf.OpenRecordset
  Do While Not rs.EOF
    Debug.Print rs!EmployeeID
    rs.MoveNext
  Loop
End Sub

 

[bcmarshall]

I really appreciate all the help. Other forums allow you to give a star or some sort of extra credit or special thanks to people that helped you but I don’t see that option here. Is that option available? And if so, how do I find it? I would really like to thank everyone that helped me.

 

[CJ_London]

you use the like button, bottom right of the relevant post