Criteria "Or" Problem

[Bob Barnes]

I posted this question below, but want to redefine it.

When using the QBE-Grid, entering "1 Or 2" as a Criteria
works properly.

Trying to get the same result from an unbound field on a
Form (Criteria of [Forms]![frmMain]![ABC]) returns NO
records if the unbound field has something like "1 Or 2".

I build the "1 Or 2" from a multi-select List Box that
populates properly.

I even ran this thru code (QueryDef, Parameter) but
it still fails when the unbound control has the "1 Or 2".

Again, assistance welcomed.

TIA - Bob

 

[Sam]

Bob,

Consider the difference in SQL underlying your two queries:

Entering "1 Or 2" in QBE Grid:

SELECT Table1.value
FROM Table1
WHERE (((Table1.value)=1 Or (Table1.value)=2));

vs.

Using Parameter:

SELECT Table1.value
FROM Table1
WHERE (((Table1.value)=[forms]![form1]![ABC]));

Perhaps you should use your multi-select to create the SQL directly.

Sam

 

[Jeff Boyce]

Bob

Just a thought ... have you tried using the Eval() function on the contents
of the unbound field?

Good luck

Jeff Boyce
<Access MVP>

 

[Bob Barnes]

Jeff - Will try & get back.

Thank you - Bob

 

[Bob Barnes]

Tried "Eval([Forms]![Main Switchboard]![AllMcns])" in the
QBE (figured it wouldn't work & it didn't).

However, I used to Program Lotus 1-2-3 (& I know Excel
does the same thing). This should be similar to Eval.
In 1-2-3, if you had a formula but wanted to use only the
"value", you used "Range-Value" - In 1987, that was BIG.

So if I avoid the "[Forms]![frmMain]![ABC]" in the
QBE Criteria, how do I "reference / range value" the
"ABC" so it puts "1 Or 2" in the QBE (by "automation")?

If I can it this to Work with the Graphs, it will be NICE.

Bob

 

[Bob Barnes]

This is the 1st Totals Query -

SELECT [Troduction Data].Date, [Troduction
Data].Shift, [Troduction Data].PartID, [Troduction
Data].McnID, [Troduction Data].GroupID, Sum
([Troduction Data].Parts) AS Parts, Sum([Troduction
Data].TotalRepair) AS Repair, Sum([Troduction
Data].Scrap16) AS SumOfScrap16, Sum([Troduction
Data].TotalScrap) AS SumOfTotalScrap, Sum([Troduction
Data].OpTm) AS SumOfOpTm, Sum([Troduction Data].WaitK)
AS SumOfWaitK, Sum([Troduction Data].WaitS) AS
SumOfWaitS, Sum([Troduction Data].McnFlt) AS
SumOfMcnFlt, Sum([Troduction Data].MldFlt) AS
SumOfMldFlt, Sum([Troduction Data].WrkDly) AS
SumOfWrkDly, Sum([Troduction Data].Setup) AS SumOfSetup,
Sum([Troduction Data].Other) AS SumOfOther
FROM [Troduction Data]
GROUP BY [Troduction Data].Date, [Troduction
Data].Shift, [Troduction Data].PartID, [Troduction
Data].McnID, [Troduction Data].GroupID
HAVING ((([Troduction Data].Date) Between [Forms]![Main
Switchboard]![BegDT] And [Forms]![Main Switchboard]!
[EndDT]) AND (([Troduction Data].Shift)=[Forms]![Main
Switchboard]![ShiftDT]) AND (([Troduction Data].McnID)=
[Forms]![Main Switchboard]![AllMcns]) AND (([Troduction
Data].GroupID)=[Forms]![Main Switchboard]![DTGroup]));

I can force "[Forms]![Main Switchboard]![AllMcns]" to
appear like "1 Or Mcn = 2". But it doesn't work.

I figured out something that would work. As I populate
"AllMcns", I get a Len (each Mcn is always 3-characters).
I can use a Select Case to fill several (no more than 10
unbound controls) - I then use a series of Queries where
Criteria are "[Forms]![Main Switchboard]![Mcn1]", "[Forms]!
[Main Switchboard]![Mcn2]", etc. The Make Table Query (a
series of them) will always create the "Summary Table" used
as the RecordSource for the Graph.

Should work, but wish I could use a single unbound control
with the original Query.

Thank you - Bob

