Select Query Question

[Cordury]

I have a very simple select query:

SELECT tblMaster.[Utility Company], tblMaster.[Contact First Name], tblMaster.[Contact Last Name], tblMaster.[Office Phone], tblMaster.[Cell Phone], tblMaster.Fax, tblMaster.Email, tblMaster.[Company Web Page], tblMaster.Comments
FROM tblMaster
WHERE (((tblMaster.[Utility Company])=[Please Enter Utility Company Name]));

The problem is that, the user needs to key in the "Utility Company" the exact same way as it is stored in the table for the query to return any results.

Is it possible to use a combo box with the query, that way the user can select the "Utility Company" information they want to see? Or is there a way for me to update the SQL so the if the User just types in "Progress" but the the full name of the company is "Progress Energy" it will still return the information?

Thank you

 

[nberryman]

Create a new blank form. Put a combo box on the form using the wizard that looks up the company in the company table. Save the form and leave it open.

Open your query in design view and click in the criteia field for the Company ID, not the name. Click the Build button on the toolbar. In Expression builder double click Forms, Open forms and select you blank form with the combo box on and double click the combo box.

Close and save the query and return to the form. Add a command button that opens the query. Select the company anc click the button.

I think I got it all if not let me know

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[Cordury]

By company ID, do you mean the Access Auto Number? Because the ID was not part of my original query. I entered the ID and added the Expression in the Criteria but to no avail.

I followed your instructions but I am still having the same issues? Should I remove the "=[Please Enter Utility Company Name]"?



Thanks,
Cord

 

[lespaul]

yes you need to remove the "=[Please ...].

RIGHT CLICK the CRITERIA section of your query (where you just removed the "=[Please...]'), select 'Build', you will get the 'EXPRESSION BUILDER'. In the tree on the left, find forms, select either loaded forms or all forms, find the form you just created with the list of utility companies, find the combo box on the form and your where clause will look like:

[Forms]![Your Form Name]![Your combo Box Name]

Leslie

 

[Cordury]

Thank you Leslie,I entered:

'[Forms]![frmCombo]![ComboVendor]'

in the Criteria of the Utility Co Name field and I am getting an Error. Do I still have to add the Company ID (Autonumbered by Access) and add the above statement?
Thanks,
Cord

 

[lespaul]

Cord,

Why don't you send me your database and I'll take a look.


landrews@metrocourt.state.nm.us



Leslie