Storing query criteria in a Table

  • Thread starter Thread starter GimpBoy
  • Start date Start date
G

GimpBoy

This is a re-post of a question I put up last night, for some reason I
cannot see my question in the group, perhaps I made a mistake posting it.
Please excuse me if this question is asked twice.

I would like to be able to store criteria info in a table, however when I
attempt to do so the info is not evaluated as criteria, rather it is looked
at as strait text.

For example, if I were to store the text 'like 1 or 3 or 18 or 27' in a
table and call it in the criteria by using the table/row info
'[Orders]![Se1]' the query would search for the exact text 'like 1 or 3 or
18 or 27' instead of looking for the numbers 1 OR 3 OR 18 OR 27.

I tried most all functions I can think of and nothing has worked, I was
optimistic about the eval() function, also tried storing the numbers with
comma sepereated and using the in([Orders]![Se1]) however this did not pick
up that the commas were actual commas in the equasion.

Any help you could give me would be very appreciated, Last night was the
first time Ive come across this group and I have to say it is very
refreshing to see people helping others in this way.

Thank You in Advance. DougB
 
Sounds very similar to the old multi-select listbox problem.
Basically, you are storing the wrong string.

See below for some hints.

A Multi-Select ListBox can not be used directly in the Query Grid Criteria.
e.g. Forms![FormName]![ListBoxName] will NOT work.
Why?
Simply examine the output of the Selection and you will see that the string
produced is different from the one the Query Grid requires:
"MyText1 Or MyText2 Or MyText3" from Listbox
"MyText1" Or "MyText2" Or "MyText3" from query grid.

The solution to the multi-select listbox problem is to write the query in
code so that it looks the same way the query grid does.
Build the criteria based on selected values that you can determine by
looping through the selected property of the list box.

I usually write the query in the Grid and use multiple criteria and then
copy the SQL to code and work it into this format:

========================================================
Private Sub btnCreateInvoice_Click()
On Error GoTo Err_btnCreateInvoice_Click

Dim frm As Form, ctl As Control, varItm As Variant, strCriteria As
String
Dim dbs As Database, qdf As QueryDef
Set dbs = CurrentDb
Set frm = Forms![CreateInvoiceData]
Set ctl = frm!ListPOs
strCriteria = ""

For Each varItm In ctl.ItemsSelected
strCriteria = strCriteria & "(pohdr.ponumber)='" &
Trim(ctl.ItemData(varItm)) & "' Or "
Next varItm

If strCriteria = "" Then
MsgBox "Select one or more PO's."
Exit Sub
End If

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

strSQL = "SELECT [invnumber], Date() AS invdate, pohdr.vendno "
strSQL = strSQL & "FROM pohdr INNER JOIN poln ON pohdr.pokey =poln.pokey
"
strSQL = strSQL & "WHERE (" & strCriteria & ");"

If QueryExists("Inv1") = True Then
dbs.QueryDefs.Delete "Inv1"
End If

Set qdf = dbs.CreateQueryDef("Inv1")
qdf.SQL = strSQL

Set ctl = Nothing
Set frm = Nothing
Set dbs = Nothing

Exit_btnCreateInvoice_Click:
Exit Sub

