Advanced Database Search with FrontPage (Full Version)

All Forums >> [Web Development] >> ASP, PHP, and Database



Message

Avichai -> Advanced Database Search with FrontPage (12/7/2004 6:32:35)

I have a database which contains many categories... ex: firstame, lastname, address, age, sex, race, etc. (all text fields). I want to create a sorta "advanced search page," which allows users to input whatever data they know and then have it return all possible records... Ex: they input age (23), sex (m) and race (asian) (leaving other fields blank) and the results are all records that contain all 23 year old asian males. Or if they enter name (taylor) (leaving all other fields blank) it only returns all the records with taylor as the last name.

I think my problem has been that (using the OR) if I leave spaces blank then it uses this as a spec.... therefore it returns ALL records iin the database because all records may contain empty fields... And if using AND it views the blanks as requirements and thefore returns no records.... anyway.... does anyone get what I'm asking?

ANY suggestions would be appreciated - I'm a firstimer.
Thanks



rdouglass -> RE: Advanced Database Search with FrontPage (12/7/2004 11:33:31)

Hi and Welcome to Outfront.

If I hear your question correctly, you'll need to build the query 'on the fly' by checking each field for values. If they have a value, include the field in the request.

There have been several threads about this in the ASP forum. Here are two that should be relevant.

http://www.frontpagewebmaster.com/m-237358/mpage-1/key-/tm.htm#237371

http://www.frontpagewebmaster.com/m-119991/mpage-1/key-fly/tm.htm#120012

Hope they help. Re-post if they don't...



Avichai -> RE: Advanced Database Search with FrontPage (12/7/2004 22:16:32)

Okay... I haven't used the above ideas yet, but they sound like exactly what I need. My next question is... keeping in mind the above... within this same search page I have to catagories.... InDate and OutDate.... I would like to be able to search for all records that are between these dates and meet any other inputs by the user.... ie... user inputs sex (m), age (23) and 1/1/4 and 2/4/4... the results would then be all males age 23 that were here between 1/1/4 and 2/2/4. (my date fields are text fields)



rdouglass -> RE: Advanced Database Search with FrontPage (12/8/2004 10:23:15)

quote:

(my date fields are text fields)


Do they have to be text field? That query would be a lot easier if they were date fields...[:(]

If they must be text, you'll need to go thru a good excersize in disecting and re-building those fields in order to do a comparison. For instance, the problem would surface because using just text, the date 10/2/2004 would be before 2/10/2004 - OOOPS!

We can do it, but it might not be much fun...[;)]

Does that help move you in any direction?



Avichai -> RE: Advanced Database Search with FrontPage (12/8/2004 10:40:02)

Well, let me think on the date thing for a moment. The reason I choose "text" in the first place was because I when updating, my edit place has a field for date and time, however this is not always entered and even though I had the field set to zero allowed and the default set to zero, I could not update.....

But, I'll come back to that... In the mean time, I made tried the query and added "if thens" now I'm returned with an error that says

Microsoft VBScript runtime error '800a000d'
Type mismatch: 'value'

/TruckLog_interface/Log/editor/search_advanced.asp, line 555


I can't find where the mismatch is. I hate that I'm so new to this. Sooooo before I got to the date (which I think I could now succesfully do an update) let me try to get the basic search done. 


<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>ID</title>
<meta name="Microsoft Border" content="tl, default">
</head>

<body>

<p> </p>
<form BOTID="0" METHOD="POST" action="search_advanced.asp">
	<input type="hidden" name="fpdbr_0_PagingMove" value="  |<  ">
	<table BORDER="0">
		<tr>
			<td> </td>
			<td>
			 </td>
		</tr>
		<tr>
			<td><b>CarrierName</b></td>
			<td>
			<input TYPE="TEXT" NAME="CarrierName" VALUE="<%=Server.HtmlEncode(Request("CarrierName"))%>"></td>
		</tr>
		<tr>
			<td><b>TrailerNumber</b></td>
			<td>
			<input TYPE="TEXT" NAME="TrailerNumber" VALUE="<%=Server.HtmlEncode(Request("TrailerNumber"))%>"></td>
		</tr>
		<tr>
			<td> </td>
			<td>
			 </td>
		</tr>
		<tr>
			<td><b>Sealed</b></td>
			<td>
			<input TYPE="TEXT" NAME="Sealed" VALUE="<%=Server.HtmlEncode(Request("Sealed"))%>"></td>
		</tr>
		<tr>
			<td><b>SealMatchBol</b></td>
			<td>
			<input TYPE="TEXT" NAME="SealMatchBol" VALUE="<%=Server.HtmlEncode(Request("SealMatchBol"))%>"></td>
		</tr>
		<tr>
			<td><b>SealNumber</b></td>
			<td>
			<input TYPE="TEXT" NAME="SealNumber" VALUE="<%=Server.HtmlEncode(Request("SealNumber"))%>"></td>
		</tr>
		<tr>
			<td><b>PickupName</b></td>
			<td>
			<input TYPE="TEXT" NAME="PickupName" VALUE="<%=Server.HtmlEncode(Request("PickupName"))%>"></td>
		</tr>
		<tr>
			<td><b>ReturnAuthorizationNumber</b></td>
			<td>
			<input TYPE="TEXT" NAME="ReturnAuthorizationNumber" VALUE="<%=Server.HtmlEncode(Request("ReturnAuthorizationNumber"))%>"></td>
		</tr>
		<tr>
			<td><b>IncomingBOLNumber</b></td>
			<td>
			<input TYPE="TEXT" NAME="IncomingBOLNumber" VALUE="<%=Server.HtmlEncode(Request("IncomingBOLNumber"))%>"></td>
		</tr>
		<tr>
			<td><b>ReleaseNumber</b></td>
			<td>
			<input TYPE="TEXT" NAME="ReleaseNumber" VALUE="<%=Server.HtmlEncode(Request("ReleaseNumber"))%>"></td>
		</tr>
		<tr>
			<td><b>InDate</b></td>
			<td>
			<input TYPE="TEXT" NAME="InDate" VALUE="<%=Server.HtmlEncode(Request("InDate"))%>"></td>
		</tr>
		<tr>
			<td><b>Chilled or Dry?</b></td>
			<td>
			<input TYPE="text" NAME="FGProduct" VALUE="<%=Server.HtmlEncode(Request("FGProduct"))%>"></td>
		</tr>
		<tr>
			<td><b>LiveDrop</b></td>
			<td>
			<input TYPE="TEXT" NAME="LiveDrop" VALUE="<%=Server.HtmlEncode(Request("LiveDrop"))%>"></td>
		</tr>
		<tr>
			<td><b>AvailableToLoad</b></td>
			<td>
			<input TYPE="TEXT" NAME="AvailableToLoad" VALUE="<%=Server.HtmlEncode(Request("AvailableToLoad"))%>"></td>
		</tr>
		<tr>
			<td><b>LoadedWith</b></td>
			<td>
			<input TYPE="TEXT" NAME="LoadedWith" VALUE="<%=Server.HtmlEncode(Request("LoadedWith"))%>"></td>
		</tr>
		<tr>
			<td><b>CannotBeLoaded</b></td>
			<td>
			<input TYPE="TEXT" NAME="CannotBeLoaded" VALUE="<%=Server.HtmlEncode(Request("CannotBeLoaded"))%>"></td>
		</tr>
		<tr>
			<td><b>NoLongerInYard</b></td>
			<td>
			<input TYPE="TEXT" NAME="NoLongerInYard" VALUE="<%=Server.HtmlEncode(Request("NoLongerInYard"))%>"></td>
		</tr>
		<tr>
			<td> </td>
			<td>
			 </td>
		</tr>
		<tr>
			<td><b>Material</b></td>
			<td>
			<input TYPE="TEXT" NAME="Material" VALUE="<%=Server.HtmlEncode(Request("Material"))%>"></td>
		</tr>
		<tr>
			<td> </td>
			<td>
			 </td>
		</tr>
		<tr>
			<td><b>Item1</b></td>
			<td>
			<input TYPE="TEXT" NAME="Item" VALUE="<%=Server.HtmlEncode(Request("Item"))%>"></td>
		</tr>
		<tr>
			<td><b>Description1</b></td>
			<td>
			<input TYPE="TEXT" NAME="Description" VALUE="<%=Server.HtmlEncode(Request("Description"))%>"></td>
		</tr>
		<tr>
			<td> </td>
			<td>
			 </td>
		</tr>
		<tr>
			<td><b>LotNumber1</b></td>
			<td>
			<input TYPE="TEXT" NAME="LotNumber" VALUE="<%=Server.HtmlEncode(Request("LotNumber"))%>"></td>
		</tr>
		<tr>
			<td> </td>
			<td>
			 </td>
		</tr>
		<tr>
			<td> </td>
			<td>
			 </td>
		</tr>
		<tr>
			<td><b>TankerTCode</b></td>
			<td>
			<input TYPE="TEXT" NAME="TankerTCode" VALUE="<%=Server.HtmlEncode(Request("TankerTCode"))%>"></td>
		</tr>
		<tr>
			<td> </td>
			<td>
			 </td>
		</tr>
		<tr>
			<td> </td>
			<td>
			 </td>
		</tr>
		<tr>
			<td><b>DSealNumber</b></td>
			<td>
			<input TYPE="TEXT" NAME="DSealNumber" VALUE="<%=Server.HtmlEncode(Request("DSealNumber"))%>"></td>
		</tr>
		<tr>
			<td> </td>
			<td>
			 </td>
		</tr>
		<tr>
			<td><b>DDate</b></td>
			<td>
			<input TYPE="TEXT" NAME="DDate" VALUE="<%=Server.HtmlEncode(Request("DDate"))%>"></td>
		</tr>
		<tr>
			<td><b>DBOL</b></td>
			<td>
			<input TYPE="TEXT" NAME="DBOL" VALUE="<%=Server.HtmlEncode(Request("DBOL"))%>"></td>
		</tr>
	</table>
	<br>
	<input TYPE="Submit"><input TYPE="Reset"><!--webbot bot="SaveAsASP" clientside suggestedext="asp" preview=" " startspan --><!--webbot bot="SaveAsASP" endspan --><p> </p>
