Set the criteria for a column in an existing query dynamically ?

[Paul7905]

I have a cross tab query which i am running from code donated to me from Paul here at tek tips.

The name of the Crosstab Query that i am running in the code is "Crosstab_Size"

This Query has as it's source, another Query called "ProdinfoReportQuery"

If I execute this query by fetching it up and manually typing in the following into the "criteria" field of the item-name column in the Crosstab_Size query and run it, I get exactly the results I need.

criteria i key in is : (note, the ItemName is one i selected to test it and needs to be a variable somehow)

[ProdinfoReportQuery]![ItemName]="Bougainvillea brasiliensis X"


What i would like to to is, before funning the code to execute the query, is to set the "criteria" for the ItemName column in the query to read as above with the ability to insert the name of the item as a variable or argument (I have the item name argument in a string called "Itemnamecriteria&quot ~

is there a way to do this ?

Dim db As DAO.DATABASE, qdf As QueryDef, fld As Field
Dim X As Integer, strFldName() As String

X = 0
Set db = CurrentDb
For Each qdf In db.QueryDefs
If qdf.name = "Crosstab_Size" Then
X = qdf.Fields.Count

SOMEHOW, SET THE CRITERIA FOR ItemName HERE?
criteria for the item name column in the query set as follows ??
[ProdinfoReportQuery]![ItemName]="& Itemnamecriteria"



ReDim strFldName(1 To X) As String

X = 0
For Each fld In qdf.Fields
X = X + 1
strFldName(X) = fld.name
Next
End If
Next qdf


I hope this makes sense ~ basically, i want the code you gave me to run the query for one specific item only by setting the criteria dynamically somewhere in your code.

thanks !

 

[LarryDeLaruelle]

If you are only passing one parameter, the easiest way is to set up a form to prompt the user for the criteria.

I prefer combo or list boxes since it minimizes user input error (seems like that could be a problem with the parameter you used above).

In the query, use the expression builder to reference the text, combo or list box on the form.

If you haven't used the expression builder I would be happy to help you work though it.

Larry De Laruelle
larry1de@yahoo.com