Help with filtering a query using a form

[racefan91]

I am trying to filter a query based on combo boxes or text boxes from a form. I can't seem to get this to work properly. Sometimes it will work with just a couple things entered into my form but other times it will start showing rows that don't match the criteria. These are what I have going.

This is the code for my query:

SELECT Tbl_Tournaments.Tournament, Tbl_Tournaments.Round, Tbl_Tournaments.Region1, Tbl_Tournaments.Seed1, Tbl_Tournaments.Rank1, Tbl_Tournaments.Star1, Tbl_Tournaments.Region2, Tbl_Tournaments.Seed2, Tbl_Tournaments.Rank2, Tbl_Tournaments.Star2, Tbl_Tournaments.Winner, Tbl_Tournaments.Loser
FROM Tbl_Tournaments
WHERE (((Tbl_Tournaments.Round) Like [Forms]![Frm_Tournaments Sort]![Round] & "*") AND ((Tbl_Tournaments.Region1) Like [Forms]![Frm_Tournaments Sort]![Region1] & "*") AND ((Tbl_Tournaments.Seed1) Like [Forms]![Frm_Tournaments Sort]![Seed1] & "*") AND ((Tbl_Tournaments.Rank1) Like [Forms]![Frm_Tournaments Sort]![Rank1] & "*") AND ((Tbl_Tournaments.Star1)=([Tbl_Tournaments].[Winner]))) OR (((Tbl_Tournaments.Seed2) Like [Forms]![Frm_Tournaments Sort]![Seed2] & "*") AND ((Tbl_Tournaments.Rank2) Like [Forms]![Frm_Tournaments Sort]![Rank2] & "*") AND ((Tbl_Tournaments.Star2)=([Tbl_Tournaments].[Winner])))
ORDER BY Tbl_Tournaments.Tournament, Tbl_Tournaments.Round, Tbl_Tournaments.Region1;

[/CODE]

When I used & "Null" behind Seed1 & Seed2 that seems to be working better for the seed criteria but I still have issues with nothing showing up if I don't enter a seed in my form. With the & "*" behind the seed I do get all the records to show up like it should when I have no criteria entered into my form. I thought I used Like....& is null into a different form and that one I believe works but now when I go in there the coding looks a lot different like access automically changed it. If anybody can help me with this I would really appreciate it.

[ItsMe]

One approach may be to use the VBA editor rather than the criteria fields in the Access query.

Access may update (change) what you place into the fields at the bottom of the query window. Access wants to write everything out in SQL.

[racefan91]

I don't know how to read or write VBA. I only know how to do a very little bit in the sql, mostly just with simpler stuff.

[ItsMe]

I don't do so well with SQL. I took a look at the where clause and I can not see any provisions for null or 0 values. You may be able to catch this with an IIf statement in the criteria fields.

Also, your where clause considers criteria for either Seed1 or Seed2 and Rank1 or Rank2. The where clause does not consider criteria for Region and Round for Seed2 and Rank2.

In general, your SQL seems very specific for a search form. I always approach these matters with VBA. Perhaps someone else can provide a solution you can paste into your query.

[ck4794]

Just guessing from the outside, and not knowing the intimate details of this database, here's the approach I would take. I would have all those combos unbound and in the form header. A continuous form. The record source would have criteria being >like "(whatever unbound combo you have)" & "*"<. I don't know why but the >& "*"< has made a huge difference for me on more than one occasion. I would also put on all those combos' respective "after update" events to requery.

I hope i at least gave you an idea if this doesn't work for you.

[orange]

see Martin Green's site and tutorials at
http://www.fontstuff.com/access/acctut17.htm