</form>
<table width="100%" border="1">
	<thead>
		<tr>
			<th ALIGN="LEFT"><b>CarrierName</b></th>
			<th ALIGN="LEFT"><b>TrailerNumber</b></th>
			<th ALIGN="LEFT"><b>TractorNumber</b></th>
			<th ALIGN="LEFT"><b>Sealed</b></th>
			<th ALIGN="LEFT"><b>SealMatchBol</b></th>
			<th ALIGN="LEFT"><b>SealNumber</b></th>
			<th ALIGN="LEFT"><b>PickupName</b></th>
			<th ALIGN="LEFT"><b>ReturnAuthorizationNumber</b></th>
			<th ALIGN="LEFT"><b>IncomingBOLNumber</b></th>
			<th ALIGN="LEFT"><b>ReleaseNumber</b></th>
			<th ALIGN="LEFT"><b>InDate</b></th>
			<th ALIGN="LEFT"><b>InTime</b></th>
			<th ALIGN="LEFT"><b>EnteredBy</b></th>
			<th ALIGN="LEFT"><b>LiveDrop</b></th>
			<th ALIGN="LEFT"><b>AvailableToLoad</b></th>
			<th ALIGN="LEFT"><b>LoadedWith</b></th>
			<th ALIGN="LEFT"><b>CannotBeLoaded</b></th>
			<th ALIGN="LEFT"><b>NoLongerInYard</b></th>
			<th ALIGN="LEFT"><b>Contents</b></th>
			<th ALIGN="LEFT"><b>Comments1</b></th>
			<th ALIGN="LEFT"><b>UDate</b></th>
			<th ALIGN="LEFT"><b>UTime</b></th>
			<th ALIGN="LEFT"><b>Material</b></th>
			<th ALIGN="LEFT"><b>FGProduct</b></th>
			<th ALIGN="LEFT"><b>Pallet</b></th>
			<th ALIGN="LEFT"><b>Item1</b></th>
			<th ALIGN="LEFT"><b>Item2</b></th>
			<th ALIGN="LEFT"><b>Item3</b></th>
			<th ALIGN="LEFT"><b>Item4</b></th>
			<th ALIGN="LEFT"><b>Item5</b></th>
			<th ALIGN="LEFT"><b>Item6</b></th>
			<th ALIGN="LEFT"><b>Item7</b></th>
			<th ALIGN="LEFT"><b>Item8</b></th>
			<th ALIGN="LEFT"><b>Item9</b></th>
			<th ALIGN="LEFT"><b>Item10</b></th>
			<th ALIGN="LEFT"><b>Item11</b></th>
			<th ALIGN="LEFT"><b>Item12</b></th>
			<th ALIGN="LEFT"><b>Description1</b></th>
			<th ALIGN="LEFT"><b>Description2</b></th>
			<th ALIGN="LEFT"><b>Description3</b></th>
			<th ALIGN="LEFT"><b>Description4</b></th>
			<th ALIGN="LEFT"><b>Description5</b></th>
			<th ALIGN="LEFT"><b>Description6</b></th>
			<th ALIGN="LEFT"><b>Description7</b></th>
			<th ALIGN="LEFT"><b>Description8</b></th>
			<th ALIGN="LEFT"><b>Description9</b></th>
			<th ALIGN="LEFT"><b>Description10</b></th>
			<th ALIGN="LEFT"><b>Description11</b></th>
			<th ALIGN="LEFT"><b>Description12</b></th>
			<th ALIGN="LEFT"><b>Quantity1</b></th>
			<th ALIGN="LEFT"><b>Quantity2</b></th>
			<th ALIGN="LEFT"><b>Quantity3</b></th>
			<th ALIGN="LEFT"><b>Quantity4</b></th>
			<th ALIGN="LEFT"><b>Quantity5</b></th>
			<th ALIGN="LEFT"><b>Quantity6</b></th>
			<th ALIGN="LEFT"><b>Quantity7</b></th>
			<th ALIGN="LEFT"><b>Quantity8</b></th>
			<th ALIGN="LEFT"><b>Quantity9</b></th>
			<th ALIGN="LEFT"><b>Quantity10</b></th>
			<th ALIGN="LEFT"><b>Quantity11</b></th>
			<th ALIGN="LEFT"><b>Quantity12</b></th>
			<th ALIGN="LEFT"><b>LotNumber1</b></th>
			<th ALIGN="LEFT"><b>LotNumber2</b></th>
			<th ALIGN="LEFT"><b>LotNumber3</b></th>
			<th ALIGN="LEFT"><b>LotNumber4</b></th>
			<th ALIGN="LEFT"><b>LotNumber5</b></th>
			<th ALIGN="LEFT"><b>LotNumber6</b></th>
			<th ALIGN="LEFT"><b>LotNumber7</b></th>
			<th ALIGN="LEFT"><b>LotNumber8</b></th>
			<th ALIGN="LEFT"><b>LotNumber9</b></th>
			<th ALIGN="LEFT"><b>LotNumber10</b></th>
			<th ALIGN="LEFT"><b>LotNumber11</b></th>
			<th ALIGN="LEFT"><b>LotNumber12</b></th>
			<th ALIGN="LEFT"><b>UnitSize1</b></th>
			<th ALIGN="LEFT"><b>UnitSize2</b></th>
			<th ALIGN="LEFT"><b>UnitSize3</b></th>
			<th ALIGN="LEFT"><b>UnitSize4</b></th>
			<th ALIGN="LEFT"><b>UnitSize5</b></th>
			<th ALIGN="LEFT"><b>UnitSize6</b></th>
			<th ALIGN="LEFT"><b>UnitSize7</b></th>
			<th ALIGN="LEFT"><b>UnitSize8</b></th>
			<th ALIGN="LEFT"><b>UnitSize9</b></th>
			<th ALIGN="LEFT"><b>UnitSize10</b></th>
			<th ALIGN="LEFT"><b>UnitSize11</b></th>
			<th ALIGN="LEFT"><b>UnitSize12</b></th>
			<th ALIGN="LEFT"><b>Vendor</b></th>
			<th ALIGN="LEFT"><b>PalletCount</b></th>
			<th ALIGN="LEFT"><b>RcvdProductQuality</b></th>
			<th ALIGN="LEFT"><b>RcvdTrailerQuality</b></th>
			<th ALIGN="LEFT"><b>RcvdPalletQuality</b></th>
			<th ALIGN="LEFT"><b>Comments2</b></th>
			<th ALIGN="LEFT"><b>UEnteredBy</b></th>
			<th ALIGN="LEFT"><b>TankerTCode</b></th>
			<th ALIGN="LEFT"><b>PumpTimeStart</b></th>
			<th ALIGN="LEFT"><b>PumpTimeFinish</b></th>
			<th ALIGN="LEFT"><b>DSealNumber</b></th>
			<th ALIGN="LEFT"><b>DBOL</b></th>
			<th ALIGN="LEFT"><b>DPalletCount</b></th>
			<th ALIGN="LEFT"><b>DDate</b></th>
			<th ALIGN="LEFT"><b>DTime</b></th>
			<th ALIGN="LEFT"><b>DEnteredBy</b></th>
			<th ALIGN="LEFT"><b>DContents</b></th>
			<th ALIGN="LEFT"><b>Comments3</b></th>
		</tr>
	</thead>
	<tbody>
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<!--#include file="../../_fpclass/FPDBLIB.INC"-->