-----Original Message-----
post the SQL that you came up w/, and perhaps the code used to generate it.

Sam

Sam - I just reached the same conclusion you did.

I then used multi-select to create the SQL directly (or
what I thought would work, but it didn't).

Even considering putting the Parameters in multiple rows
of the QBE, but want to avoid that if possible. I know
using a Filter in a DoCmd.OpenReport works great, but
how can I "Filter the Query" to get around this problem?

However, I am running 4 "Totals" Queries to then
Make A Table which serves as the RecordSource for
a MS Graph.

Will keep trying. Thank you - Bob

-----Original Message-----
Bob,

Consider the difference in SQL underlying your two queries:

Entering "1 Or 2" in QBE Grid:

SELECT Table1.value
FROM Table1
WHERE (((Table1.value)=1 Or (Table1.value)=2));

vs.

Using Parameter:

SELECT Table1.value
FROM Table1
WHERE (((Table1.value)=[forms]![form1]![ABC]));

Perhaps you should use your multi-select to create the SQL directly.

Sam


I posted this question below, but want to redefine it.

When using the QBE-Grid, entering "1 Or 2" as a Criteria
works properly.

Trying to get the same result from an unbound field on a
Form (Criteria of [Forms]![frmMain]![ABC]) returns NO
records if the unbound field has something like "1 Or 2".

I build the "1 Or 2" from a multi-select List Box that
populates properly.

I even ran this thru code (QueryDef, Parameter) but
it still fails when the unbound control has the "1 Or 2".

Again, assistance welcomed.

TIA - Bob




.

.

 

[Bob Barnes]

Sam - Thank you.

I wound up getting it to work quickly. But I will copy
your SQL-builder code in my "Knowledge Base".

Thank you - Bob

-----Original Message-----
Bob,

The SQL below won't work for the same reason outlined in my first message
(e.g. you cant say WHERE [variable] = A Or B).

You can do what you want to do using code like the following:

' Fn to generate query based on IDs selected in list box
' returns the SQL string just for kix
Public Function SetSQL() As String
' Put your stuff here
Const MYFIELDNAME = "[McnID]"
Const MYFORMNAME = "Form1"
Const MYCONTROLNAME = "lstMultiSelectLB"
Const MYQUERY = "Query1"

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs(MYQUERY)
Set frm = Forms(MYFORMNAME)
Set ctl = frm.Controls(MYCONTROLNAME)

' the first part of your SQL statement. you'll eventually want to choose
your columns here, etc.
strSQL = "SELECT * FROM Table1 WHERE ("
strSQL = strSQL & MYFIELDNAME & "="

' iterate through listbox and create WHERE criteria
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR " & MYFIELDNAME & "="
Next varItem

' cut off trailign junk and close parens
strSQL = "" & Left$(strSQL, Len(strSQL) - (5 + Len (MYFIELDNAME))) & ")"

' redefine query
qdf.SQL = strSQL

' return sql string
SetSQL = strSQL
End Function




The new query can be opened immediately after the function is called, e.g.:

Private Sub cmdRunQuery_Click()

Dim stDocName As String

SetSQL

DoCmd.OpenQuery "Query1", acNormal, acEdit

End Sub


Try this and let us know. Usual warnings apply.

Sam

This is the 1st Totals Query -

SELECT [Troduction Data].Date, [Troduction
Data].Shift, [Troduction Data].PartID, [Troduction
Data].McnID, [Troduction Data].GroupID, Sum
([Troduction Data].Parts) AS Parts, Sum([Troduction
Data].TotalRepair) AS Repair, Sum([Troduction
Data].Scrap16) AS SumOfScrap16, Sum([Troduction
Data].TotalScrap) AS SumOfTotalScrap, Sum([Troduction
Data].OpTm) AS SumOfOpTm, Sum([Troduction Data].WaitK)
AS SumOfWaitK, Sum([Troduction Data].WaitS) AS
SumOfWaitS, Sum([Troduction Data].McnFlt) AS
SumOfMcnFlt, Sum([Troduction Data].MldFlt) AS
SumOfMldFlt, Sum([Troduction Data].WrkDly) AS
SumOfWrkDly, Sum([Troduction Data].Setup) AS SumOfSetup,
Sum([Troduction Data].Other) AS SumOfOther
FROM [Troduction Data]
GROUP BY [Troduction Data].Date, [Troduction
Data].Shift, [Troduction Data].PartID, [Troduction
Data].McnID, [Troduction Data].GroupID
HAVING ((([Troduction Data].Date) Between [Forms]! [Main
Switchboard]![BegDT] And [Forms]![Main Switchboard]!
[EndDT]) AND (([Troduction Data].Shift)=[Forms]![Main
Switchboard]![ShiftDT]) AND (([Troduction Data].McnID) =
[Forms]![Main Switchboard]![AllMcns]) AND (([Troduction
Data].GroupID)=[Forms]![Main Switchboard]![DTGroup]));

I can force "[Forms]![Main Switchboard]![AllMcns]" to
appear like "1 Or Mcn = 2". But it doesn't work.

I figured out something that would work. As I populate
"AllMcns", I get a Len (each Mcn is always 3- characters).
I can use a Select Case to fill several (no more than 10
unbound controls) - I then use a series of Queries where
Criteria are "[Forms]![Main Switchboard]! [Mcn1]", "[Forms]!
[Main Switchboard]![Mcn2]", etc. The Make Table Query (a
series of them) will always create the "Summary Table" used
as the RecordSource for the Graph.

