Database Results Filter

[Guest]

I have a form on my website that submits data to a database using the
standard FP database wizard for ASP. It works as designed. I'm trying to
pull data from the DB to display on another page, but I'm having trouble
getting it out.

The data I want to filter on are 2 text fields (Meet and Session). An
example of each: value for Meet is "12-13 November CGBD A/BB Meet" and value
for Session is "Saturday Morning / Prelims". I want to display the Last_Name
and First_Name for all records that match those values for Meet and Session.

I've tried all combinations of the values with and without spaces and _ and
without the slash as well as all combinations of equals, contains, etc and
can't get the records to display. I've checked the entries in the DB and the
spellings etc - all are correct. Are there restrictions on the data format
(it's text) and/or the filter string?

I can make it work by assigning numeric values for Meet and Session, but I
need the text values for the Confirmation Page upon submit of the form.

Any ideas (besides a special script - I need to keep it within FP).
Thanks,
Michael

 

[Kathleen Anderson [MVP - FrontPage]]

Michael:
Try this to start:
http://www.spiderwebwoman.com/tutorials/doubledropdown.htm

 

[Guest]

Kathleen,
Thanks for your reply, but that's not what I'm trying to do.
It's a sign-up procedure - user enters name and picks the meet and session
they want to volunteer for - it's not interactive. The DB table has 6 fields
populated by the form (text box for the names, user selections for the rest).
I want to display a list of the names in a table on another page that match
criteria in 2 of the fields. In other words, if field A = abc AND field B =
xyz, then display the Name.

This appears to be easily supported using the DB Results Wizard. However,
it looks like I can't get a match of the data from the query using text for
values. If I replace the values in the fields with numbers and change the
SQL statement accordingly, , it works fine. But, I need the text values for
the confirmation page.

Here's the SQL statement ... SELECT * FROM Volunteers WHERE (Meet = '12-13
November CGBD A/BB Meet' AND Session = 'Saturday Morning / Prelims') ORDER BY
ID1 ASC ... maybe that's the problem?

Thanks for your help,
Michael

 

[Kathleen Anderson [MVP - FrontPage]]

Michael:

Try changing the fieldname Session to something else - using that name
caused a problem for me a few months ago.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

 

[p c]

If you run the SQL an a query directly in the DB, doe sit give your an
error, does it give you the results you expect? Do you records that
match both of the Where criteria?

Copy this SQL to Access an test it out in Access.

SELECT Last_Name, First_Name
ROM Volunteers
WHERE
Meet = '12-13 November CGBD A/BB Meet'
AND Session = 'Saturday Morning / Prelims'
ORDER BY Last_Name

If it runs properly in the DB, then the problem is your code. Session is
not a reserved word for Access but is a function for ASP. For SQL, you
need to use square brackets for fields with spaces or fields that are
reserved words, e.g., [session] [field with spaces]

Are you hard coding the values for meet and session or are you using
variables. Post the asp code for how you create the SQL. That could be
your problem.

 

[Guest]

Kathleen and p c,
I got it to work by using custom query instead of record source. The code
was OK - not sure why the switch worked - maybe it was a problem with Session
as a field name (I forgot about that).
Thanks,
Michael

If you run the SQL an a query directly in the DB, doe sit give your an
error, does it give you the results you expect? Do you records that
match both of the Where criteria?

Copy this SQL to Access an test it out in Access.

SELECT Last_Name, First_Name
ROM Volunteers
WHERE
Meet = '12-13 November CGBD A/BB Meet'
AND Session = 'Saturday Morning / Prelims'
ORDER BY Last_Name

If it runs properly in the DB, then the problem is your code. Session is
not a reserved word for Access but is a function for ASP. For SQL, you
need to use square brackets for fields with spaces or fields that are
reserved words, e.g., [session] [field with spaces]

Are you hard coding the values for meet and session or are you using
variables. Post the asp code for how you create the SQL. That could be
your problem.

Kathleen,
Thanks for your reply, but that's not what I'm trying to do.
It's a sign-up procedure - user enters name and picks the meet and session
they want to volunteer for - it's not interactive. The DB table has 6 fields
populated by the form (text box for the names, user selections for the rest).
I want to display a list of the names in a table on another page that match
criteria in 2 of the fields. In other words, if field A = abc AND field B =
xyz, then display the Name.

This appears to be easily supported using the DB Results Wizard. However,
it looks like I can't get a match of the data from the query using text for
values. If I replace the values in the fields with numbers and change the
SQL statement accordingly, , it works fine. But, I need the text values for
the confirmation page.

Here's the SQL statement ... SELECT * FROM Volunteers WHERE (Meet = '12-13
November CGBD A/BB Meet' AND Session = 'Saturday Morning / Prelims') ORDER BY
ID1 ASC ... maybe that's the problem?

Thanks for your help,
Michael

: