criteria expression for list box

[Gator]

I have a form with two option buttons and a list box. the list box data
depends on what option is selected. Each option used a different table to
draw in the data. However, I have combined the data into one table. The
table contains a list of Accounts.
01 01 01
01 01 02
01 50 01
What I need is when Option1 is selected it draws the accounts that do not
have 50 in the middle and the other Option2 draws only the accounts with 50
in the middle.
Can someone help with the expression?

Here is code I was using in the initial setup using two tables...

Option1 Click
If Option1.value =True then
List1.Rowsource = "SELECT LEFT(ID,2) FROM Table1"
Else
List1.Rowsource = "SELECT LEFT(ID,2) FROM Table2"
End If

Now the RowSource will be the same table just a different criteria. Can
someone help construct?

 

[Douglas J. Steele]

No need to have separate tables.

If Option1 =True then
List1.Rowsource = "SELECT ID FROM Table1 " & _
"WHERE Mid(ID, 4, 2) <> "50"
Else
List1.Rowsource = "SELECT ID FROM Table1 " & _
"WHERE Mid(ID, 4, 2) = "50"
End If

 

[Gator]

what I'm doing now is querying List2 based on List1 with the following
code...but I'm getting error - expected end of stmt.???

Private Sub List1_Click()

If Option1.Value = True Then

List2.RowSource = "SELECT ID, accountname AS Account FROM Accounts WHERE
MID(ID,4,2) = "50" AND left(ID,2) Like " & "'" & List1 & "*';"

Else

List8.RowSource = "select ID, accountname AS Account from Accounts where
MID(ID,4,2) <> "50" AND left(ID,2) Like " & "'" & List0 & "*';"

End If

End Sub
thanks

 

[Douglas J. Steele]

It's because you're putting double quotes inside of double quotes (sorry, I
missed that in my original response)

You can use single quotes instead:

Private Sub List1_Click()

If Option1.Value = True Then

List2.RowSource = "SELECT ID, accountname AS Account FROM Accounts WHERE
MID(ID,4,2) = '50' AND left(ID,2) Like '" & List1 & "*';"

Else

List8.RowSource = "select ID, accountname AS Account from Accounts where
MID(ID,4,2) <> '50' AND left(ID,2) Like '" List0 & "*';"

End If


See what Allen Browne has at http://www.allenbrowne.com/casu-17.html for
more on quotes in quotes.