Multiple Criteria

[Gator]

I need to add an additional criteria for the list query. How do I
incorporate the criteria where List2 will query based on Fields 5 & 6
selected in List0. It's works fine as is by querying just by Field6...but I
need to add Field5 to it.

List0 has the following as rowsource...
SELECT DISTINCT [2008Q2].[field5], [2008Q2].[field6], [2008Q2].[field7] FROM
2008Q2 ORDER BY [field7];

Code behind....
Private Sub List0_AfterUpdate()
Me!List2.Requery
End Sub

Private Sub List0_Click()
List2.RowSource = "select [2008Q2].[field12], [2008Q2].[field6],
[2008Q2].[field5] from 2008Q2 where ((([2008Q2].[field6])='" & List0 & "'));"
End Sub

Function AddNumbersInListbox(ListboxControl As ListBox) As Single
Dim lngLoop As Long
Dim lngSum As Single
lngSum = 0
For lngLoop = 0 To (ListboxControl.ListCount - 1)
lngSum = lngSum + ListboxControl.ItemData(lngLoop)
Next lngLoop
AddNumbersInListbox = lngSum
End Function

 

[Steve Sanford]

It appears that the bound field in List0 is [field6], which is the second
field. So adding [field5] to the row source of List2, assuming that [field5]
is a text type, try:

Private Sub List0_Click()

List2.RowSource = "SELECT [2008Q2].[field12], [2008Q2].[field6],
[2008Q2].[field5] FROM 2008Q2 WHERE [2008Q2].[field6]='" & List0 & "' AND
[2008Q2].[field5] = '" & List0.Column(0) & "';"

End Sub


List0.Column(0) selects the first column (remember it is zero based) of the
list box.


Note: If you have a table names 2008Q2, you really need to rethink your
structure. It is not normalized. Do you recreate all of your
forms/queries/reports each time the year and/or quarter changes??



HTH

 

[Gator]

I get an error on load...
error: data type mismatch in criteria expression.

It appears that the bound field in List0 is [field6], which is the second
field. So adding [field5] to the row source of List2, assuming that [field5]
is a text type, try:

Private Sub List0_Click()

List2.RowSource = "SELECT [2008Q2].[field12], [2008Q2].[field6],
[2008Q2].[field5] FROM 2008Q2 WHERE [2008Q2].[field6]='" & List0 & "' AND
[2008Q2].[field5] = '" & List0.Column(0) & "';"

End Sub


List0.Column(0) selects the first column (remember it is zero based) of the
list box.


Note: If you have a table names 2008Q2, you really need to rethink your
structure. It is not normalized. Do you recreate all of your
forms/queries/reports each time the year and/or quarter changes??



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I need to add an additional criteria for the list query. How do I
incorporate the criteria where List2 will query based on Fields 5 & 6
selected in List0. It's works fine as is by querying just by Field6...but I
need to add Field5 to it.

List0 has the following as rowsource...
SELECT DISTINCT [2008Q2].[field5], [2008Q2].[field6], [2008Q2].[field7] FROM
2008Q2 ORDER BY [field7];

Code behind....
Private Sub List0_AfterUpdate()
Me!List2.Requery
End Sub

Private Sub List0_Click()
List2.RowSource = "select [2008Q2].[field12], [2008Q2].[field6],
[2008Q2].[field5] from 2008Q2 where ((([2008Q2].[field6])='" & List0 & "'));"
End Sub

Function AddNumbersInListbox(ListboxControl As ListBox) As Single
Dim lngLoop As Long
Dim lngSum As Single
lngSum = 0
For lngLoop = 0 To (ListboxControl.ListCount - 1)
lngSum = lngSum + ListboxControl.ItemData(lngLoop)
Next lngLoop
AddNumbersInListbox = lngSum
End Function

 

[Gator]

It worked when I went to the table and changed Field4 to text datatype. what
would be different about the code if field4 remained a number type?

It appears that the bound field in List0 is [field6], which is the second
field. So adding [field5] to the row source of List2, assuming that [field5]
is a text type, try:

Private Sub List0_Click()

List2.RowSource = "SELECT [2008Q2].[field12], [2008Q2].[field6],
[2008Q2].[field5] FROM 2008Q2 WHERE [2008Q2].[field6]='" & List0 & "' AND
[2008Q2].[field5] = '" & List0.Column(0) & "';"

End Sub


List0.Column(0) selects the first column (remember it is zero based) of the
list box.


Note: If you have a table names 2008Q2, you really need to rethink your
structure. It is not normalized. Do you recreate all of your
forms/queries/reports each time the year and/or quarter changes??



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I need to add an additional criteria for the list query. How do I
incorporate the criteria where List2 will query based on Fields 5 & 6
selected in List0. It's works fine as is by querying just by Field6...but I
need to add Field5 to it.

List0 has the following as rowsource...
SELECT DISTINCT [2008Q2].[field5], [2008Q2].[field6], [2008Q2].[field7] FROM
2008Q2 ORDER BY [field7];

Code behind....
Private Sub List0_AfterUpdate()
Me!List2.Requery
End Sub

Private Sub List0_Click()
List2.RowSource = "select [2008Q2].[field12], [2008Q2].[field6],
[2008Q2].[field5] from 2008Q2 where ((([2008Q2].[field6])='" & List0 & "'));"
End Sub

Function AddNumbersInListbox(ListboxControl As ListBox) As Single
Dim lngLoop As Long
Dim lngSum As Single
lngSum = 0
For lngLoop = 0 To (ListboxControl.ListCount - 1)
lngSum = lngSum + ListboxControl.ItemData(lngLoop)
Next lngLoop
AddNumbersInListbox = lngSum
End Function

 

[Steve Sanford]

It worked when I went to the table and changed Field4 to text datatype. what
would be different about the code if field4 remained a number type?

I didn't see anything about a [field4] in the code, but the answer to your
question has to do with the delimiters.

Number do not need a delimiter.
Text needs to be delimited with quotes - 'text' or "test2"
Date/time is delimited with the hash marks - #9/16/2008#

HTH