Empty Text Boxes Change Query

[FurryGorilla]

I've been trying to implement a lookup page on an access database. There are 7 fields which allow the user to narrow down the field based on the amount of information they enter, however I do not want it so they have to fill in every field as they would more or less have to know the record they wanted.

The field names are clockno, shift, startdate & enddate which are combined to form the search, workcell, activity, and opno.

Is there a way of doing this without writing out every combination of query, using an excessive amount of if..else statements, and without splitting the page into separate pages?

An example of the SQL (wrong forum I know) is as follows (not all one line):
[tt]Query="SELECT * FROM oee WHERE clockno = &request("clock&quot&" AND shift = '"&request("shift&quot&"' AND date BETWEEN #"&startdate&"# AND #"&enddate&"# AND cell = "&request("workcell&quot&" AND actcode = "&request("act&quot&" AND opno = "&request("opno&quot[/tt]

Thanks in advance

Chris

 

[Geee]

Use the SQL Like command and enclose your search criterea in % signs like this...

Query="SELECT * FROM oee WHERE clockno LIKE %" & request("clock&quot&"% AND shift LIKE '%"&request("shift&quot&"%'" ... and so on.

Basically this means that your generated SQL statemant will read SELECT * FROM oee WHERE clockno LIKE %10332% AND SHIFT LIKE '%%'...
This means that if the user enters nothing in the shift but a clock number, you statement should still return the correct records as although SHIFT is in the WHERE clause you are basically saying Where SHIFT = anything.

G -GTM Solutions, Home of USITE-
-=

http://www.gtmsolutions.com=-

 

[FurryGorilla]

Thanks G but now when I run the query I get another problem.

I changed this by placing apostrophes around the % but it then said I have a data type mismatch in the expression. After that I tried a * and it seemed to work:

[tt]Query="SELECT * FROM oee WHERE "
Query=Query&"clockno LIKE '*"&request("clock&quot&"'"
Query=Query&" AND shift LIKE '*"&request("shift&quot&"'"
Query=Query&" AND cell LIKE '*"&request("workcell&quot&"'"
Query=Query&" AND actcode LIKE '*"&request("act&quot&"'"
Query=Query&" AND opno LIKE '*"&request("opno&quot&"'"[/tt]

Unfortunately this runs in MS Access when you copy the results of Response.Write Query in the published HTML page but it does not display any records on the HTML page, saying it has reached the EOF.

Very strange

Chris

 

[bassguy]

Hi, Part of the problem is the "feature" of access that they use the * as a wild card but the SQL standard is to use the % ...what this does is makes it so when you try to debug your ASP SQL statements with response.write they do not work in the Access sql Query area....and when you try to write a query in Access and then paste it into an ASP page you are speaking the wrong dielect again....

trust your code with the % in it and try the asp page it will probably work.

bassguy

 

[ToddWW]

As an alternative, here's an idea to build the query string one piece at a time. Only requires an IF..ENDIF for each form element instead of IF..ENDIF for every possible combination.

<%
Query=&quot;SELECT * FROM oee WHERE &quot;
IF Trim(request(&quot;clock&quot) <> &quot;&quot; THEN
Query = Query & &quot;clockno = &quot; & request(&quot;clock&quot & &quot; AND &quot;
END IF
IF Trim(request(&quot;shift&quot) <> &quot;&quot; THEN
Query = Query & &quot;shift = '&quot; & request(&quot;shift&quot & &quot;' AND &quot;
END IF

' AND SO ON FOR EACH FORM FIELD..
%>

When ASP is done building the query variable, you'll have the &quot; AND &quot; on the end of it. So put a line at the very end that removes it like this.

<%
Query = Left(Query,Len(Query)-5)
%>

Now, you'll have problems if the user does not enter anything in any of the fields so use JavaScript, if you're not already, to make sure at least one field has some data in it. You should be doing this anyways.

I like to put Response.Write Query during testing so I can see the SQL statement before I commit it.

TW