help with SQL statement

[bower305]

I am trying to construct a SQL statement in VBA and seem to have hit a wall. What I have right now works but I want to add an "All" option at the top of the list. For some reason, whatever I try ends up as an error. Can anyone help?

Here is my code:

strSQL = "SELECT " & str_choice & ".ID," & str_choice & ".State," & str_choice & ".FIPS FROM " & str_choice & " ORDER BY " & str_choice & ".FIPS;"

 

[WayneRyan]

bower,

It looks like the variable in your SQL statement is the name of
the table.

Surely you don't want to select from all tables. Even if the table
name is the variable, they won't have the same fields.

If you are meaning fields, (Select * From ...) is what you
need. Just substitute the "*" if they select all.

The str_choice has only one value here. It would seem that a
listbox would be in order.

Need more info ...

Wayne

 

[bower305]

The variable strChoice does specify a table. Depending on what the user chosses in the first 2 comboboxes, the listbox updates. There are 3 versions of a table each which its own list of states. I can get the correct table selected and populate the list box correctly but I cannot get an "all" choice at the top of the list that would select all of the states in the list.
Does this help at all?

 

[WayneRyan]

bower,

OK, I'm with you so far.

You have a combo that selects a table.

Based on that you have a combo with "All" and a list of states.

If they select "All" Then: 
   sql = "Select * from YourTable order by State"
Otherwise, you go through the work of traversing the listbox,
   making your IN (a,b,c) clause.

Basically, your WHERE clause either doesn't exist (ALL) or
you construct it based on the listbox.

Wayne

 

[Brian1960]

SQL Code

strSQL = "SELECT " & str_choice & ".ID," & str_choice & ".State," & str_choice & ".FIPS FROM " & str_choice & " ORDER BY " & str_choice & ".FIPS;"

Change to

strSQL = "SELECT " & str_choice & ".* FROM " & str_choice & " ORDER BY " & str_choice & ".FIPS;"

This should work but you may have to add in
," & str_choice & ".FIPS

to the select side.

 

[bower305]

Thanks for all your suggestions but my real problem is getting the text "All" to show up as a choice in the listbox. Can anyone help with this?

 

[WayneRyan]

bower,

Me.ListBox.RowSource = "Select 'All' From YourTable Union " & _
                       "Select State From YourTable"

Wayne