<%
'Function to build query for search
Dim mySQL
mySQL = " "
If Request("CarrierName") = " " then
mySQL = " "
else
mySQL = "(CarrierName LIKE '%::CarrierName::%')"
end if

If Request("TrailerNumber") > " " then
If mySQL = " " then
mySQL = "(TrailerNumber LIKE '%::TrailerNumber::%')"
else
mySQL = mySQL & " AND (TrailerNumber LIKE '%::TrailerNumber::%')"
end if
end if

If Request("Sealed") > " " then
If mySQL > " " then
mySQL = mySQL & " AND(Sealed LIKE '%::Sealed::%')"
else
mySQL = "(Sealed LIKE '%::Sealed::%')"
end if
end if

If Request("SealMatchBol") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (SealMatchBol LIKE '%::SealMatchBol::%')"
else
mySQL = "(SealMatchBol LIKE '%::SealMatchBol::%')"
end if
end if

If Request("ReturnAuthorizationNumber") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (ReturnAuthorizationNumber LIKE '%::ReturnAuthorizationNumber::%')"
else
mySQL = "(ReturnAuthorizationNumber LIKE '%::ReturnAuthorizationNumber::%')"
end if
end if

If Request("IncomingBOLNumber") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (IncomingBOLNumber LIKE '%::IncomingBOLNumber::%')"
else
mySQL = "(IncomingBOLNumber LIKE '%::IncomingBOLNumber::%')"
end if
end if

If Request("ReleaseNumber") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (ReleaseNumber LIKE '%::ReleaseNumber::%')"
else
mySQL = "(ReleaseNumber LIKE '%::ReleaseNumber::%')"
end if
end if

If Request("InDate") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (InDate LIKE '%::InDate::%')"
else
mySQL = "(InDate LIKE '%::InDate::%')"
end if
end if

If Request("FGProduct") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (FGProduct LIKE '%::FGProduct::%')"
else
mySQL = "(FGProduct LIKE '%::FGProduct::%')"
end if
end if

If Request("LiveDrop") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (LiveDrop LIKE '%::LiveDrop::%')"
else
mySQL = "(LiveDrop LIKE '%::LiveDrop::%')"
end if
end if

If Request("AvailableToLoad") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (AvailableToLoad LIKE '%::AvailableToLoad::%')"
else
mySQL = "(AvailableToLoad LIKE '%::AvailableToLoad::%')"
end if
end if

If Request("LoadedWith") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (LoadedWith LIKE '%::LoadedWith::%')"
else
mySQL = "(LoadedWith LIKE '%::LoadedWith::%')"
end if
end if

If Request("CannotBeLoaded") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (CannotBeLoaded LIKE '%::CannotBeLoaded::%')"
else
mySQL = "(CannotBeLoaded LIKE '%::CannotBeLoaded::%')"
end if
end if

If Request("NoLongerInYard") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (NoLongerInYard LIKE '%::NoLongerInYard::%')"
else
mySQL = "(NoLongerInYard LIKE '%::NoLongerInYard::%')"
end if
end if

If Request("Material") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (Material LIKE '%::Material::%')"
else
mySQL = "(Material LIKE '%::Material::%')"
end if
end if

If Request("Item") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (Item1 LIKE '%::Item::%' OR Item2 LIKE '%::Item::%' OR Item3 LIKE '%::Item::%' OR Item4 LIKE '%::Item::%' OR Item5 LIKE '%::Item::%' OR Item6 LIKE '%::Item::%' OR Item7 LIKE '%::Item::%' OR Item8 LIKE '%::Item::%' OR Item9 LIKE '%::Item::%' OR Item10 LIKE '%::Item::%' OR Item11 LIKE '%::Item::%' OR Item12 LIKE '%::Item::%')"
else
mySQL = "(Item1 LIKE '%::Item::%' OR Item2 LIKE '%::Item::%' OR Item3 LIKE '%::Item::%' OR Item4 LIKE '%::Item::%' OR Item5 LIKE '%::Item::%' OR Item6 LIKE '%::Item::%' OR Item7 LIKE '%::Item::%' OR Item8 LIKE '%::Item::%' OR Item9 LIKE '%::Item::%' OR Item10 LIKE '%::Item::%' OR Item11 LIKE '%::Item::%' OR Item12 LIKE '%::Item::%')"
end if
end if

If Request("Description") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (Description1 LIKE '%::Description::%' OR Description2 LIKE '%::Description::%' OR Description3 LIKE '%::Description::%' OR Description4 LIKE '%::Description::%' OR Description5 LIKE '%::Description::%' OR Description6 LIKE '%::Description::%' OR Description7 LIKE '%::Description::%' OR Description8 LIKE '%::Description::%' OR Description9 LIKE '%::Description::%' OR Description10 LIKE '%::Description::%' OR Description11 LIKE '%::Description::%' OR Description12 LIKE '%::Description::%')"
else
mySQL = "(Description1 LIKE '%::Description::%' OR Description2 LIKE '%::Description::%' OR Description3 LIKE '%::Description::%' OR Description4 LIKE '%::Description::%' OR Description5 LIKE '%::Description::%' OR Description6 LIKE '%::Description::%' OR Description7 LIKE '%::Description::%' OR Description8 LIKE '%::Description::%' OR Description9 LIKE '%::Description::%' OR Description10 LIKE '%::Description::%' OR Description11 LIKE '%::Description::%' OR Description12 LIKE '%::Description::%')"
end if
end if

If Request("LotNumber") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (LotNumber1 = '::LotNumber::' OR LotNumber2 = '::LotNumber::' OR LotNumber3 = '::LotNumber::' OR LotNumber4 = '::LotNumber::' OR LotNumber5 = '::LotNumber::' OR LotNumber6 = '::LotNumber::' OR LotNumber7 = '::LotNumber::' OR LotNumber8 = '::LotNumber::' OR LotNumber9 = '::LotNumber::' OR LotNumber10 = '::LotNumber::' OR LotNumber11 = '::LotNumber::' OR LotNumber12 = '::LotNumber::')"
else
mySQL = "(LotNumber1 = '::LotNumber::' OR LotNumber2 = '::LotNumber::' OR LotNumber3 = '::LotNumber::' OR LotNumber4 = '::LotNumber::' OR LotNumber5 = '::LotNumber::' OR LotNumber6 = '::LotNumber::' OR LotNumber7 = '::LotNumber::' OR LotNumber8 = '::LotNumber::' OR LotNumber9 = '::LotNumber::' OR LotNumber10 = '::LotNumber::' OR LotNumber11 = '::LotNumber::' OR LotNumber12 = '::LotNumber::')"
end if
end if

If Request("TankerTCode") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (TankerTCode LIKE '%::TankerTCode::%')"
else
mySQL = "(TankerTCode LIKE '%::TankerTCode::%')"
end if
end if

