Parameter query with wildcard expression 3

[miller65ghg]

I have a parameter query which prompts for a job skill category. In the Criteria row I have:

[Enter job skill:]

A sampling of some of the job skills for that field which the query could return are:

Windows 95/98
Windows NT 4.x Server
Windows 2000 Professional
Windows 2000 Server

If I enter one of the four above, EXACTLY, into the Enter Parameter Value dialog which appears upon running the query, then I get complete query results.

However, I don't want the user to have to type the whole text string. Problem is that if I enter a small text string with wildcard character (e.g., "Win*&quot into the dialog prompt, I get a return query with no records.

I would like to create a paramter query in which the prompt allows for the inclusion of wildcard characters. Any suggestion?

Thank you,

Mark

 

[ghubbell]

Hi Mark,
Make a single simple form, unbound- not attached to anytable, set its pop up property to Yes, sizeable borders with a controlbox and min/max buttons to min only. With the wizard on (wand & stars depressed) place a combo box on your form and choose I want the combo to look up data from a table. Next, select the table that your query is based off of. Name your new combobox "CboSelect".
Take a moment and look at its "limit to list" property. To get there, double-click on the combobox and a properties dialog box will pop up. Select the Data tab. This must be set to Yes.
Again using the wizard on your form create a command button: (under miscellaneous-Run query, and choose your query name.
Close and save your form.

In your query in your criteria row, type:
=Forms![EnterTheNameOfTheFormYouJustMade]![CboSelect]

Close and save your query.
Open your form and select your choice. Click your command button and your query should run! You can do it!





Gord
ghubbell@total.net

 

[miller65ghg]

Gord,

Thanks for the reply. I will try your recommendation and tell you how it goes.

Mark

 

[miller65ghg]

Gord,

I created a Pop Up form per your instructions, however, when I click the command button to run the query, after having made a selection, I get a return recordset with no records. I must have not set something properly.

Is there a way to create a query such that inputs allow wildcards? So far I haven't found one. Right now I'm limited to running a general query, then applying a filter to the recordset to get more specific.

Thank you,

Mark

 

[miller65ghg]

Gord,

Another follow up:

If I put

Like "*" And [Enter text:] And Like "*"

into the Criteria row of my query, I get a recordset with no records, after entering in a valid text string.

Can you tell me what is wrong with that expression?

Thank you,

Mark

 

[ghubbell]

Hi Mark, thanks by the way... I may have not been explicit enough about where you place the criteria so we'll just run through this...

If your combo is based off of the table in the query, and the table in the query has a field (lets call it "skills&quot, and if you run the query with nothing in the criteria line below "skills" you would see results like:
Windows 95/98
Windows NT 4.x Server
Windows 2000 Professional
Windows 2000 Server
in the field called "skills"
(hopefully?!)

If our combo has 1 column, column 1 is the bound column, it is limited to list, and when displayed should show:
Windows 95/98
Windows NT 4.x Server
Windows 2000 Professional
Windows 2000 Server
(hopefully!!)

If =Forms![EnterTheNameOfTheFormYouJustMade]![CboSelect] is placed in the first criteria line just below the field "skills", this should work!

The results of this query should only give you one "skill" as a result. If you need to see more or would like to see "anything with win*" then we'll have to build a different query to "drive" the combo, called a Union Query.

Let's get this to go first.

Also, to make it easier for your users to choose from your combobox, do this:

Form in design view, double-click on the black square top left in the view. This brings up the forms properties dialog. Go to the Events tab, find the On Open event, select "Event Procedure", then click on the ellipse "..." button just beside to open up Visual Basic. It will label up a sub for you like Form_Open(Cancel as integer) and end it with an "end sub"

Between these two lines add:

OnErrorResumeNext
Me.CboSearch.SetFocus
Me.CboSearch.Dropdown

Close & save and lets try some more! I'll be watching! Gord
ghubbell@total.net

 

[Quehay]

miller:

The combo box is a great way to go (nice tutoring Gord!)--it gives users discrete choices. Re: What's wrong with the prompt?

Try:

[tt]Like "*" & [Enter the value desired]& "*"[/tt]

*You're building a string rather than a logical test

 

[Quehay]

The several skill thing would be feasible with a multiselect listbox and a SQL IN (var1,var2) sort of approach.

 

[miller65ghg]

Quehay,

Thank you! Works great! As you probably read, I had been trying:

Like "*" And [Enter text:] And Like "*"

which didn't work. Your method is exactly what I was looking for.

Gord, thanks again for your help. In the process I have learned about Combo boxes, and am already thinking of ways to incorporate them into my DB.

Mark