user-defined wildcard query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to build a query that launches from a form that allows the
user to search a field using wildcards? for example, my database has
variable names in abbrv version and full name. I would like a user to be
able to type in something like GDP* and have all variables returned with the
letters gdp in them. i guess this concerns parameters, but i am new to
access. Could someone help?
 
Dear Polisci:

Is that your name or major?

First, searching for GDP* would find only those rows where the designated
field STARTS with GDP, not contains it. You would need *GDP* for that.

Please refer to the LIKE operator in help to do just what you have
described.

Tom Ellison
 
polisci said:
Is it possible to build a query that launches from a form that allows the
user to search a field using wildcards? for example, my database has
variable names in abbrv version and full name. I would like a user to be
able to type in something like GDP* and have all variables returned with the
letters gdp in them. i guess this concerns parameters, but i am new to
access.
Click to expand...

Just set the field's criteria to something like:

LIKE [Enter wildcard pattern]

This a fairly primitive way do set a query, so when you
start doing more advanced stuff, look to using a form text
box for users to enter the wildcard pattern. the criteria
will then be:

LIKE Forms!theformname.thetextboxname
 
What if I want the user to enter a simple word in the form, like "pier" but I
want to have the query use the wildcard "*" to search for words like "pier
one" or "1.3s pier"? How can I put the wildcard in the query so the user
doesn't have to deal with it?

Marshall Barton said:
polisci said:
Is it possible to build a query that launches from a form that allows the
user to search a field using wildcards? for example, my database has
variable names in abbrv version and full name. I would like a user to be
able to type in something like GDP* and have all variables returned with the
letters gdp in them. i guess this concerns parameters, but i am new to
access.
Click to expand...

Just set the field's criteria to something like:

LIKE [Enter wildcard pattern]

This a fairly primitive way do set a query, so when you
start doing more advanced stuff, look to using a form text
box for users to enter the wildcard pattern. the criteria
will then be:

LIKE Forms!theformname.thetextboxname
Click to expand...
 
OK, got that one figured out. But, what if I want to seach between number
ranges and the user may not put in the exact number. For example, if the
user wants to search between 99-005 and 02-030, and the data for those
numbers in the table is in the form of "99-0005" and "2-0030"?

K. Blythe said:
What if I want the user to enter a simple word in the form, like "pier" but I
want to have the query use the wildcard "*" to search for words like "pier
one" or "1.3s pier"? How can I put the wildcard in the query so the user
doesn't have to deal with it?

Marshall Barton said:
polisci said:
Is it possible to build a query that launches from a form that allows the
user to search a field using wildcards? for example, my database has
variable names in abbrv version and full name. I would like a user to be
able to type in something like GDP* and have all variables returned with the
letters gdp in them. i guess this concerns parameters, but i am new to
access.
Click to expand...

Just set the field's criteria to something like:

LIKE [Enter wildcard pattern]

This a fairly primitive way do set a query, so when you
start doing more advanced stuff, look to using a form text
box for users to enter the wildcard pattern. the criteria
will then be:

LIKE Forms!theformname.thetextboxname
Click to expand...
Click to expand...
 
I thought you wanted to let the user enter the wildcards?

The situation where you add wildcards, as you've probably
already figured out, is to use:
Like "*" & [enter partial string] & "*"
--
Marsh
MVP [MS Access]


K. Blythe said:
What if I want the user to enter a simple word in the form, like "pier" but I
want to have the query use the wildcard "*" to search for words like "pier
one" or "1.3s pier"? How can I put the wildcard in the query so the user
doesn't have to deal with it?

Marshall Barton said:
Just set the field's criteria to something like:

LIKE [Enter wildcard pattern]

This a fairly primitive way do set a query, so when you
start doing more advanced stuff, look to using a form text
box for users to enter the wildcard pattern. the criteria
will then be:

LIKE Forms!theformname.thetextboxname
Click to expand...
Click to expand...
 