If Request("DSealNumber") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (DSealNumber LIKE '%::DSealNumber::%')"
else
mySQL = "(DSealNumber LIKE '%::DSealNumber::%')"
end if
end if

If Request("DDate") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (DDate LIKE '%::DDate::%')"
else
mySQL = "(DDate LIKE '%::DDate::%')"
end if
end if

If Request("DBOL") > " " then
If mySQL > " " then
mySQL = mySQL & " AND (DBOL LIKE '%::DBOL::%')"
else
mySQL = "(DBOL LIKE '%::DBOL::%')"
end if
end if

'If query string is still blank, set a No Record Found
value
If mySQL = " " then
mySQL = "CarrierName LIKE 'xxxx')"
end if

%>


<%
fp_sQry="SELECT * FROM Log WHERE ("&mySQL&") ORDER BY CarrierName ASC"
fp_sDefault=NULL
fp_sNoRecords="<tr><td colspan=103 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="TruckLog"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ID=3&CarrierName=202&TrailerNumber=202&TractorNumber=202&Sealed=202&SealMatchBol=202&SealNumber=202&PickupName=202&ReturnAuthorizationNumber=202&IncomingBOLNumber=202&ReleaseNumber=202&InDate=202&InTime=202&EnteredBy=202&LiveDrop=202&AvailableToLoad=202&LoadedWith=202&CannotBeLoaded=202&NoLongerInYard=202&Contents=202&Comments1=203&UDate=202&UTime=202&Material=202&FGProduct=202&Pallet=202&Item1=202&Item2=202&Item3=202&Item4=202&Item5=202&Item6=202&Item7=202&Item8=202&Item9=202&Item10=202&Item11=202&Item12=202&Description1=202&Description2=202&Description3=202&Description4=202&Description5=202&Description6=202&Description7=202&Description8=202&Description9=202&Description10=202&Description11=202&Description12=202&Quantity1=202&Quantity2=202&Quantity3=202&Quantity4=202&Quantity5=202&Quantity6=202&Quantity7=202&Quantity8=202&Quantity9=202&Quantity10=202&Quantity11=202&Quantity12=202&LotNumber1=202&LotNumber2=202&LotNumber3=202&LotNumber4=202&LotNumber5=202&LotNumber6=202&LotNumber7=202&LotNumber8=202&LotNumber9=202&LotNumber10=202&LotNumber11=202&LotNumber12=202&UnitSize1=202&UnitSize2=202&UnitSize3=202&UnitSize4=202&UnitSize5=202&UnitSize6=202&UnitSize7=202&UnitSize8=202&UnitSize9=202&UnitSize10=202&UnitSize11=202&UnitSize12=202&Vendor=202&PalletCount=202&RcvdProductQuality=202&RcvdTrailerQuality=202&RcvdPalletQuality=202&Comments2=203&UEnteredBy=202&TankerTCode=202&PumpTimeStart=202&PumpTimeFinish=202&DSealNumber=202&DBOL=202&DPalletCount=202&DDate=202&DTime=202&DEnteredBy=202&DContents=202&Comments3=203&"
fp_iDisplayCols=103
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../../../_fpclass/fpdbrgn1.inc"-->
			<%=FP_FieldVal(fp_rs,"TractorNumber")%></td>
			</tr>
			<%=FP_FieldVal(fp_rs,"Sealed")%><%=FP_FieldVal(fp_rs,"SealMatchBol")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"SealNumber")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"PickupName")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"ReturnAuthorizationNumber")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"IncomingBOLNumber")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"ReleaseNumber")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"InDate")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"InTime")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"EnteredBy")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"LiveDrop")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"AvailableToLoad")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"LoadedWith")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"CannotBeLoaded")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"NoLongerInYard")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"Contents")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"Comments1")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"UDate")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"UTime")%><td>
			 </td>
			<%=FP_FieldVal(fp_rs,"Material")%>
			<%=FP_FieldVal(fp_rs,"FGProduct")%>
			<%=FP_FieldVal(fp_rs,"Pallet")%>
			<%=FP_FieldVal(fp_rs,"Item1")%>
			<%=FP_FieldVal(fp_rs,"Item2")%>
			<%=FP_FieldVal(fp_rs,"Item3")%>
			<%=FP_FieldVal(fp_rs,"Item4")%>
			<%=FP_FieldVal(fp_rs,"Item5")%>
			<%=FP_FieldVal(fp_rs,"Item6")%>
			<%=FP_FieldVal(fp_rs,"Item7")%>
			<%=FP_FieldVal(fp_rs,"Item8")%>
			<%=FP_FieldVal(fp_rs,"Item9")%>
			<%=FP_FieldVal(fp_rs,"Item10")%>
			<%=FP_FieldVal(fp_rs,"Item11")%>
			<%=FP_FieldVal(fp_rs,"Item12")%>
			<%=FP_FieldVal(fp_rs,"Description1")%>
			<%=FP_FieldVal(fp_rs,"Description2")%>
			<%=FP_FieldVal(fp_rs,"Description3")%>
			<%=FP_FieldVal(fp_rs,"Description4")%>
			<%=FP_FieldVal(fp_rs,"Description5")%>
			<%=FP_FieldVal(fp_rs,"Description6")%>
			<%=FP_FieldVal(fp_rs,"Description7")%>
			<%=FP_FieldVal(fp_rs,"Description8")%>
			<%=FP_FieldVal(fp_rs,"Description9")%>
			<%=FP_FieldVal(fp_rs,"Description10")%>
			<%=FP_FieldVal(fp_rs,"Description11")%>
			<%=FP_FieldVal(fp_rs,"Description12")%>
			<%=FP_FieldVal(fp_rs,"Quantity1")%>
			<%=FP_FieldVal(fp_rs,"Quantity2")%>
			<%=FP_FieldVal(fp_rs,"Quantity3")%>
			<%=FP_FieldVal(fp_rs,"Quantity4")%>
			<%=FP_FieldVal(fp_rs,"Quantity5")%>
			<%=FP_FieldVal(fp_rs,"Quantity6")%>
			<%=FP_FieldVal(fp_rs,"Quantity7")%>
			<%=FP_FieldVal(fp_rs,"Quantity8")%>
			<%=FP_FieldVal(fp_rs,"Quantity9")%>
			<%=FP_FieldVal(fp_rs,"Quantity10")%>
			<%=FP_FieldVal(fp_rs,"Quantity11")%>
			<%=FP_FieldVal(fp_rs,"Quantity12")%>
			<%=FP_FieldVal(fp_rs,"LotNumber1")%>
			<%=FP_FieldVal(fp_rs,"LotNumber2")%>
			<%=FP_FieldVal(fp_rs,"LotNumber3")%>
			<%=FP_FieldVal(fp_rs,"LotNumber4")%>
			<%=FP_FieldVal(fp_rs,"LotNumber5")%>
			<%=FP_FieldVal(fp_rs,"LotNumber6")%>
			<%=FP_FieldVal(fp_rs,"LotNumber7")%>
			<%=FP_FieldVal(fp_rs,"LotNumber8")%>
			<%=FP_FieldVal(fp_rs,"LotNumber9")%>
			<%=FP_FieldVal(fp_rs,"LotNumber10")%>
			<%=FP_FieldVal(fp_rs,"LotNumber11")%>
			<%=FP_FieldVal(fp_rs,"LotNumber12")%>
			<%=FP_FieldVal(fp_rs,"UnitSize1")%>
			<%=FP_FieldVal(fp_rs,"UnitSize2")%>
			<%=FP_FieldVal(fp_rs,"UnitSize3")%>
			<%=FP_FieldVal(fp_rs,"UnitSize4")%>
			<%=FP_FieldVal(fp_rs,"UnitSize5")%>
			<%=FP_FieldVal(fp_rs,"UnitSize6")%>
			<%=FP_FieldVal(fp_rs,"UnitSize7")%>
			<%=FP_FieldVal(fp_rs,"UnitSize8")%>
			<%=FP_FieldVal(fp_rs,"UnitSize9")%>
			<%=FP_FieldVal(fp_rs,"UnitSize10")%>
			<%=FP_FieldVal(fp_rs,"UnitSize11")%>
			<%=FP_FieldVal(fp_rs,"UnitSize12")%>
			<%=FP_FieldVal(fp_rs,"Vendor")%>
			<%=FP_FieldVal(fp_rs,"PalletCount")%>
			<%=FP_FieldVal(fp_rs,"RcvdProductQuality")%>
			<%=FP_FieldVal(fp_rs,"RcvdTrailerQuality")%>
			<%=FP_FieldVal(fp_rs,"RcvdPalletQuality")%>
			<%=FP_FieldVal(fp_rs,"Comments2")%>
			<%=FP_FieldVal(fp_rs,"UEnteredBy")%>
			<%=FP_FieldVal(fp_rs,"TankerTCode")%>
			<%=FP_FieldVal(fp_rs,"PumpTimeStart")%>
			<%=FP_FieldVal(fp_rs,"PumpTimeFinish")%>
			<%=FP_FieldVal(fp_rs,"DSealNumber")%>
			<%=FP_FieldVal(fp_rs,"DBOL")%>
			<%=FP_FieldVal(fp_rs,"DPalletCount")%>
			<%=FP_FieldVal(fp_rs,"DDate")%>
			<%=FP_FieldVal(fp_rs,"DTime")%>
			<%=FP_FieldVal(fp_rs,"DEnteredBy")%>
			<%=FP_FieldVal(fp_rs,"DContents")%>
			<%=FP_FieldVal(fp_rs,"Comments3")%>
