Parameter Query w/3 Options...due YESTERDAY of course!

[ALewis06]

I have pulled data from external data source via ODBC into a table, fine. I have created a simple query to display the data as desired. Now I need to create a self-serve vehicle so that people can search the data based on their criteria and from that the customer needs to be able to produce a report from the results. I want the customer to be able to select from any combination of 3 options: 1) FirstName LastName 2) Company Name or 3) Account Number.

Do I start by building a parameter query?

Any help would be greatly appreciated. Need it asap!

 

[Trevor G]

Create a form and place some textboxes or possibly comboboxes and let the users interface with the form rather than the query. Use the query criteria to use the form elements and then the result of the query can become a report. Add a commandbutton to run the process.

 

[jdraw]

You may get some ideas from
http://www.fontstuff.com/access/acctut08.htm

 

[ALewis06]

So no parameter query setup necessary? Just create a form based on the query as is? I'm quite new to creating a form as a search box and I'm not exactly sure I thoroughly get what you're saying when you say "Use the query criteria to use the form elements and then the result of the query can become a report." but I will dig deeper.

 

[Trevor G]

Create the form with the textbox as mentioned and name them according to your field names so perhaps txtCompany save the form as frmSearch, then open the query in design view. Select the criteria below the Company field, then use the Build Wizard to find the form (On left double click forms and look for the frmSearch form) and the txtCompany field then save the query Next create the report based on the query and add a button on the form to open the report.

You then either type in the company or if you prefer use a drop down which lists all the company names and use that as the search point.

 

[ALewis06]

And will that method work if I need to create the option for someone to be able search on any of the 3 criteria I mentioned? Will I be able to do all of that in one form: provide the option to select based on Company, FirstName/LastName or Account Number?

 

[Trevor G]

Yes you just add the other textbox and in the criteria of the query you use the same process as mentioned but you will also have to add a Like statement as some of the options may not be choosen and you may want to return all the data.

Like Forms![frmSearch]![txtCompany] &"*"

The above is the example of the criteria for the Company field

 

[ALewis06]

Trevor G I built the form using the same 3 fields that are in the query ie. I added the 3 textboxes to the form design grid and used the corresponding fields in the query as the data source. Here is my dilemma: the query has 72,000 rows in it and when I preview the form, the fields do not come up blank so that an end user could enter his own paramters--the fields are pre-populated with the 72,000 results from the query. Until I get the form right I cannot point it to a report. Help

 

[Trevor G]

The form you designed should be unbound textboxes not bound to a table as they are free to type. The form is not bound to a query or table it is unbound.

See example attached.

The search form has 3 textbox each has been named txtLastName, txtDepartment, txtOffice the query has the criteria using the like statement the report shows the results.

 

[ALewis06]

That sample db was extremely helpful! Thanks. I am very close now. For some reason the parameters work fine when I run the query but when I run the report there's an additional parameter dialog box that shows up called "Name". I read somewhere that you shouldn't use a field called "Name" but I had no choice--this came from a table I downloaded from the server and can't change it. I did rename it in the query but when I run the report, there's still a parameter box showing up. I designed the search form just as you did in your sample db but the results are not coming back correctly.

 

[Trevor G]

In design view on the report show the properties and at the top click the drop down and select the name field and you should be able to see it, then look at the properties of the report again and select the control source which should be the query name and click the elipse button on the right then look for the field name. place the following before it txtName:Name that will give it an expression name which you can use to get ride of the report field showing name.

If this doesn't work see if you can upload and extract of your database and I will take a look.

 

[ALewis06]

The field Name doesn't show up in the drop down list--it shows up only as what I renamed it to which is Community. I wish I could upload a copy to get more of your expert advice but it is proprietary data I pulled from our server.

 

[Trevor G]

Try creating a new report to see if it works.

 

[RainLover]

Lewis

If you really need help then delete the data and replace it with some dummy stuff. Then zip and upload that.

I tested Trevor's sample and it works for me.

 

[ALewis06]

I have loaded my db with test data and attached it. The query and report both work fine. But for some reason the form does not. When I put in any set of parameters, all of the results come back instead of the parameterized ones.

Trevor: I have studied every part of the design of your search form in your sample db...what am I missing?

ALewis

 

[Trevor G]

Can you convert your database to 2003 version.

 

[ALewis06]

This is so frustrating! I've taken so many things out of the database that I THINK would prevent the db from converting down to 2003, but it's still not allowing it. I removed the macro, checked the fields in the table, and even removed the parameter expressions in the query but no dice. Ugh!!!!!!!

Is there someone with Access 2010 that can help me??

 

[vbaInet]

Did you follow this?

http://office.microsoft.com/en-us/a...se-in-an-earlier-file-format-HA010341553.aspx

 

[Trevor G]

This is so frustrating! I've taken so many things out of the database that I THINK would prevent the db from converting down to 2003, but it's still not allowing it. I removed the macro, checked the fields in the table, and even removed the parameter expressions in the query but no dice. Ugh!!!!!!!

Is there someone with Access 2010 that can help me??

I asked if you can convert it as the forum generally suggest the database be in 2003 format.

I have Office 2010 so I will take a look for you.

Possible reasons why it wont convert down is that you maybe using something like a calculation field in a table which comes with Access 2010 but you can't do that in Access 2003 or 2007.

 

[ALewis06]

I understand Trevor. I'm certainly not frustrated with you--just the fact that I'm not seeing the answer. vbaInet: I am looking at that link now, thx