John Jason Jordan wrote:
On Sun, 16 Mar 2008 08:03:20 -0500 (Central Daylight Time)
"Terry Ehrhart" <[EMAIL PROTECTED]> dijo:
I have a query that I use in MS Access that will let me enter a single
character such as "a" and I will get a report of all the names beginning
with "a". I haven't been able to duplicate this query in Base. I need a
pop-up window to enter the character request. I would appreciate any
suggestions.
Open the database in Base - the main window that shows the icons on the
left for Tables, Queries, Forms, Reports. Click on Queries. Then click
on the Create Query in Design View.
When the design window pops up you will see a bunch of columns on the
bottom with a blank space above, and a popup will appear on top asking
you to select a table or query to base the query on. Select the table
that contains your field.
For the following instructions, change "column1" to the name of the
field on which you wish to do the selecting.
In the top row of the first column on the left enter:
SUBSTR( "Column1", 1, 1 )
In the Criterion row in the same column enter:
LIKE 'a'
I put 'a' there because you said you wanted all records where the name
begins with "a." In fact, I assume the names actually begin with "A,"
so the LIKE statement should be:
LIKE 'A'
Save the query and then run it. If it runs the do a Save on the entire
database (File > Save from the main window), because until you do that
the query is not saved in the file. If it doesn't work post back and
we'll troubleshoot. I'm very new to this and know little of how the
query design thing works, but I'll try my best to figure it out.
Also, for future consideration, the "1, 1" in the SUBSTR line tells the
query to start at the leftmost character of the field and look at only
one character. If, for example, you wanted it to look at three
characters beginning with the 10th character you would enter "10, 3."
Thought I would just expand on John's fine example.
You can also use a parameter for the actual criteria. So - if you
followed Johns instructions, in the last step instead of entering 'A' on
the criterion line you could enter :SearchString. Now when you run the
query a dialog box will pop up and ask you for the value of
'SearchString', you can enter a or A or B...
Two additional things to look at.
One - you really do not need the LIKE feature in the query as described
by John because you have used the SUBSTR function to limit the column
value - you could just use the search string. Since SUBTR( "Column", 1,
1 ) = 'A' works just as well.
Two - you could keep the LIKE function but drop the use of SUBSTR, then
you have the flexibility to expand on the single character search.
In the top row of the first column on the left enter:
"Column1"
In the Criterion row in the same column enter:
LIKE :SearchString
NOW when you run the query and are asked for the value for SearchString you
have the complete value of Column1 to work with and you use the wild card
feature of the LIKE command.
You want the records that begin with 'a' you enter: a%
If you want records that begin with 'aa' you enter: aa%
The character '%' matches zero or more characters in other words.
The character '_' matches any single character.
So, if you entered SearchString as: J_ne you would get matches on Jane and June
but not Janice.
Finally you can mix the two: J_n% would match on Jane, June and Janice but not
Lane.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