</td>
</tr>
		<!--#include file="../../../_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>

</body>

</html>


I hope I used the code thing right... if not, sorry for the large use.

Thanks



rdouglass -> RE: Advanced Database Search with FrontPage (12/8/2004 11:04:13)

quote:

'If query string is still blank, set a No Record Found
value
If mySQL = " " then
mySQL = "CarrierName LIKE 'xxxx')"
end if


Should be:

quote:

'If query string is still blank, set a No Record Found value
If mySQL = " " then
mySQL = "CarrierName LIKE 'xxxx')"
end if


see how the word 'value' flowed to the next line from your comment? It should be on the same line as part of the comment.

Also, you probably should make sure your testing for "" and not " " (notice the space?). And finally to tighten the code a little and group your OR's properly, the sections you use with long OR phrases, maybe you can do something like this: 

If Request("Item") > "" then
If mySQL > "" then
mySQL = mySQL & " AND"
end if
mySQL = " ((Item1 LIKE '%::Item::%') OR (Item2 LIKE '%::Item::%') OR (Item3 LIKE '%::Item::%') OR (Item4 LIKE '%::Item::%') OR (Item5 LIKE '%::Item::%') OR (Item6 LIKE '%::Item::%') OR (Item7 LIKE '%::Item::%') OR (Item8 LIKE '%::Item::%') OR (Item9 LIKE '%::Item::%') OR (Item10 LIKE '%::Item::%') OR (Item11 LIKE '%::Item::%') OR (Item12 LIKE '%::Item::%'))"
end if


See, I changed it slightly to only add the AND if necessary and not build the whole thing for each condition (reduces possible typos). All that said, I think your first issue is the first fix I posted about your comment line flowing over.

That any help?



Avichai -> RE: Advanced Database Search with FrontPage (12/8/2004 11:41:55)

I'm close! The query returns many results even when there has been no criteria entered. What's up with that? Also, because I put my code on a diet, I no longer know how to format the output or add a hyperlink..... I can probably figure this out...

So two questions: One - why am I getting results (not queried). and Two - do i have to set any defaults

Thanky you



Avichai -> RE: Advanced Database Search with FrontPage (12/8/2004 11:49:20)

Hey!!! I got it... I'm going to work on formatting.

Another question... can this web bot be removed?

<!--webbot bot="SaveAsASP" clientside suggestedext="asp" preview="" startspan -->[SaveAsASP]<!--webbot bot="SaveAsASP" endspan i-checksum="21580" -->

Thank you



Spooky -> RE: Advanced Database Search with FrontPage (12/8/2004 12:44:00)

Yes

http://www.outfront.net/spooky/adv_drw_diet.htm



Avichai -> RE: Advanced Database Search with FrontPage (12/8/2004 15:24:02)

Okay .. . I used the diet plan (macro) and it works wonderfully!!! Should have used it in the first place...

Second, I finished the search page and it works wonderfully!!!!!!!

Now, I'd like to customize it and work some more... As I mentioned earlier, (keeping in mind the layout of the search) I'd like to be able to search between to dates... My in and out dates are in two different columns... so for example I'd search for all males which came in between x and x. However, it should not be mandatory that users know any information, just what they can recall.

Also, I'd like to use drop-down boxes for some fields in the search instead of text. I think Ihave a good idea about this one and have not had the time to try it yet, but the date thingy is the biggest question.

Thanks to everyone!



Avichai -> RE: Advanced Database Search with FrontPage (12/8/2004 15:44:58)

Sorry, I forgot.... I'm using MS Access and I've changed my date fields from text to Date/Time (which was the only choice I had). Everything else seems to still work on my site, so I guess the change is okay.



Avichai -> RE: Advanced Database Search with FrontPage (12/9/2004 15:58:10)

Okay.... So the search is going wonderful... now I'm trying my hand at the date stuff... here is some (very bad) code that I've come up with... Anyone know how to properly do this? 

<%
'Function to build query for search
Dim mySQL
mySQL = ""
If Request("CarrierName") = "" then
mySQL = ""
else
mySQL = "(CarrierName LIKE '%::CarrierName::%')"
end if

If Request("TrailerNumber") > "" then
If mySQL = "" then
mySQL = "(TrailerNumber LIKE '%::TrailerNumber::%')"
else
mySQL = mySQL & " AND (TrailerNumber LIKE '%::TrailerNumber::%')"
end if
end if

If Request("Sealed") > "" then
If mySQL > "" then
mySQL = mySQL & " AND (Sealed LIKE '%::Sealed::%')"
else
mySQL = "(Sealed LIKE '%::Sealed::%')"
end if
end if

If Request("InDate") > "" then
	If Request("DDate") > "" then
		If mySQL > "" then
			mySQL = mySQL & " BETWEEN('#InDate#' and '#DDate#')"
		else
			mySQL = mySQL & " AND (DDate = '#DDate#')"
		else
			mySQL = mySQL & " AND (InDate = '#InDate#')"
		else
			mySQL = BETWEEN ('#InDate#' and '#DDate#') 
		End if
	End if
End if




rdouglass -> RE: Advanced Database Search with FrontPage (12/10/2004 9:48:27)

quote:

If Request("InDate") > "" then
If Request("DDate") > "" then
If mySQL > "" then
mySQL = mySQL & " BETWEEN('#InDate#' and '#DDate#')"
else
mySQL = mySQL & " AND (DDate = '#DDate#')"
else
mySQL = mySQL & " AND (InDate = '#InDate#')"
else
mySQL = BETWEEN ('#InDate#' and '#DDate#')
End if
End if
End if


Try this:

If Request("InDate") > "" then
If Request("DDate") > "" then
If mySQL > "" then
mySQL = mySQL & "AND BETWEEN (#::InDate::# and #::DDate::#)"
else
mySQL = mySQL & " AND (DDate = #::DDate::#)"
else
mySQL = mySQL & " AND (InDate = #::InDate::#)"
else
mySQL = " BETWEEN (#::InDate::# and #::DDate::#)
End if
End if
End if

At least that's the date/time syntax for Access. Although your logic may be wrong. What happens if there is a DDate but not an InDate (or do you care)? What is supposed to be between those two dates?

Hope it helps.



Avichai -> RE: Advanced Database Search with FrontPage (12/10/2004 10:06:29)

Actually, it's my logic that I wanted to check.

The InDate with No OutDate is a problem, but I could set both in and out boxes as required and then provide to large dates.


This database is for people coming in each day, so I might want to look back over a month's range to see if a certain person(trailer) was here and hyperling to another page to see all the details.




rdouglass -> RE: Advanced Database Search with FrontPage (12/10/2004 10:15:19)

What I would do if it were me, is I'd force something to always be in DDate and InDate:

