Search not working from form to Query...Re-Post 1

[CindiN]

TL ... I think I did the code the way you said, but this is what it looks like now. Did the system change things around? Now it doesn't search correctly even on one keyword in the form.

WHERE (((PositionTitlesTBL2.PositionTitle) Like "*" & [Forms]![Form2]![txtPositionTitle] & "*&quot) OR (((PositionDescriptionTBL2.GeneralDesc) Like "*" & [Forms]![Form2]![txtGeneralDesc] & "*&quot AND ((Nz([Forms]![Form2]![txtPositionTitle],"&quot)="&quot) OR (((PositionDescriptionTBL2.PrincipalDuties) Like "*" & [Forms]![Form2]![txtPrinDuties] & "*&quot AND ((Nz([Forms]![Form2]![txtGeneralDesc],"&quot)="&quot) OR (((YearsofExperienceTBL.YearsRequired) Like "*" & [Forms]![Form2]![txtYearsRequired] & "*&quot AND ((Nz([Forms]![Form2]![txtPrinDuties],"&quot)="&quot) OR (((Nz([Forms]![Form2]![txtYearsRequired],"&quot)="&quot);

 

[CindiN]

SunTsu,

I also tried your suggestion, and if I typed it correctly, and I think I did...see below. It says my expression is too complex. Should I have the Primary Key's in the query from the other tables? They are set as Foreign Keys in my main table that this whole query is based on. Does that make sense?

Thanks,
Cindi

 

[CindiN]

Sorry SunTsu, Forgot to attach the SQL Statement.

WHERE (((PositionTitlesTBL2.PositionTitle)=IIf([forms]![Form2]![txtPositionTitle]<>&quot;&quot;,InStr([forms]![Form2]![txtPositionTitle]),[PositionTitle])) AND ((PositionDescriptionTBL2.GeneralDesc)=IIf([forms]![Form2]![txtGeneralDesc]<>&quot;&quot;,InStr([forms]![Form2]![txtGeneralDesc]),[GeneralDesc])) AND ((PositionDescriptionTBL2.PrincipalDuties)=IIf([forms]![Form2]![txtPrinDuties]<>&quot;&quot;,InStr([forms]![Form2]![txtPrinDuties]),[PrincipalDuties])) AND ((YearsofExperienceTBL.YearsRequired)=IIf([forms]![Form2]![txtYearsRequired]<>&quot;&quot;,InStr([forms]![Form2]![txtYearsRequired]),[YearsRequired])));

Thanks can't tell you how much I appreciate all yours and TL's help, you guys are the greatest!
Cindi

 

[tlbroadbent]

I'd still like you to try what I suggested in my last post to the other thread. Copy my query and paste it into a new query. Execute it to see what results you get. With copy and paste you can avoid retyping everything and possibly making mistakes. Is that OK? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CindiN]

TL...Forget the first post above, that was from last night before you left your last message on the previous post.

I did the steps you wrote in the last post, last message. Here is a copy...notice the 3 items I changed (with the ******* on both sides of the text), because as I mentioned before, I was using the wrong table with the wrong Primary Key, so I changed them here from your original. It still only provides one field of results.

SELECT
d.PositionID,
t.PositionTitle,
d.GeneralDesc,
d.PrincipalDuties,
*********d.[ExperienceID#],**********
y.YearsRequired
FROM YearsofExperienceTBL As y
INNER JOIN (PositionTitlesTBL2 As t
INNER JOIN PositionDescriptionTBL2 As d
ON t.PositionID = d.PositionID)
ON y.[ExperienceID#] = d.[ExperienceID#]
WHERE (t.PositionTitle Like &quot;*&quot; & [Forms]![Form2]![txtPositionTitle] & &quot;*&quot;
OR [Forms]![Form2]![txtPositionTitle] Is Null)
AND (d.GeneralDesc Like &quot;*&quot; & [Forms]![Form2]![txtGeneralDesc] & &quot;*&quot;
OR [Forms]![Form2]![txtGeneralDesc] Is Null)
AND (d.PrincipalDuties Like &quot;*&quot; & [Forms]![Form2]![txtPrinDuties] & &quot;*&quot;
OR [Forms]![Form2]![txtPrinDuties] Is Null)
AND (y.YearsRequired Like &quot;*&quot; & [Forms]![Form2]!********[txtYearsRequired]******* & &quot;*&quot;
OR [Forms]![Form2]!******[txtYearsRequired]******* Is Null);

I also tried it with your other change, the one with the OR nz, etc.

Thank you sooo much, I really appreciate it.
Cindi

 

[tlbroadbent]

The query looks good. It should work. Have you verified that the criteria you entered is found on the tables? You ran the query with no criteria. Have you tried adding criteria to one column at a time to see where the query might be failing or if it is in fact working properly? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CindiN]

Yes, I verified that the criteria is in the tables. I ran the query with no criteria and I get ALL the records. I have added criteria to one column at a time and I get the correct results. It's only when I add criteria in two different columns that should pull up two different records that it does not work. Another words, type Analyst in the PositionTitle column and type Training in the PrinDuties column and the results should be 4 records, 3 that have Analyst and 1 that has Training.

Thank You,
Cindi

 

[tlbroadbent]

I've been a little dense on this one. Sorry I'm so slow to catch on.

It sounds like you want to select records match one or more criteria. The query we've been working on selects a record that meets all four criteria. The fix is easy. Change each AND in the where clause to OR. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CindiN]

Sorry TL, that didn't work either. Now I get ALL the records again. Even when I only search on one field, I get ALL the records this time.

I'm sorry to be so much trouble.
Thanks for all your help!
Cindi

 

[tlbroadbent]

I need some sleep. I've not been thinking clearly on this problem. For that I apologize.

Let's take a saner and hopefully simpler approach to this query. We'll break this down to component parts and make sure the components work before the final assembly.

Create the following queries. Test each one to make sure it return the records it should. Rename according to your standards.

qryPositionDesc
SELECT
PositionID, GeneralDesc,
PrincipalDuties, [EducationID#]
FROM PositionDescriptionTBL2
WHERE (d.GeneralDesc Like &quot;*&quot; & [Forms]![Form2]![txtGeneralDesc] & &quot;*&quot;
OR [Forms]![Form2]![txtGeneralDesc] Is Null)
AND (d.PrincipalDuties Like &quot;*&quot; & [Forms]![Form2]![txtPrinDuties] & &quot;*&quot;
OR [Forms]![Form2]![txtPrinDuties] Is Null);

qryYearsExperience
SELECT [ExperienceID#], YearsRequired
FROM YearsofExperienceTBL
WHERE y.YearsRequired Like &quot;*&quot; & [Forms]![Form2]![txtEducation] & &quot;*&quot;
OR [Forms]![Form2]![txtEducation] Is Null;

qryPositionTitle
SELECT PositionID, PositionTitle
FROM PositionTitlesTBL2
WHERE (t.PositionTitle Like &quot;*&quot; & [Forms]![Form2]![txtPositionTitle] & &quot;*&quot;
OR [Forms]![Form2]![txtPositionTitle] Is Null)

Now create a new query that will use the three queries created above. This should produce the desired result.

qryFinalResult
SELECT
d.PositionID,
t.PositionTitle,
d.GeneralDesc,
d.PrincipalDuties,
d.[EducationID#],
y.YearsRequired
FROM qryPositionDesc As d
INNER JOIN qryYearsExperience As y
ON d.[ExperienceID#] = y.[ExperienceID#]
INNER JOIN qryPositionTitle As t
ON d.PositionID = t.PositionID; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CindiN]

TL, All of the above queries work individually (without any values entered on the form, or with just one value entered on the form), Again, the exception is on the first query, when I enter a value on the form in the GeneralDesc field AND in the PrinDuties field, then hit the SEARCH button....I get nothing. Entering a value in GeneralDesc then hitting SEARCH...works great. So does entering a value in PrinDuties then hitting SEARCH...works great. But they just don't work at the same time. Of course, the 1st query is the only one I can test with 2 values on the form at the same time.

I didn't test the last one, with all three together.

Thanks,
Cindi

 

[CindiN]

TL.... Would this be better for you to see the whole thing if I e-mailed it to you? Maybe it's not in the query, could it be in the form or the table?

Cindi

 

[tlbroadbent]

Please do. tlbroadbent@hotmail.com Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CindiN]

OK, sent...Thank you, Thank you, Thank you!!!

Cindi

 

[CindiN]

TL..Did you get it this afternoon 9/23? Not rushing you, just checking cause the first time I sent it didn't go through.

Thanks,
Cindi