| Author |
Message |
|
Kurt Peterse
#1 / 7
|
 WildCard Searches... I have a table that has ProjectName, Team, SubTeam1, and SubTeam2. I have on the main menu form several fields where the user can do
wildcard searches. If a user enters something into the textbox
SubTeam1, I want the query to search both SubTeam1 and SubTeam2 for that
entry. Same goes if the user only enters something into the textbox
SubTeam2.
If the user enter something in both textboxes, I want the query to look
both SubTeam1 and SubTeam2 fields for both textboxes. Hope that made
sense...
I have the following formula where if a user enters something into
SubTeam1, it will onlysearch SubTeam1. I couldn't modify the formula to
do what I said above:
Field:
IIf(IsNull([Forms]![frmMainMenu]![SubTeam1]),1,InStr(1,[SubTeam1],[Forms]![frmMainMenu]![SubTeam1],1))
Criteria:
Quote: >=Val("1")
Thanks
|
| Sun, 09 Sep 2001 03:00:00 GMT |
|
 |
|
Joe Fallo
#2 / 7
|
WildCard Searches... The SubTeam1 and 2 fields really ought to be the same field in a different table with a field for Type identifier. Then create 1 to many relationship between the two tables. By normalizing your tables up front, the searches and reports become
infinitely easier.
Just look at the trouble you are having now.
I would re-structure things rather than try to solve this specific problem.
You'll be better off on the long run.
--
HTH
Joe
(Remove nospam from my e-mail address)
Quote: > I have a table that has ProjectName, Team, SubTeam1, and SubTeam2. > I have on the main menu form several fields where the user can do
> wildcard searches. If a user enters something into the textbox
> SubTeam1, I want the query to search both SubTeam1 and SubTeam2 for that
> entry. Same goes if the user only enters something into the textbox
> SubTeam2.
> If the user enter something in both textboxes, I want the query to look
> both SubTeam1 and SubTeam2 fields for both textboxes. Hope that made
> sense...
> I have the following formula where if a user enters something into
> SubTeam1, it will onlysearch SubTeam1. I couldn't modify the formula to
> do what I said above:
> Field:
IIf(IsNull([Forms]![frmMainMenu]![SubTeam1]),1,InStr(1,[SubTeam1],[Forms]![ f
rmMainMenu]![SubTeam1],1))
Quote: > Criteria:
> >=Val("1")
> Thanks
|
| Sun, 09 Sep 2001 03:00:00 GMT |
|
|
|
Kurt Peterse
#3 / 7
|
WildCard Searches... Joe, I understand what you said. I did start out with the SubTeam to be its own
table with it linked to the main table by a ID.
Then I had the SubTeam subform in the main form for the user to edit the data.
The reason why I switched it what I have now is that the user wants the main
form to be viewed in datasheet view. That can't be done if there is a subform
in the main form. Therefore I eliminated the subform and just limited the user
to enter the SubTeam in two fields on the main table.
what can i do now?
Quote:
> The SubTeam1 and 2 fields really ought to be the same field in a different > table with a field for Type identifier. Then create 1 to many relationship > between the two tables. > By normalizing your tables up front, the searches and reports become
> infinitely easier.
> Just look at the trouble you are having now.
> I would re-structure things rather than try to solve this specific problem.
> You'll be better off on the long run.
> --
> HTH
> Joe
> (Remove nospam from my e-mail address)
> > I have a table that has ProjectName, Team, SubTeam1, and SubTeam2.
> > I have on the main menu form several fields where the user can do
> > wildcard searches. If a user enters something into the textbox
> > SubTeam1, I want the query to search both SubTeam1 and SubTeam2 for that
> > entry. Same goes if the user only enters something into the textbox
> > SubTeam2.
> > If the user enter something in both textboxes, I want the query to look
> > both SubTeam1 and SubTeam2 fields for both textboxes. Hope that made
> > sense...
> > I have the following formula where if a user enters something into
> > SubTeam1, it will onlysearch SubTeam1. I couldn't modify the formula to
> > do what I said above:
> > Field:
> IIf(IsNull([Forms]![frmMainMenu]![SubTeam1]),1,InStr(1,[SubTeam1],[Forms]![f
> rmMainMenu]![SubTeam1],1))
> > Criteria:
> > >=Val("1")
> > Thanks
|
| Sun, 09 Sep 2001 03:00:00 GMT |
|
|
|
Joe
#4 / 7
|
WildCard Searches...
Quote: > I have a table that has ProjectName, Team, SubTeam1, and SubTeam2.
> I have on the main menu form several fields where the user can do
> wildcard searches. If a user enters something into the textbox
> SubTeam1, I want the query to search both SubTeam1 and SubTeam2 for that
> entry. Same goes if the user only enters something into the textbox
> SubTeam2.
> If the user enter something in both textboxes, I want the query to look
> both SubTeam1 and SubTeam2 fields for both textboxes. Hope that made
> sense...
select distinctrow mytable.* from mytable where (trim([Forms]![frmMainMenu]![SubTeam1] & '') = '' or subteam1 like '*' & [Forms]![frmMainMenu]![SubTeam1] & '*' or subteam2 like '*' & [Forms]![frmMainMenu]![SubTeam1] & '*') and (trim([Forms]![frmMainMenu]![SubTeam2] & '') = '' or subteam1 like '*' & [Forms]![frmMainMenu]![SubTeam2] & '*' or subteam2 like '*' & [Forms]![frmMainMenu]![SubTeam2] & '*') --
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
|
| Mon, 10 Sep 2001 03:00:00 GMT |
|
|
|
Joe
#5 / 7
|
WildCard Searches...
Quote: > Joe,
> I understand what you said. I did start out with the SubTeam to be its own
> table with it linked to the main table by a ID.
> Then I had the SubTeam subform in the main form for the user to edit the data.
> The reason why I switched it what I have now is that the user wants the main
> form to be viewed in datasheet view. That can't be done if there is a subform
> in the main form. Therefore I eliminated the subform and just limited the user
> to enter the SubTeam in two fields on the main table.
Use DConcat from <http://members.ricochet.net/~jfoster/> to let the luser look at all the subteams even in datasheet view, but editing will still need to be done in the subform. --
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
|
| Mon, 10 Sep 2001 03:00:00 GMT |
|
|
|
Kurt Peterse
#6 / 7
|
WildCard Searches... Joe, that works well, except for one prob, the "and" in the formula.. using your formula, if the user puts something in both search boxes (like sales and marketing),
there is nothing for the result because it seems that both sales and marketing has to be in both
fields (subteam1 and subteam2) of a record for it to show up.
if i changed the "and" to a "or", the query will show all records if the user leaves the subteam
searches null but enters something into another search box (like to search for a projectname).
so, if i have the "and", all searches work, except if a user tries to do a 2 subteam search (sales
and mkt where the record only shows up if both sales and mkt are in the Subteam1 and Subteam2).
if i change the "and" to "or", it works if the user only does a subteam search. it wont work if
they leave the subteam search null and do other searches.
whatcha think?
Quote:
> > I have a table that has ProjectName, Team, SubTeam1, and SubTeam2.
> > I have on the main menu form several fields where the user can do
> > wildcard searches. If a user enters something into the textbox
> > SubTeam1, I want the query to search both SubTeam1 and SubTeam2 for that
> > entry. Same goes if the user only enters something into the textbox
> > SubTeam2.
> > If the user enter something in both textboxes, I want the query to look
> > both SubTeam1 and SubTeam2 fields for both textboxes. Hope that made
> > sense...
> select distinctrow mytable.*
> from mytable
> where (trim([Forms]![frmMainMenu]![SubTeam1] & '') = ''
> or subteam1 like '*' & [Forms]![frmMainMenu]![SubTeam1] & '*'
> or subteam2 like '*' & [Forms]![frmMainMenu]![SubTeam1] & '*')
> and (trim([Forms]![frmMainMenu]![SubTeam2] & '') = ''
> or subteam1 like '*' & [Forms]![frmMainMenu]![SubTeam2] & '*'
> or subteam2 like '*' & [Forms]![frmMainMenu]![SubTeam2] & '*')
> --
> WARNING: I cannot be held responsible for the above They're coming to
> because my cats have apparently learned to type. take me away, ha ha!
|
| Mon, 10 Sep 2001 03:00:00 GMT |
|
|
|
Kurt Peterse
#7 / 7
|
WildCard Searches... errr....hold onto what i said...i added an extra ( ) around your formula and changed the "and" to "or"....it seems to be working..i'll get back to you if it doesn't... Thanks sooooo much!
Quote:
> > I have a table that has ProjectName, Team, SubTeam1, and SubTeam2.
> > I have on the main menu form several fields where the user can do
> > wildcard searches. If a user enters something into the textbox
> > SubTeam1, I want the query to search both SubTeam1 and SubTeam2 for that
> > entry. Same goes if the user only enters something into the textbox
> > SubTeam2.
> > If the user enter something in both textboxes, I want the query to look
> > both SubTeam1 and SubTeam2 fields for both textboxes. Hope that made
> > sense...
> select distinctrow mytable.*
> from mytable
> where (trim([Forms]![frmMainMenu]![SubTeam1] & '') = ''
> or subteam1 like '*' & [Forms]![frmMainMenu]![SubTeam1] & '*'
> or subteam2 like '*' & [Forms]![frmMainMenu]![SubTeam1] & '*')
> and (trim([Forms]![frmMainMenu]![SubTeam2] & '') = ''
> or subteam1 like '*' & [Forms]![frmMainMenu]![SubTeam2] & '*'
> or subteam2 like '*' & [Forms]![frmMainMenu]![SubTeam2] & '*')
> --
> WARNING: I cannot be held responsible for the above They're coming to
> because my cats have apparently learned to type. take me away, ha ha!
|
| Mon, 10 Sep 2001 03:00:00 GMT |
|
| |
|