If trim(Request("InDate")&"") > "" then
myInDate = Request("InDate")
ELSE
myInDate = "01/01/1900"
END IF
If trim(Request("DDate")&"") > "" then
myDDate = Request("DDate")
ELSE
myDDate = "12/31/2999"
END IF

If mySQL > "" then
mySQL = mySQL & " AND"
end if

mySQL = mySQL &"(InDate BETWEEN (#" & myInDate & "# and #" & myDDate & "#) AND DDate BETWEEN (#" & myInDate & "# and #" & myDDate & "#))"

Haven't checked this for errors but I think you'll see what I'm doing. If no InDate entered, use a very 'low' date; if not DDate, use a very 'high' date.

At least that's one way to approach it. Hope it helps.



Avichai -> RE: Advanced Database Search with FrontPage (12/10/2004 10:36:48)

Okay assuming the Date fields are Required.... this is what I have 

<%
'Function to build query for search
Dim mySQL
mySQL = ""
If Request("CarrierName") = "" then
mySQL = ""
else
mySQL = "(CarrierName LIKE '%::CarrierName::%')"
end if

If Request("TrailerNumber") > "" then
If mySQL = "" then
mySQL = "(TrailerNumber LIKE '%::TrailerNumber::%')"
else
mySQL = mySQL & " AND (TrailerNumber LIKE '%::TrailerNumber::%')"
end if
end if


'!!!!!!!!!!!!!!!!!!Drop Down Box with Unknown set to 0

If Request("Material") <> "0" then
If mySQL > "" then
mySQL = mySQL & " AND (Material LIKE '%::Material::%')"
else
mySQL = "(Material LIKE '%::Material::%')"
end if
end if

'!!!!!!!!!!!!!!!!!!!Multiple Search Across Fields

If Request("Item") > "" then
If mySQL > "" then
mySQL = mySQL & " AND ((Item1 LIKE '%::Item::%') OR (Item2 LIKE '%::Item::%') OR (Item3 LIKE '%::Item::%') OR (Item4 LIKE '%::Item::%') OR (Item5 LIKE '%::Item::%') OR (Item6 LIKE '%::Item::%') OR (Item7 LIKE '%::Item::%') OR (Item8 LIKE '%::Item::%') OR (Item9 LIKE '%::Item::%') OR (Item10 LIKE '%::Item::%') OR (Item11 LIKE '%::Item::%') OR (Item12 LIKE '%::Item::%'))"
else
mySQL = "((Item1 LIKE '%::Item::%') OR (Item2 LIKE '%::Item::%') OR (Item3 LIKE '%::Item::%') OR (Item4 LIKE '%::Item::%') OR (Item5 LIKE '%::Item::%') OR (Item6 LIKE '%::Item::%') OR (Item7 LIKE '%::Item::%') OR (Item8 LIKE '%::Item::%') OR (Item9 LIKE '%::Item::%') OR (Item10 LIKE '%::Item::%') OR (Item11 LIKE '%::Item::%') OR (Item12 LIKE '%::Item::%'))"
end if
end if

If Request("DBOL") > "" then
If mySQL > "" then
mySQL = mySQL & " AND (DBOL LIKE '%::DBOL::%')"
else
mySQL = "(DBOL LIKE '%::DBOL::%')"
end if
end if

'!!!!!!!!!!!!!!!!!!!!Between Dates

If Request("InDate") > "" then 
If mySQL > "" then 
mySQL = mySQL & " AND BETWEEN (#::InDate::# and #::DDate::#)" 
else 
mySQL = " BETWEEN (#::InDate::# and #::DDate::#) 
End if 
End if 

'If query string is still blank, set a No Record Foundvalue
If mySQL = "" then
mySQL = "(CarrierName LIKE 'xxxx')"
end if

%>


What do you think? And, do I need to place restrictions on the text box???




rdouglass -> RE: Advanced Database Search with FrontPage (12/10/2004 10:58:36)

quote:

What do you think?


The eternal question.[:D]

Well, I think the answer is "Does it do what you want?" [;)]

