|
| |
|
|
Avichai
Posts: 44
Joined: 12/7/2004
Status: offline
 |
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
|
|
|
|
Avichai
Posts: 44
Joined: 12/7/2004
Status: offline
|
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)
| |
|
|
|
Avichai
Posts: 44
Joined: 12/7/2004
Status: offline
|
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
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Avichai
Posts: 44
Joined: 12/7/2004
Status: offline
|
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
Posts: 44
Joined: 12/7/2004
Status: offline
|
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
| |
|
|
|
Avichai
Posts: 44
Joined: 12/7/2004
Status: offline
|
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
Posts: 44
Joined: 12/7/2004
Status: offline
|
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
Posts: 44
Joined: 12/7/2004
Status: offline
|
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
< Message edited by Avichai -- 12/11/2004 19:20:21 >
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
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.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Avichai
Posts: 44
Joined: 12/7/2004
Status: offline
|
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
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
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.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Avichai
Posts: 44
Joined: 12/7/2004
Status: offline
|
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???
| |
|
|
|
Avichai
Posts: 44
Joined: 12/7/2004
Status: offline
|
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.
< Message edited by Avichai -- 12/10/2004 11:42:49 >
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
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.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Avichai
Posts: 44
Joined: 12/7/2004
Status: offline
|
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
%>
< Message edited by Avichai -- 12/10/2004 12:05:51 >
| |
|
|
|
Avichai
Posts: 44
Joined: 12/7/2004
Status: offline
|
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 & "#))"
< Message edited by Avichai -- 12/12/2004 8:03:29 >
| |
|
|
|
Avichai
Posts: 44
Joined: 12/7/2004
Status: offline
|
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
Posts: 44
Joined: 12/7/2004
Status: offline
|
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
< Message edited by Avichai -- 12/11/2004 19:19:31 >
| |
|
|
|
Avichai
Posts: 44
Joined: 12/7/2004
Status: offline
|
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
< Message edited by Avichai -- 12/11/2004 19:18:59 >
| |
|
|
|
Avichai
Posts: 44
Joined: 12/7/2004
Status: offline
|
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
< Message edited by Avichai -- 12/12/2004 8:02:08 >
| |
|
|
|
Avichai
Posts: 44
Joined: 12/7/2004
Status: offline
|
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....
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
