Hi,
I have a Search input form which collects from the user a person's name. I
am using LIKE with a "%" suffix in the SQL so that the user does not have to
type in the full name. When they hit the Search button, a query is run to
search the Person table for a match. This produces a recordset (I am using
ADO).

If the RecordCount is zero, they get a No Match message.

If the RecordCount is 1, a DoCmd.OpenForm is performed to open the person's
Detail form -- this passes the record's RecordID as the OpenForm's WHERE
argument (ie, "[RecordID] = n"). This works fine.

If the RecordCount is greater than 1, a DoCmd.OpenForm is performed to open
a Listing form which will list all matching records -- the user can then
select one from the list and use it to open the Detail form. This part is
NOT WORKING.

The DoCmd statement looks like this:-

DoCmd.OpenForm "frmPersonList", , , strWhere, acFormReadOnly

strWhere is declared as a String (I have also tried making it a Variant to
match the OpenForm argument). This variable is concatenated with the SQL
string to produce the original RecordSet referred to above -- ie:-

mySQL = "SELECT * FROM <table> WHERE " & strWhere

This successfully returns the correct RecordCount for all scenarios, so I
know it works.

For debugging, I have reduced the code to using just Surname in the search,
so the value of strWhere is simply (eg) "[Surname] LIKE 'Smit%'". This
results in a RecordCount that accounts for all the Smiths in the table.
However, when I pass strWhere with this value as the OpenForm argument, the
Listing form is opened as an empty form.

If I modify strWhere to "[Surname] LIKE 'Smith'", the Listing form opens
with all the Smiths listed. So I suspect that it is a quoting problem with
the "%" character. I have tried various alternatives -- single quotes,
escaped double quotes, Chr(39), etc, but nothing seems to work.

Can anyone see what I am doing wrong? Why does strWhere work in the SQL in
the parent form, but not when it is passed in OpenForm?

I am using Access 2003 under WinXP. I also have a related question, but I
will create another post for that (this one is to long already).

Thanks for any advice.

--
Cheers,
Lyn.

Perhaps a * instead of the %?

"Lyn" <lhancock (AT) ihug (DOT) com.au> wrote

Yes, that worked! I still find it puzzling -- in an SQL statement you have
to use "%" as the wildcard for LIKE ("*" doesn't work here). But in the
Help for OpenForm, the WHERE argument is described as a "string expression
that's a valid SQL WHERE clause without the word WHERE." Apparently that is
not strictly true.

Anyway, I have worked around the issue by leaving strWhere with "%" for the
SQL statement, then doing a Replace function on it ("*" for "%") before
applying it to the OpenForm statement.

A trap for young players.

Many thanks for the prompt assistance.

--
Cheers,
Lyn.

"MacDermott" <macdermott (AT) nospam (DOT) com> wrote

I think % is typically used in T-SQL, the language used by SQL Server, while
* is used in ANSI-SQL, which is used by Jet, which is native to Access.

"Lyn" <lhancock (AT) ihug (DOT) com.au> wrote