Results Page with Multiple Query Fields

[Guest]

I created an ASP.Net Results page that connects to an Access table. There
are four fields that we want to use in the query. We want staff to be able
to use one or all of those fields to narrow down the results. There are
three fields that use contains and one that uses equal. We have 'and'
between all four statements.

The problem is if we leave the Grade_Level blank and enter a Subject_Area,
we get 'no results'. In order for this to work, we must enter something into
the Grade_Level query field. If we change Grade_Level to contains it works
but we get grades 1, 11, and 12 if we enter 1.

Is there a way to modify the sql statement so that we can get the result we
need?

Here is my sql statement:
fp_sQry="SELECT * FROM CopyofResults WHERE (Grade_Level = '::Grade_Level::'
AND School_Name LIKE '%::School_Name::%' AND Subject_Areas LIKE
'%::Subject_Areas::%' AND Tier LIKE '%::Tier::%')"
fp_sDefault="Grade_Level=&School_Name=&Subject_Areas=&Tier="
fp_sNoRecords="<tr><td colspan=10 align=""LEFT"" width=""100%"">No records
returned.</td></tr>"

Thanks in advance for you help!

 

[Guest]

I just re-read my message and realized it is an ASP page...not ASP.net.

 

[Stefan B Rusynko]

The Grade_Level = '::Grade_Level::' is AND ing w/ all the other conditions so it will always fail if Grade_Level is not found
You need to add an OR to allow for no Grade level entered (Grade_Level is empty - 2 single quotes)

((Grade_Level = '::Grade_Level::' OR Grade_Level='' ) AND School_Name ....

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


|I just re-read my message and realized it is an ASP page...not ASP.net.
|
| "LauraK" wrote:
|
| > I created an ASP.Net Results page that connects to an Access table. There
| > are four fields that we want to use in the query. We want staff to be able
| > to use one or all of those fields to narrow down the results. There are
| > three fields that use contains and one that uses equal. We have 'and'
| > between all four statements.
| >
| > The problem is if we leave the Grade_Level blank and enter a Subject_Area,
| > we get 'no results'. In order for this to work, we must enter something into
| > the Grade_Level query field. If we change Grade_Level to contains it works
| > but we get grades 1, 11, and 12 if we enter 1.
| >
| > Is there a way to modify the sql statement so that we can get the result we
| > need?
| >
| > Here is my sql statement:
| > fp_sQry="SELECT * FROM CopyofResults WHERE (Grade_Level = '::Grade_Level::'
| > AND School_Name LIKE '%::School_Name::%' AND Subject_Areas LIKE
| > '%::Subject_Areas::%' AND Tier LIKE '%::Tier::%')"
| > fp_sDefault="Grade_Level=&School_Name=&Subject_Areas=&Tier="
| > fp_sNoRecords="<tr><td colspan=10 align=""LEFT"" width=""100%"">No records
| > returned.</td></tr>"
| >
| > Thanks in advance for you help!

 

[David Berry]

And to get an exact match when you enter 1 (and not 1, 11, 12 etc) you can't
use a LIKE statement since that will pull up everything with a 1 in it.