Access parameter query using wildcards 1

[Menace4258]

Hey, I have to design queries for a MS Access Database. One query is a search by file name. So I need to use a parameter query so the user can enter a part of the file name, and also wildcards to search the full file name.

I've tried several different syntax possibilities, but nothing seems to be working. If anyone has any suggestions they would be greatly appreciated. Here's some of the syntax options I've attempted so far.

'FILE_NAME' = *[Enter Filename]*
'FILE_NAME' = *"[Enter Filename]"*
'FILE_NAME' = "*[Enter Filename]*"
'FILE_NAME' = "*[[Enter Filename]]*"

And some other possibilities.

 

[Bob44TheCat]

Hi Menace,

You'd have done better to post this in the Access Queries forum but, as it's here - in the Criteria put
Like [Enter full Filename or partial Filename plus *]
The bit in the square brackets is the prompt that the user actually sees, so you can phrase it however you want. You might also need to do a bit of user training to get them to understand that they can enter the asterisk anywhere they need it. Obviously, if they put in the full filename, they don't need the wildcard at all.

Also, if you do a search of the Access Queries forum you will find several opinions, from people much more qualified than me, recommending that you steer clear of parameter queries. As a quick and dirty starting point though, you should be OK with this.

Hope this helps,

Bob.

 

[SkipVought]

That won't work since the syntax is different for a parameter query...

parameter query...
[tt]
Where MyField = [Enter Value for MyField]
[/tt]
contains a value
[tt]
Where MyField Like '%TheValue%'
[/tt]
this can be done using VB code

....
sSQL = sSQL & "Where MyField Like '%" & TheValue & "%' "
....

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Bob44TheCat]

Errr, Skip -
I hate to ask, but: Am I missing something?
I'm assuming FILE_NAME is a field in an Access table, and Menace is using a bog-standard, out-of-the-box Access parameter query to search for records using a partial filename plus a wildcard (actually that should have been wildcards, plural). If my assumptions are right, that works in Access 97 and 2K, at least.

Cheers,

Bob.

 

[SkipVought]

try

[tt]
Like [What?]
[/tt]
and enter lookup data WITH wildcard

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Lilliabeth]

The user won't be allowed to enter expressions in the dialog box.

Put this in the criteria cell for the field in the query design:

Like*&[Enter any part of the field]&*

 

[SkipVought]

In the QUERY GRID...
[tt]
Like [What?]
[/tt]
the user enters
[tt]
XYZ%
[/tt]
if that's not feasible, then you're stuck with VBA & SQL.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Lilliabeth]

Or you can concatenate the wildcards to the beginning and end of the parameter prompt so the user doesnt have to remember to type the *s.
Like *&[Enter any part of the file name]&*

Or am I missing something?

 

[SkipVought]

Lilliabeth,

Finally figured out what you have been trying so patiently to convey!

==> *

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Lilliabeth]

Thank you! You're the best!

 

[SkipVought]

I continue to learn, and I have a looooooong way to go!

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]