K. Blythe said:
OK, got that one figured out. But, what if I want to seach between number
ranges and the user may not put in the exact number. For example, if the
user wants to search between 99-005 and 02-030, and the data for those
numbers in the table is in the form of "99-0005" and "2-0030"?
Click to expand...


Sorry, but that does not describe a **number** range, those
are character strings with digits in them. A "range" of
that nature will not do what a real numbers would.
Depending on the "range" you specify, it might include
entries that you don't want and leave out others that you do
want. This is because strings are compared left to right
(dictionary style), where as number values are compared
using their entire value.

You might be able to make a reasonable range comparison if
the strings are converted to a canonical form with the same
number of digits in each part (padded with leading zeros).
 
Marshall said:
Sorry, but that does not describe a **number** range, those
are character strings with digits in them. A "range" of
that nature will not do what a real numbers would.
Depending on the "range" you specify, it might include
entries that you don't want and leave out others that you do
want. This is because strings are compared left to right
(dictionary style), where as number values are compared
using their entire value.

You might be able to make a reasonable range comparison if
the strings are converted to a canonical form with the same
number of digits in each part (padded with leading zeros).
Click to expand...


I just thought of a way to do the conversion as long as
there is always one and only one dash in the string. Then
the WHERE clause would look like:

Format(Val(Left(InStr(field,"-")-1)),"000000") & "-" &
Format(Val(Mid(InStr(field,"-")+1)),"000000") BETWEEN
Format(Val(Left(InStr(start,"-")-1)),"000000") & "-" &
Format(Val(Mid(InStr(start,"-")+1)),"000000") AND
Format(Val(Left(InStr(end,"-")-1)),"000000") & "-" &
Format(Val(Mid(InStr(end,"-")+1)),"000000")
 
Yes, that conversion will work since there is alway and only a single dash in
the string.

Where is the conversion done? Is it in the expression for the query? I am
new to Access so any details are helpful.

Kelly
 
Ordinarily I'd just say to place the expression in the
query's WHERE clause, but I suspect you might not know what
I'm talking about ;-)

To use that in the query design grid, enter:
Format(Val(Left(InStr(field,"-")-1)),"000000") & "-" &
Format(Val(Mid(InStr(field,"-")+1)),"000000")
in a blank field in the query. Then enter this in the
Criteria below it:
BETWEEN Format(Val(Left(InStr(start,"-")-1)),"000000") &
"-" & Format(Val(Mid(InStr(start,"-")+1)),"000000") AND
Format(Val(Left(InStr(end,"-")-1)),"000000") & "-" &
Format(Val(Mid(InStr(end,"-")+1)),"000000")

If you switch the query to SQL View, it should look I what I
posted earlier.
 
Thanks! You were correct - I wouldn't have known what you meant. I'll give
it a try.

Marshall Barton said:
Ordinarily I'd just say to place the expression in the
query's WHERE clause, but I suspect you might not know what
I'm talking about ;-)

To use that in the query design grid, enter:
Format(Val(Left(InStr(field,"-")-1)),"000000") & "-" &
Format(Val(Mid(InStr(field,"-")+1)),"000000")
in a blank field in the query. Then enter this in the
Criteria below it:
BETWEEN Format(Val(Left(InStr(start,"-")-1)),"000000") &
"-" & Format(Val(Mid(InStr(start,"-")+1)),"000000") AND
Format(Val(Left(InStr(end,"-")-1)),"000000") & "-" &
Format(Val(Mid(InStr(end,"-")+1)),"000000")

If you switch the query to SQL View, it should look I what I
posted earlier.
--
Marsh
MVP [MS Access]


K. Blythe said:
Yes, that conversion will work since there is alway and only a single dash in
the string.

Where is the conversion done? Is it in the expression for the query? I am
new to Access so any details are helpful.

Kelly
Click to expand...
Click to expand...
 
Back
Top