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.

 

[Psychpt]

create a query from the values in the form and attach the report to the query.

HTH

 

[Rhonin]

When you run your report, if I understand you correctly, you can specify the criteria in the query by using
Forms!FormName!FieldName in the criteria line of the selected field.

I quite often use a form with Start and End date fields and check boxes the user selects. My report has a query that uses the form fields to select data (7 of them. Works well.

 

[CindiN]

Thank you so much for the reply. I did what you suggested and it works well, with one exception. I added .Value"*" to the end of the criteria so that it will find keywords that might have text after it. However, this doesn't take care of any keywords that might have text in front of it. My criteria is [forms]![FormName]![FieldName].[Value]&"*". Is there a way to put the "*" in front of this statement?
Also, does it matter if you put brackets in there or not?

 

[CindiN]

Does anyone know the answer to this question posted above, I sure would appreciate it, I'm really stuck. Thank You!!!

 

[suntsu]

Hi CindiN,

You will need to use the InStr function,
I just created the following sql to show you an example.

this is the sql for a query, that uses the value in a forms textbox as the criteria for a report.

SELECT myTable.myField
FROM myTable
WHERE (((InStr([myField],[forms]![myForm]![MyFormsTextBox]))=True));

Hope this helps you out a little.

SunTsu

 

[suntsu]

Further to my last post :

if the value in the forms text box is "soft"
you will get the following results from the recordset:

microsoft;
software;
asoft1;

(Anything that contains your criteria will , no matter where in the field it lives).

SunTsu

 

[CindiN]

Thank you SunTsu! Am I supposed to put this statement in the querys' criteria row or in the Form on the Search Button's OnClick event??? If it does go in the query, do I repeat the Select statement for every field's criteria on the query? Also, I am getting a syntax error saying I need parenthesis in the subquery....can't figure it out. Here's my statement:
SELECT [PositionTitlesTBL2].[PositionTitle] FROM [PositionTitlesTBL2] WHERE(((InStr([PositionTitle],[Forms]![Form2]![txtPositionTitle]))=True));

I really appreciate your help on this, sorry I'm still learning.

Thanks!
CindiN

 

[suntsu]

enter the sql criteria for each field that you need to filter on in the query design. Then use this query as the recordsource for the report.

You will then want to use a command button on the criteria form to open your report.

Let me know if this works out for you.

SunTsu

 

[CindiN]

Thank you, I'll try it. Do you know why I'm getting the syntax error I mentioned above?

CindiN

 

[suntsu]

I managed to recreate your error by putting the sql string as the criteria for that field, you don't want to do this.

create a new query, in design view select view then SQL view (from the toolbar). Just paste the sql stright into there, then look back to the query design to see how access shows the design (in a friendly way).

You should see two "Fields",

field1 = PositionTitle
show = yes
criteria = blank

field2 = InStr([PositionTitle],[Forms]![Form2]![txtPositionTitle])
show = no
criteria = "True"

Use this as a base to work from, you will need to copy this for each field that you want to filter on.


Good Luck,


SunTsu

 

[Psychpt]

if you put LIKE 'soft*' it will give you everything starting with soft and so on.

HTH

 

[CosmoKramer]

CindiN,

Is there a way to put the "*" in front of this statement?

Try:

"*" & [forms]![FormName]![FieldName].[Value] & "*"

 

[suntsu]

Please see my earlier posts (ON THIS PAGE), you must use the InStr function where your search string can be anywhere in the field.

"*" & [forms]![FormName]![FieldName].[Value] & "*"
will not work.

SunTsu

 

[CosmoKramer]

SunTsu

I beg to differ, it will work....I use wildcard syntax like that all the time and it works great for me. It's simply a matter of two ways to get the same results.....

 

[suntsu]

My apologies Cosmo, you were 100% correct and my inexperience is now known ;P

I had tried to do the same in the past but had obviously entered the wrong syntax.

Theres one vote for Cosmo :I

 

[CindiN]

You have both been a great help, unfortunately, it still seems that I cannot enter data in two or more fields on the form where it will return two different records. I even tried putting the criteria on each of the "OR" lines so it would find field1 OR field2. (Which by the way, there aren't enough "OR" lines for all my fields) For example, if I enter data in the PositionTitle field that would return record #1, and I also enter data in GeneralDesc field that would return record #2, then I get nothing. But if I search on them separately, then I get the correct information, but on two different searches. Got any ideas?

Thank you so much for taking the time to help me!!! :-{}

 

[CosmoKramer]

CindiN,

Could you post the SQL code for your query?

 

[CindiN]

Cosmo,

This is a copy of the SQL view for the Query that the Form launches. I cut it down quite a bit, because I have about another 10 fields to add to this query that are on the form.

Currently the statements are all on the same criteria lines, that doesn't work. I tried putting them each on the next OR lines under Criteria, that didn't work. I even tried doing it the way SunTsu suggested above, that didn't work.

It's really weird! Thank you for helping me!
CindiN

SELECT PositionDescriptionTBL2.PositionID, PositionTitlesTBL2.PositionTitle, PositionDescriptionTBL2.GeneralDesc, PositionDescriptionTBL2.PrincipalDuties, PositionDescriptionTBL2.[EducationID#], YearsofExperienceTBL.YearsRequired
FROM YearsofExperienceTBL INNER JOIN (PositionTitlesTBL2 INNER JOIN PositionDescriptionTBL2 ON PositionTitlesTBL2.PositionID = PositionDescriptionTBL2.PositionID) ON YearsofExperienceTBL.[ExperienceID#] = PositionDescriptionTBL2.[ExperienceID#]
WHERE (((PositionTitlesTBL2.PositionTitle) Like "*" & [Forms]![Form2]![txtPositionTitle].[Value] & "*&quot AND ((PositionDescriptionTBL2.GeneralDesc) Like "*" & [Forms]![Form2]![txtGeneralDesc].[Value] & "*&quot AND ((PositionDescriptionTBL2.PrincipalDuties) Like "*" & [Forms]![Form2]![txtPrinDuties].[Value] & "*&quot AND ((YearsofExperienceTBL.YearsRequired) Like "*" & [Forms]![Form2]![txtEducation].[Value] & "*&quot);

 

[tlbroadbent]

Cindi,

Either change the value property to text or leave it off. The Text property is the current contents of the control. The Value property is the saved value of the text box control.

So you are searching for the contents of the text boxes before you typed anything into them. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.