It doesn't look like you're checking any date fields: is what BETWEEN (#::InDate::# and #::DDate::#) ? Also, you're not checking for any blanks in DDate; the code will error if you don't allow or check for that.

What happens when you use the code I posted?



Avichai -> RE: Advanced Database Search with FrontPage (12/10/2004 11:23:32)

Okay, I would have done it your way from the beginning but I didn't see it until now..... I thought you reposted my gibbrish from before - SORRY.

Okay... here is the error I got when trying

Database Results Error
Description: Between operator without And in query expression '((Sealed LIKE '%%') AND (SealMatchBol LIKE '%%') AND (FGProduct LIKE '%%') AND (LiveDrop LIKE '%%') AND (AvailableToLoad LIKE '%%') AND (LoadedWith LIKE '%%') AND (CannotBeLoaded LIKE '%%') AND (NoLongerInYard LIKE '%%') AND (Material LIKE '%%') AND (Cont'.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine

One or more form fields were empty. You should provide default values for all form fields that are used in the query.

Also, will this return all records between these dates if nothing else is inserted...i.e. on loading the page.



rdouglass -> RE: Advanced Database Search with FrontPage (12/10/2004 11:42:11)

quote:

mySQL = mySQL &"(InDate BETWEEN (#" & myInDate & "# and #" & myDDate & "#) AND DDate BETWEEN (#" & myInDate & "# and #" & myDDate & "#))"


If you're using my code, try changing the parens to read like this:

mySQL = mySQL &"((InDate BETWEEN #" & myInDate & "# and #" & myDDate & "#) AND (DDate BETWEEN #" & myInDate & "# and #" & myDDate & "#))"

Any luck with that?

If not, directly after hat line put this in to test:

Response.write(mySQL)
Response.end

That should output your SQL to the browser without trying the DB connection. Then we can confirm exactly what SQL is being sent to the DB.



Avichai -> RE: Advanced Database Search with FrontPage (12/10/2004 11:49:49)



Well, I'm closer. The error is gone, but it is not pulling any records...... Records that should return as between the two dates

Okay... I think the problem is not with the dates, but with the change that I made in order to use a drop box instead of a text box.

here is what I changed it to
If Request("Sealed") <> "0" then
If mySQL > "" then
mySQL = mySQL & " AND(Sealed LIKE '%::Sealed::%')"
else
mySQL = "(Sealed LIKE '%::Sealed::%')"
end if
end if

(I placed a choice of "unknown" in the drop box with a value of "0" and selected it)

for the drop box to work do I need to and an initial requester value or something?

On second thought, I don't know what the problem is . The above looks correct to me., but for some reason it isn't working

I've reposted the new entire code : 

<%
'Function to build query for search
Dim mySQL
mySQL = ""
If Request("CarrierName") = "" then
mySQL = ""
else
mySQL = "(CarrierName LIKE '%::CarrierName::%')"
end if

If Request("TrailerNumber") > "" then
If mySQL = "" then
mySQL = "(TrailerNumber LIKE '%::TrailerNumber::%')"
else
mySQL = mySQL & " AND (TrailerNumber LIKE '%::TrailerNumber::%')"
end if
end if

If Request("Sealed") <> "0" then
If mySQL > "" then
mySQL = mySQL & " AND(Sealed LIKE '%::Sealed::%')"
else
mySQL = "(Sealed LIKE '%::Sealed::%')"
end if
end if

If Request("SealMatchBol") <> "0" then
If mySQL > "" then
mySQL = mySQL & " AND (SealMatchBol LIKE '%::SealMatchBol::%')"
else
mySQL = "(SealMatchBol LIKE '%::SealMatchBol::%')"
end if
end if

If Request("ReturnAuthorizationNumber") > "" then
If mySQL > "" then
mySQL = mySQL & " AND (ReturnAuthorizationNumber LIKE '%::ReturnAuthorizationNumber::%')"
else
mySQL = "(ReturnAuthorizationNumber LIKE '%::ReturnAuthorizationNumber::%')"
end if
end if

If Request("IncomingBOLNumber") > "" then
If mySQL > "" then
mySQL = mySQL & " AND (IncomingBOLNumber LIKE '%::IncomingBOLNumber::%')"
else
mySQL = "(IncomingBOLNumber LIKE '%::IncomingBOLNumber::%')"
end if
end if

If Request("ReleaseNumber") > "" then
If mySQL > "" then
mySQL = mySQL & " AND (ReleaseNumber LIKE '%::ReleaseNumber::%')"
else
mySQL = "(ReleaseNumber LIKE '%::ReleaseNumber::%')"
end if
end if


If Request("FGProduct") <> "0" then
If mySQL > "" then
mySQL = mySQL & " AND (FGProduct LIKE '%::FGProduct::%')"
else
mySQL = "(FGProduct LIKE '%::FGProduct::%')"
end if
end if

If Request("LiveDrop") <> "0" then
If mySQL > "" then
mySQL = mySQL & " AND (LiveDrop LIKE '%::LiveDrop::%')"
else
mySQL = "(LiveDrop LIKE '%::LiveDrop::%')"
end if
end if

If Request("AvailableToLoad") <> "0" then
If mySQL > "" then
mySQL = mySQL & " AND (AvailableToLoad LIKE '%::AvailableToLoad::%')"
else
mySQL = "(AvailableToLoad LIKE '%::AvailableToLoad::%')"
end if
end if

If Request("LoadedWith") <> "0" then
If mySQL > "" then
mySQL = mySQL & " AND (LoadedWith LIKE '%::LoadedWith::%')"
else
mySQL = "(LoadedWith LIKE '%::LoadedWith::%')"
end if
end if

If Request("CannotBeLoaded") <> "0" then
If mySQL > "" then
mySQL = mySQL & " AND (CannotBeLoaded LIKE '%::CannotBeLoaded::%')"
else
mySQL = "(CannotBeLoaded LIKE '%::CannotBeLoaded::%')"
end if
end if

If Request("NoLongerInYard") <> "0" then
If mySQL > "" then
mySQL = mySQL & " AND (NoLongerInYard LIKE '%::NoLongerInYard::%')"
else
mySQL = "(NoLongerInYard LIKE '%::NoLongerInYard::%')"
end if
end if

If Request("Material") <> "0" then
If mySQL > "" then
mySQL = mySQL & " AND (Material LIKE '%::Material::%')"
else
mySQL = "(Material LIKE '%::Material::%')"
end if
end if

If Request("Item") > "" then
If mySQL > "" then
mySQL = mySQL & " AND ((Item1 LIKE '%::Item::%') OR (Item2 LIKE '%::Item::%') OR (Item3 LIKE '%::Item::%') OR (Item4 LIKE '%::Item::%') OR (Item5 LIKE '%::Item::%') OR (Item6 LIKE '%::Item::%') OR (Item7 LIKE '%::Item::%') OR (Item8 LIKE '%::Item::%') OR (Item9 LIKE '%::Item::%') OR (Item10 LIKE '%::Item::%') OR (Item11 LIKE '%::Item::%') OR (Item12 LIKE '%::Item::%'))"
else
mySQL = "((Item1 LIKE '%::Item::%') OR (Item2 LIKE '%::Item::%') OR (Item3 LIKE '%::Item::%') OR (Item4 LIKE '%::Item::%') OR (Item5 LIKE '%::Item::%') OR (Item6 LIKE '%::Item::%') OR (Item7 LIKE '%::Item::%') OR (Item8 LIKE '%::Item::%') OR (Item9 LIKE '%::Item::%') OR (Item10 LIKE '%::Item::%') OR (Item11 LIKE '%::Item::%') OR (Item12 LIKE '%::Item::%'))"
end if
end if

If Request("Description") > "" then
If mySQL > "" then
mySQL = mySQL & " AND ((Description1 LIKE '%::Description::%') OR (Description2 LIKE '%::Description::%') OR (Description3 LIKE '%::Description::%') OR (Description4 LIKE '%::Description::%') OR (Description5 LIKE '%::Description::%') OR (Description6 LIKE '%::Description::%') OR (Description7 LIKE '%::Description::%') OR (Description8 LIKE '%::Description::%') OR (Description9 LIKE '%::Description::%') OR (Description10 LIKE '%::Description::%') OR (Description11 LIKE '%::Description::%') OR (Description12 LIKE '%::Description::%'))"
else
mySQL = "((Description1 LIKE '%::Description::%') OR (Description2 LIKE '%::Description::%') OR (Description3 LIKE '%::Description::%') OR (Description4 LIKE '%::Description::%') OR (Description5 LIKE '%::Description::%') OR (Description6 LIKE '%::Description::%') OR (Description7 LIKE '%::Description::%') OR (Description8 LIKE '%::Description::%') OR (Description9 LIKE '%::Description::%') OR (Description10 LIKE '%::Description::%') OR (Description11 LIKE '%::Description::%') OR (Description12 LIKE '%::Description::%'))"
end if
end if

If Request("LotNumber") > "" then
If mySQL > "" then
mySQL = mySQL & " AND ((LotNumber1 = '::LotNumber::') OR (LotNumber2 = '::LotNumber::') OR (LotNumber3 = '::LotNumber::') OR (LotNumber4 = '::LotNumber::') OR (LotNumber5 = '::LotNumber::') OR (LotNumber6 = '::LotNumber::') OR (LotNumber7 = '::LotNumber::') OR (LotNumber8 = '::LotNumber::') OR (LotNumber9 = '::LotNumber::') OR (LotNumber10 = '::LotNumber::') OR (LotNumber11 = '::LotNumber::') OR (LotNumber12 = '::LotNumber::'))"
else
mySQL = "((LotNumber1 = '::LotNumber::') OR (LotNumber2 = '::LotNumber::') OR (LotNumber3 = '::LotNumber::') OR (LotNumber4 = '::LotNumber::') OR (LotNumber5 = '::LotNumber::') OR (LotNumber6 = '::LotNumber::') OR (LotNumber7 = '::LotNumber::') OR (LotNumber8 = '::LotNumber::') OR (LotNumber9 = '::LotNumber::') OR (LotNumber10 = '::LotNumber::') OR (LotNumber11 = '::LotNumber::') OR (LotNumber12 = '::LotNumber::'))"
end if
end if

If Request("TankerTCode") > "" then
If mySQL > "" then
mySQL = mySQL & " AND (TankerTCode LIKE '%::TankerTCode::%')"
else
mySQL = "(TankerTCode LIKE '%::TankerTCode::%')"
end if
end if

If Request("Contents") <> "0" then
If mySQL > "" then
mySQL = mySQL & " AND (Contents LIKE '%::Contents::%')"
else
mySQL = "(Contents LIKE '%::Contents::%')"
end if
end if

If Request("DSealNumber") > "" then
If mySQL > "" then
mySQL = mySQL & " AND (DSealNumber LIKE '%::DSealNumber::%')"
else
mySQL = "(DSealNumber LIKE '%::DSealNumber::%')"
end if
end if

If Request("DBOL") > "" then
If mySQL > "" then
mySQL = mySQL & " AND (DBOL LIKE '%::DBOL::%')"
else
mySQL = "(DBOL LIKE '%::DBOL::%')"
end if
end if

If trim(Request("InDate")&"") > "" then
myInDate = Request("InDate")
ELSE
myInDate = "01/01/1900"
END IF
If trim(Request("DDate")&"") > "" then
myDDate = Request("DDate")
ELSE
myDDate = "12/31/2999"
END IF

If mySQL > "" then
mySQL = mySQL & " AND"
end if

mySQL = mySQL &"((InDate BETWEEN #" & myInDate & "# and #" & myDDate & "#) AND (DDate BETWEEN #" & myInDate & "# and #" & myDDate & "#))" 

'If query string is still blank, set a No Record Foundvalue
If mySQL = "" then
mySQL = "(CarrierName LIKE 'xxxx')"
end if

%>


<%
fp_sQry="SELECT * FROM Log WHERE ("&mySQL&") ORDER BY CarrierName ASC"
fp_sDefault=0
fp_sNoRecords="<tr><td colspan=103 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="TruckLog"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ID=3&CarrierName=202&TrailerNumber=202&TractorNumber=202&Sealed=202&SealMatchBol=202&SealNumber=202&PickupName=202&ReturnAuthorizationNumber=202&IncomingBOLNumber=202&ReleaseNumber=202&InDate=202&InTime=202&EnteredBy=202&LiveDrop=202&AvailableToLoad=202&LoadedWith=202&CannotBeLoaded=202&NoLongerInYard=202&Contents=202&Comments1=203&UDate=202&UTime=202&Material=202&FGProduct=202&Pallet=202&Item1=202&Item2=202&Item3=202&Item4=202&Item5=202&Item6=202&Item7=202&Item8=202&Item9=202&Item10=202&Item11=202&Item12=202&Description1=202&Description2=202&Description3=202&Description4=202&Description5=202&Description6=202&Description7=202&Description8=202&Description9=202&Description10=202&Description11=202&Description12=202&Quantity1=202&Quantity2=202&Quantity3=202&Quantity4=202&Quantity5=202&Quantity6=202&Quantity7=202&Quantity8=202&Quantity9=202&Quantity10=202&Quantity11=202&Quantity12=202&LotNumber1=202&LotNumber2=202&LotNumber3=202&LotNumber4=202&LotNumber5=202&LotNumber6=202&LotNumber7=202&LotNumber8=202&LotNumber9=202&LotNumber10=202&LotNumber11=202&LotNumber12=202&UnitSize1=202&UnitSize2=202&UnitSize3=202&UnitSize4=202&UnitSize5=202&UnitSize6=202&UnitSize7=202&UnitSize8=202&UnitSize9=202&UnitSize10=202&UnitSize11=202&UnitSize12=202&Vendor=202&PalletCount=202&RcvdProductQuality=202&RcvdTrailerQuality=202&RcvdPalletQuality=202&Comments2=203&UEnteredBy=202&TankerTCode=202&PumpTimeStart=202&PumpTimeFinish=202&DSealNumber=202&DBOL=202&DPalletCount=202&DDate=202&DTime=202&DEnteredBy=202&DContents=202&Comments3=203&"
fp_iDisplayCols=103
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>



Avichai -> RE: Advanced Database Search with FrontPage (12/10/2004 12:15:02)

Okay.... I see the problem.

All of my records have an InDate, but NOT all of them have an Out Date (DDate)

I need the code to still search, even if the record DDate would be empty


Here is what I thought could come close 

If trim(Request("InDate")&"") > "" then
myInDate = Request("InDate")
ELSE
myInDate = "01/01/1900"
END IF
If trim(Request("DDate")&"") > "" then
myDDate = Request("DDate")
ELSE
myDDate = ""
END IF

'my changes

If myDDate > "" then
If mySQL > "" then
mySQL = mySQL & " AND (InDate = #" & myInDate &"#)
else
mySQL = mySQL & " AND"
end if

mySQL = mySQL &"((InDate BETWEEN #" & myInDate & "# and #" & myDDate & "#) AND (DDate BETWEEN #" & myInDate & "# and #" & myDDate & "#))"




rdouglass -> RE: Advanced Database Search with FrontPage (12/10/2004 13:17:58)

quote:

even if the record DDate would be empty


I knew this was going to be fun...[:D]

I know this may be taking you in another direction, but if it was me, at this point this is what I would do:

IN Access build a query to match exactly your table structure (name for name) BUT instead of DDate being just a field, make it a calculation instead. Make DDate be 01/01/1999 or something like that instead of Null (or empty).

Do you see what I'm suggesting? Then you'd just call the query instead of the table (as long as the fields were still in the same order) and any Null's would be dealt with. Writing back to the table, you'd still use the table (instead of the query) in your SQL, but for reading, use the query.

Does that help at all?



Avichai -> RE: Advanced Database Search with FrontPage (12/10/2004 13:23:19)

Okay.... You really kinda lost me there, but you are right this is fun....

However, I have to prepare for tonight and won't be able to work again until Saturday night. I don't know if the other solution that you propose will be too much for me to muddle through. I'm supposed to present this thing on Monday 7 am....

I believe this is my last issue. There must be a way to right this.....

The final piece.




Avichai -> RE: Advanced Database Search with FrontPage (12/10/2004 16:32:13)

Does this look right? Will something like this work?

Can you or someone add/change it to fit? 

If Request("DBOL") > "" then
If mySQL > "" then
mySQL = mySQL & " AND (DBOL LIKE '%::DBOL::%')"
else
mySQL = "(DBOL LIKE '%::DBOL::%')"
end if
end if

If Request ("InDate") > "" then
If Request("DDate") = "" then
If mySQL > "" then
mySQL = mySQL & " AND (InDate = #" & InDate & "#)"
else
mySQL = ""
end if
end if
end if

If Request ("DDate") > "" then
If Request("InDate") = "" then
If mySQL > "" then
mySQL = mySQL & " AND (DDate = #" & DDate & "#)"
else
mySQL = ""
end if
end if
end if

If Request ("InDate") > "" then
If Request ("DDate") > "" then
If mySQL > "" then
mySQL = mySQL & " AND ((InDate BETWEEN #" & InDate & "# and #" & DDate & "#) AND (DDate BETWEEN #" & InDate & "# and #" & DDate & "#))"
else
mySQL = ""
End if
End if
End if



Avichai -> RE: Advanced Database Search with FrontPage (12/11/2004 18:54:03)

Okay - Here is my latest try! and below is the error that I am receiving.... ANY suggestions 

If Request("InDate") > "" then
If Request("DDate") = "" then
If mySQL > "" then
mySQL = mySQL & " AND (InDate = #" & InDate & "#)"
else
mySQL = "(InDate = #" & InDate & "#)"
end if
end if
end if

If Request("DDate") > "" then
If Request("InDate") = "" then
If mySQL > "" then
mySQL = mySQL & " AND (DDate = #" & DDate & "#)"
else
mySQL = "(DDate = #" & DDate & "#)"
end if
end if
end if

If Request("InDate") > "" then
If Request("DDate") > "" then
If mySQL > "" then
mySQL = mySQL & " AND ((InDate BETWEEN #" & InDate & "# and #" & DDate & "#) AND (DDate BETWEEN #" & InDate & "# and #" & DDate & "#))"
else
mySQL = "((InDate BETWEEN #" & InDate & "# and #" & DDate & "#) AND (DDate BETWEEN #" & InDate & "# and #" & DDate & "#))"
End if
End if
End if


Database Results Error
Description: Syntax error in date in query expression '((InDate = ##))'.
Number: -2147217913 (0x80040E07)
Source: Microsoft JET Database Engine

Database Results Error
Description: Syntax error in date in query expression '(((InDate BETWEEN ## and ##) AND (DDate BETWEEN ## and ##)))'.
Number: -2147217913 (0x80040E07)
Source: Microsoft JET Database Engine



Avichai -> RE: Advanced Database Search with FrontPage (12/11/2004 19:18:21)

YES!!! THE WINNING ANSWER! 

If Request("InDate") > "" then
If Request("DDate") = "" then
If mySQL > "" then
mySQL = mySQL & " AND (InDate = #" & Request("InDate") & "#)"
else
mySQL = "(InDate = #" & Request("InDate") & "#)"
end if
end if
end if

If Request("DDate") > "" then
If Request("InDate") = "" then
If mySQL > "" then
mySQL = mySQL & " AND (DDate = #" & Request("DDate") & "#)"
else
mySQL = "(DDate = #" & Request("DDate") & "#)"
end if
end if
end if

If Request("InDate") > "" then
If Request("DDate") > "" then
If mySQL > "" then
mySQL = mySQL & " AND ((InDate BETWEEN #" & Request("InDate") & "# and #" & Request("DDate") & "#) AND (DDate BETWEEN #" & Request("InDate") & "# and #" & Request("DDate") & "#))"
else
mySQL = "((InDate BETWEEN #" & Request("InDate") & "# and #" & Request("DDate") & "#) AND (DDate BETWEEN #" & Request("InDate") & "# and #" & Request("DDate") & "#))"
End if
End if
End if



Avichai -> RE: Advanced Database Search with FrontPage (12/13/2004 10:08:49)

Hey! This doesn't "exactly" work..... still have to have two dates. Although it works "good enough" for now. I will have to work on the other suggestion above.... as soon as i understand it a little more....




Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.234375