Problem passing WHERE string in DoCmd.OpenForm

Lyn

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.

Nov 13 '05 #1

MacDermott

Perhaps a * instead of the %?

"Lyn" <lh******@ihug.com.au> wrote in message
news:cr**********@lust.ihug.co.nz...

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.

Nov 13 '05 #2

Lyn

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" <ma********@nospam.com> wrote in message
news:fs*************@newsread3.news.atl.earthlink. net...

Perhaps a * instead of the %?

"Lyn" <lh******@ihug.com.au> wrote in message
news:cr**********@lust.ihug.co.nz...

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.

Nov 13 '05 #3

MacDermott

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" <lh******@ihug.com.au> wrote in message
news:cr*********@lust.ihug.co.nz...

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" <ma********@nospam.com> wrote in message
news:fs*************@newsread3.news.atl.earthlink. net...

Perhaps a * instead of the %?

"Lyn" <lh******@ihug.com.au> wrote in message
news:cr**********@lust.ihug.co.nz...

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.

Nov 13 '05 #4