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. 3 4155
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.
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.
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.
This discussion thread is closed Replies have been disabled for this discussion. Similar topics
2 posts
views
Thread by David |
last post: by
|
6 posts
views
Thread by beowulfs |
last post: by
|
15 posts
views
Thread by Thelma Lubkin |
last post: by
|
2 posts
views
Thread by Mike |
last post: by
| | |
3 posts
views
Thread by gavm360 |
last post: by
| | | | | | | | | | | | |