How to include all records using WHERE

[showdebola]

Hi everyone,

i am quite a begginer at this so sorry for the simple question

I am running a access query from a vba code. IN the vba code I am writing the sql command.

I am getting the criteria for the sql code (WHERE command) from a number of combo boxes in a form.
The whole thing works quite well when I have something selected on all combo boxes but it does collapse when one of the combo boxes has the default value which is "" nothing.

for instance when I have
WHERE domicile.IdDomicile = 5
works fine. 5 comes from the combo box

when there is nothing selected in my combo box the sql code would be
WHERE domicile.IdDomicile =

and then I get an error msg saying the query is wrong.

Question,

what is the command in sql that instruct the query to return all records.
I tried using "*" when the combo box was not selected but then I dont get the records that are blank, only the ones with something writen in it.

Any other suggestion for this

Many Thanks

K Regards

 

[Matt Greatorex]

I had a similar problem and got around it in the following way.

str_SQL = "SELECT * FROM Table1"

If Len([I]combobox1[/I]) > 0 Then
      str_Where = " WHERE Person = '" & [I]combobox1[/I] & "'"
else
      str_Where = ""
end if

str_SQL = str_SQL & str_Where & ";"

DoCmd.RunSQL str_SQL

If a value is picked, add the where clause; if not, don't.

I'm sure someone will immediately tell you of a more efficient way of doing this, but the above worked for me.

 

[jnixon]

I'm having the same problem as described above. I have 4 combo boxes, and I need to filter a query out using any number of those boxes. However, I'm no good at SQL and I can't seem to get a query to work that would say "If the combo box is not null, filter by combo box, else display all". Right now it will only do one or the other, but not an if/else statement, and won't display any fields that are null...

Please help! Thanks.

 

[pbaldy]

The simple solution is this:

http://www.mvps.org/access/queries/qry0001.htm

but it gets ungainly with a number of different fields. When I've got a variety of options, I build something like Matt's got.

 

[jnixon]

Thank you!! That's exactly what I wanted!