Create Temporary Report based on User Input on Form 4

[CindiN]

I have a DB of Job Positions and I am trying to create a form that the users can enter keywords in multiple fields, then hit a search button to receive a report with all the Positions that match all their criteria.

I've tried binding the form to the Table, the Query and not binding it at all. The fields in the form are all unbound. I have a SEARCH button that takes me to a Report, but no matter what I have in the form fields, the report always comes up with ALL the Positions. I've also tried several ways to put criteria in the query..."*", with [Keyword?] or [forms]![formname]![fieldname]; but I don't know how to do the [forms]!... criteria making the [fieldname] so it will pickup parts of words, etc. (does that make sense?)

It is probably something right in front of my nose, but I'm just not seeing it. I have been all thru MS Help, The FAQ's and Searches on this site and even some from MS's knowledge base. Can anyone help explain this in beginners terms. I am a "LITTLE" familiar with SQL, so I'm looking for the easiest way, without a Parameter Box poping up.

Thank you sooo much.

 

[CindiN]

I took the .[Value] off all the criteria's. Now it still works if I search on one field, but when I enter info. in two fields, it doesn't give me any records.

I did have the default value of all the unbound text boxes in the form defined with "" so it would cover nulls. But I took them out and that didn't make a difference either.

Hmmmm???
Thanks!
Cindi

 

[tlbroadbent]

Add a test for the value of the text boxes being NULL as highlighted below. Note: I used aliases and changed the alignment to aid readability.

SELECT
d.PositionID,
t.PositionTitle,
d.GeneralDesc,
d.PrincipalDuties,
d.[EducationID#],
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 "*" & [Forms]![Form2]![txtPositionTitle] & "*"
OR [Forms]![Form2]![txtPositionTitle] Is Null)
AND (d.GeneralDesc Like "*" & [Forms]![Form2]![txtGeneralDesc] & "*"
OR [Forms]![Form2]![txtGeneralDesc] Is Null)
AND (d.PrincipalDuties Like "*" & [Forms]![Form2]![txtPrinDuties] & "*"
OR [Forms]![Form2]![txtPrinDuties] Is Null)
AND (y.YearsRequired Like "*" & [Forms]![Form2]![txtEducation] & "*"
OR [Forms]![Form2]![txtEducation] Is Null); 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...still no records at all. :-(

Thanks,
Cindi

 

[tlbroadbent]

How many records do you get with none of the text boxes filled in? How many records are returned if you remove all criteria? Is YearsRequired a numeric data type?

You might try the following change.

From:
OR [Forms]![Form2]![txtPositionTitle] Is Null

To:
OR Nz([Forms]![Form2]![txtPositionTitle],"&quot = "" Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[suntsu]

try using this as your criteria for each field:

=iif([forms]![frmYourForm]![txtYourField]<>&quot;&quot;,InStr([forms]![frmYourForm]![txtYourField]),[qryTheQueriesFieldName])

I have had to use this on a report criteria form, where each field can be passed criteria to filter on, and it's all working fine

 

[CindiN]

TL....Great questions, very interesting results! If I do a search without anything in any fields on the form....I get ALL records!

If I take out all the criteria and do a search without any data in fields, I get ALL the records!

If I add data into two fields, I get ALL the data!

The YearsRequired field is a text field that comes from a Table called YearsofExperienceTBL that has a primary key field called ExperienceID# and its data type is AutoNumber. I did make a mistake and put the EducationID# from another table in this query to go with the YearsRequired. But I just fixed that, and it still didn't change anything.

I'm trying your new statement now.

Your's too SunTsu!

Thank you guys...why do I feel this is going to end with a result that I've done something really obvious and I look stupid!! LOL
Cindi

 

[tlbroadbent]

If it doesn't work this time, please post your query again.

Thanks, 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 ... 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 &quot;*&quot; & [Forms]![Form2]![txtPositionTitle] & &quot;*&quot) OR (((PositionDescriptionTBL2.GeneralDesc) Like &quot;*&quot; & [Forms]![Form2]![txtGeneralDesc] & &quot;*&quot AND ((Nz([Forms]![Form2]![txtPositionTitle],&quot;&quot)=&quot;&quot) OR (((PositionDescriptionTBL2.PrincipalDuties) Like &quot;*&quot; & [Forms]![Form2]![txtPrinDuties] & &quot;*&quot AND ((Nz([Forms]![Form2]![txtGeneralDesc],&quot;&quot)=&quot;&quot) OR (((YearsofExperienceTBL.YearsRequired) Like &quot;*&quot; & [Forms]![Form2]![txtYearsRequired] & &quot;*&quot AND ((Nz([Forms]![Form2]![txtPrinDuties],&quot;&quot)=&quot;&quot) OR (((Nz([Forms]![Form2]![txtYearsRequired],&quot;&quot)=&quot;&quot);

 

[tlbroadbent]

The code isn't at all like what I posted. You've mixed in some of SunTsu's recommendations. The ANDs and ORs are incorrect. Some of the criteria are out of order and the grouping within parentheses is wrong.

It is very difficult to keep the columns and fields straight in a query like this. And Access doesn't help when it reformats the query and adds more parentheses.

I'd like you to try the following if you will.

1- Copy the complete query I posted earlier this evening - the query with the OR condition that I bolded.

2- Then create a new query in Access but don't add any tables. Switch to SQL view and paste my query into the SQL view.

3- Open the form and fill in the text boxes - 1 or 2 or 3 or all of them.

4- Execute the new query and let me know the result. 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....I'm sorry for the mistake! See new post.