Should work, but wish I could use a single unbound control
with the original Query.

Thank you - Bob

-----Original Message-----
post the SQL that you came up w/, and perhaps the code used to generate it.

Sam

Sam - I just reached the same conclusion you did.

I then used multi-select to create the SQL directly (or
what I thought would work, but it didn't).

Even considering putting the Parameters in multiple rows
of the QBE, but want to avoid that if possible. I know
using a Filter in a DoCmd.OpenReport works great, but
how can I "Filter the Query" to get around this problem?

However, I am running 4 "Totals" Queries to then
Make A Table which serves as the RecordSource for
a MS Graph.

Will keep trying. Thank you - Bob

-----Original Message-----
Bob,

Consider the difference in SQL underlying your two
queries:

Entering "1 Or 2" in QBE Grid:

SELECT Table1.value
FROM Table1
WHERE (((Table1.value)=1 Or (Table1.value)=2));

vs.

Using Parameter:

SELECT Table1.value
FROM Table1
WHERE (((Table1.value)=[forms]![form1]![ABC]));

Perhaps you should use your multi-select to create the
SQL directly.

Sam


I posted this question below, but want to redefine it.

When using the QBE-Grid, entering "1 Or 2" as a Criteria
works properly.

Trying to get the same result from an unbound

field
on a

Form (Criteria of [Forms]![frmMain]![ABC]) returns NO
records if the unbound field has something like "1 Or
2".

I build the "1 Or 2" from a multi-select List Box that
populates properly.

I even ran this thru code (QueryDef, Parameter) but
it still fails when the unbound control has the "1 Or
2".

Again, assistance welcomed.

TIA - Bob




.



.

.

 

[Joe Fallon]

FYI

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

--
Joe Fallon
Access MVP

I posted this question below, but want to redefine it.

When using the QBE-Grid, entering "1 Or 2" as a Criteria
works properly.

Trying to get the same result from an unbound field on a
Form (Criteria of [Forms]![frmMain]![ABC]) returns NO
records if the unbound field has something like "1 Or 2".

I build the "1 Or 2" from a multi-select List Box that
populates properly.

I even ran this thru code (QueryDef, Parameter) but
it still fails when the unbound control has the "1 Or 2".

Again, assistance welcomed.

TIA - Bob

 

[Bob Barnes]

Joe - Revisiting this after several days.

I had built the Unbound Control to populate data from the
ListBox, not the ListBox itself as the Criteria.

Above in this thread, the technique I used worked very
well with a Series of Queries - rapidly developed with
great results in the MS Graph. I don't know how much
MS Graphs are used within Access (I suspect not much),
but they're pretty impressive.

Thank you - Bob

-----Original Message-----
FYI

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

--
Joe Fallon
Access MVP

I posted this question below, but want to redefine it.

When using the QBE-Grid, entering "1 Or 2" as a Criteria
works properly.

Trying to get the same result from an unbound field on a
Form (Criteria of [Forms]![frmMain]![ABC]) returns NO
records if the unbound field has something like "1 Or 2".

I build the "1 Or 2" from a multi-select List Box that
populates properly.

I even ran this thru code (QueryDef, Parameter) but
it still fails when the unbound control has the "1 Or 2".

Again, assistance welcomed.

TIA - Bob

.