Err_btnCreateInvoice_Click:
MsgBox ("Error # " & str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_btnCreateInvoice_Click

End Sub

========================================================
Function QueryExists(strQueryName As String) As Boolean
On Error Resume Next
QueryExists = IsObject(CurrentDb.QueryDefs(strQueryName))
End Function

========================================================
To de-select all items in a list box try:
Dim lngX As Long

With Me![lstMyListBox]
For lngX = (.ItemsSelected.Count - 1) To 0 Step -1
.Selected(.ItemsSelected(lngX)) = False
Next lngX
End With

========================================================
To Select all Items in a Listbox:

Private Sub btnSelectAll_Click()
'You can use the Selected property to select items in a list box.
'For example, the following expression selects the fifth item in the list:
'Me!ListBox.Selected(4) = True

Dim lngX As Long

With Me![lstMyListBox]
For lngX = 0 To .ListCount
.Selected(lngX) = True
Next lngX
End With
End Sub

--
Joe Fallon
Access MVP



GimpBoy said:
This is a re-post of a question I put up last night, for some reason I
cannot see my question in the group, perhaps I made a mistake posting it.
Please excuse me if this question is asked twice.

I would like to be able to store criteria info in a table, however when I
attempt to do so the info is not evaluated as criteria, rather it is looked
at as strait text.

For example, if I were to store the text 'like 1 or 3 or 18 or 27' in a
table and call it in the criteria by using the table/row info
'[Orders]![Se1]' the query would search for the exact text 'like 1 or 3 or
18 or 27' instead of looking for the numbers 1 OR 3 OR 18 OR 27.

I tried most all functions I can think of and nothing has worked, I was
optimistic about the eval() function, also tried storing the numbers with
comma sepereated and using the in([Orders]![Se1]) however this did not pick
up that the commas were actual commas in the equasion.

Any help you could give me would be very appreciated, Last night was the
first time Ive come across this group and I have to say it is very
refreshing to see people helping others in this way.

Thank You in Advance. DougB
Click to expand...
 
this is a copy/paste of the answer to your original post, posted on
4/10/2004 at 8:40 AM:

Subject: Re: Criteria from table

What I would like to do is save my 'Select Query' criteria in a table, for
example "13 Or 15 Or 18".
So in the criteria section I would simply have to put the location of the
field [tabel].[field].

The problem i'm having is the query is not evaluating the text in the field
as criteria. In other words it is looking for the exact text "13 Or 15 Or
18" instead of treating the Or as it should.

Is there a way to make Access 'Re-Evaluate' the linked text?
-Microsoft office XP
Click to expand...

Only by writing VBA code to actually construct the entire SQL string
of the query. Parameters can only contain actual values, and not
operators such as Or.







GimpBoy said:
This is a re-post of a question I put up last night, for some reason I
cannot see my question in the group, perhaps I made a mistake posting it.
Please excuse me if this question is asked twice.

I would like to be able to store criteria info in a table, however when I
attempt to do so the info is not evaluated as criteria, rather it is looked
at as strait text.

For example, if I were to store the text 'like 1 or 3 or 18 or 27' in a
table and call it in the criteria by using the table/row info
'[Orders]![Se1]' the query would search for the exact text 'like 1 or 3 or
18 or 27' instead of looking for the numbers 1 OR 3 OR 18 OR 27.

I tried most all functions I can think of and nothing has worked, I was
optimistic about the eval() function, also tried storing the numbers with
comma sepereated and using the in([Orders]![Se1]) however this did not pick
up that the commas were actual commas in the equasion.

Any help you could give me would be very appreciated, Last night was the
first time Ive come across this group and I have to say it is very
refreshing to see people helping others in this way.

Thank You in Advance. DougB
Click to expand...
 
Dear Gimp:

Joe's suggestion is a common solution, but there is another. That
solution is to store the selections made in a local table (one in the
front end database, not linked to the back end, and not shared with
another instance of the database front end) and then JOIN to that
table. In this case, each selection is stored in a separate row of
that local table.

This is a somewhat more difficult solution than the dynamic SQL
generation. With dynamic SQL generation, you must watch for quotation
marks in the data, and you are limited to a maximum length of SQL
string generated.

The local table method is highly extensible. You avoid the
possibility of the SQL string being too long. If fact, the SQL
doesn't change. This method is less frequently used because, for many
cases, the dynamic SQL approach offers no significant disadvantages
and is easier. However, the local table can be indexed to provide
optimal performance (not much of a factor unless hundreds of
selections are possible) so, when needed, this is the "professional"
approach to the situation.

I mention this since you made the suggestion of "stor(ing] criteria
info in a table" which is just what this approach does. However, the
values 1, 3, 18, and 27 would be stored as 4 rows in the local table.
However, this then functions with an inner join as "1 OR 3 OR 18 OR
27" as you suggested.

To perform this, use a recordset to first clear the local table, then
add the rows using a loop on the Selected lines in a combo box (if
that's what you have).

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


This is a re-post of a question I put up last night, for some reason I
cannot see my question in the group, perhaps I made a mistake posting it.
Please excuse me if this question is asked twice.

I would like to be able to store criteria info in a table, however when I
attempt to do so the info is not evaluated as criteria, rather it is looked
at as strait text.

For example, if I were to store the text 'like 1 or 3 or 18 or 27' in a
table and call it in the criteria by using the table/row info
'[Orders]![Se1]' the query would search for the exact text 'like 1 or 3 or
18 or 27' instead of looking for the numbers 1 OR 3 OR 18 OR 27.

I tried most all functions I can think of and nothing has worked, I was
optimistic about the eval() function, also tried storing the numbers with
comma sepereated and using the in([Orders]![Se1]) however this did not pick
up that the commas were actual commas in the equasion.

Any help you could give me would be very appreciated, Last night was the
first time Ive come across this group and I have to say it is very
refreshing to see people helping others in this way.

Thank You in Advance. DougB
Click to expand...
 
Back
Top