Show if region not working

Hi imjusthere,

The only thing I can think of is to try writing the conditional this way:

If ((Recordset1.Fields.Item("proof_shoppingcart").Value) = "Yes" And (Recordset1.Fields.Item("cart_activate").Value) = 1)

The extra parentheses should force the whole expression to be evaluated.

Nope, didn't help.  I just seems strange to me that the first condition works fine but the second causes all records to disapear.  Would it help if I included the code for the whole page?

It might.  

Wait. The second condition defauts to a null value when deleted, which is what I'm doing because it's in a related table.  Perhaps the null is causing the confusion.  How would I go about accounting for null values?

Yes, that's the problem.  I need to account for nulls in the second condition because they are left outer joined tables so many records will contain null values.  How do I make this null friendly?

Without seeing anything else, it may be that your SQL is too restrictive.  You would want to recode it so that if the second condition does not exist, the sql returns the first condition and a null value for the second.

I think the way you have it set up now is that if the second value does not exist, the SQL doesn't return the record from the first table so the statement fails no matter what.

Tried this too.  Didn't work.  Same result. No records displayed in looper.

<%
' Show IF Conditional region1
  If Not IsNull((Recordset1.Fields.Item("cart_activate").Value)) Or Recordset1.Fields.Item("cart_activate").Value = "" Then
%>
                      <form name="eCart1_1_ATC_<%=Recordset1.Fields("proof_ID").value%>" method="POST" action="<%=cStr(Request.ServerVariables("SCRIPT_NAME"))%><%=WA_eCart_IIf((Request.ServerVariables("QUERY_STRING") <> ""), "?" & Request.ServerVariables("QUERY_STRING"), "")%>">
                        <input type="hidden" name="eCart1_1_ID_Add" value="<%=Recordset1.Fields("proof_ID").value%>" >
                        <select name="eCart1_1_Quantity_Add">
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity1").Value)%> selected><%=(Recordset1.Fields.Item("proof_quantity1").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity2").Value)%>><%=(Recordset1.Fields.Item("proof_quantity2").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity3").Value)%>><%=(Recordset1.Fields.Item("proof_quantity3").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity4").Value)%>><%=(Recordset1.Fields.Item("proof_quantity4").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity5").Value)%>><%=(Recordset1.Fields.Item("proof_quantity5").Value)%></option>
                        </select>
                        <input name="eCart1_1_ATC" type="image" value="Add to Cart" src="../WA_eCart/Images/Pacifica/Btn4_EN_addtocart.gif" align="middle" border="0">
                      </form>
                      <%
  End If
' End Conditional region1
%>

Can you post the SQL you are using to generate recordset1?

Ok, here's the whole mess.  I'm using Access so my relationships and left outer join are done in Access.  I created a querry which I use in my select statement of my SQL.

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="../WA_eCart/WA_eCart_Definition_VB.asp" -->
<!--#include file="../WA_eCart/eCart1_VB.asp" -->
<!--#include file="../includes/cssmenus2/MX_Menu2.inc.asp" -->
<!--#include file="../Connections/DSNLESS.asp" -->
<!--#include file="../includes/tNG/tNG.inc.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Session("kt_Associate") <> "") Then
  Recordset1__MMColParam = Session("kt_Associate")
End If
%>
<%
Dim Recordset1__sort
Recordset1__sort = "%"
If (Request.Form("keywords") <> "") Then
  Recordset1__sort = Request.Form("keywords")
End If
%>
<%
Dim Recordset1__sort2
Recordset1__sort2 = "%"
If (Request.Form("keywords")  <> "") Then
  Recordset1__sort2 = Request.Form("keywords")
End If
%>
<%
Dim Recordset1__sort3
Recordset1__sort3 = "%"
If (Request.Form("keywords")  <> "") Then
  Recordset1__sort3 = Request.Form("keywords")
End If
%>
<%
Dim Recordset1__sort4
Recordset1__sort4 = "%"
If (Request.Form("keywords")  <> "") Then
  Recordset1__sort4 = Request.Form("keywords")
End If
%>
<%
Dim Recordset1__sort5
Recordset1__sort5 = "%"
If (Request.Form("keywords")  <> "") Then
  Recordset1__sort5 = Request.Form("keywords")
End If
%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_DSNLESS_STRING
Recordset1_cmd.CommandText = "SELECT * FROM [proofs Query] WHERE proof_client = ? AND (proof_formNumber LIKE ? OR proof_date LIKE ? OR proof_desc LIKE ? OR proof_keywords LIKE ? OR proof_tab LIKE ?) ORDER BY proof_date DESC"
Recordset1_cmd.Prepared = true
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 200, 1, 255, Recordset1__MMColParam) ' adVarChar
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param2", 200, 1, 255, "%" + Recordset1__sort + "%") ' adVarChar
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param3", 200, 1, 255, "%" + Recordset1__sort2 + "%") ' adVarChar
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param4", 200, 1, 255, "%" + Recordset1__sort3 + "%") ' adVarChar
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param5", 200, 1, 255, "%" + Recordset1__sort4 + "%") ' adVarChar
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param6", 200, 1, 255, "%" + Recordset1__sort5 + "%") ' adVarChar

Recordset1_cmd.ActiveConnection.CursorLocation = 3
Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>
<%
Dim rsCSSMenu1
Dim rsCSSMenu1_numRows

Set rsCSSMenu1 = Server.CreateObject("ADODB.Recordset")
rsCSSMenu1.ActiveConnection = MM_DSNLESS_STRING
rsCSSMenu1.Source = "SELECT * FROM menu WHERE visible_mnu = 1 ORDER BY order_mnu ASC"
rsCSSMenu1.CursorType = 3
rsCSSMenu1.CursorLocation = 2
rsCSSMenu1.LockType = 1
rsCSSMenu1.Open()

rsCSSMenu1_numRows = 0
%>
<%
'Begin DynCSSMenu1
  Set DynCSSMenu1 = new MX_Menu2
  DynCSSMenu1.Init "DynCSSMenu1"
  DynCSSMenu1.setQuery rsCSSMenu1
  DynCSSMenu1.setPK "ID_mnu"
  DynCSSMenu1.setFK "idmnu_mnu"
  DynCSSMenu1.setNameField "label_mnu"
  DynCSSMenu1.setTitleField "tooltip_mnu"
  'URL parameters
  DynCSSMenu1.setLinkField "urllink_mnu"
  DynCSSMenu1.setTargetField "target_mnu"
  'Layout
  DynCSSMenu1.highlightCurrent true
  DynCSSMenu1.setLevel -1
  DynCSSMenu1.setLayout "horizontal"
  DynCSSMenu1.setSkin "colortest2"
  DynCSSMenu1.setShowTimeout 50
  DynCSSMenu1.setHideTimeout 50
  DynCSSMenu1.setExitTimeout 200
  DynCSSMenu1.setSubMenuOffset 0, 0, 0, 0
  DynCSSMenu1.setAnimation "slide"
  DynCSSMenu1.setImgReplace false
'End DynCSSMenu1
%>
<%
Dim Repeat_Recordset1__numRowsHL
Dim Repeat_Recordset1__indexHL

Repeat_Recordset1__numRowsHL = -1
Repeat_Recordset1__indexHL = 0
Recordset1_numRows = Recordset1_numRows + Repeat_Recordset1__numRowsHL
%>
<%
' WA eCart AddToCart
if (cStr(Request.Form("eCart1_1_ATC")) <> "" OR cStr(Request.Form("eCart1_1_ATC.x")) <> "")     then
  ATC_itemID = cStr(Request.Form("eCart1_1_ID_Add"))
  ATC_AddIfIn = 2
  ATC_RedirectAfter = "cart.asp"
  ATC_RedirectIfIn  = ""
  ATC_RSPos = Recordset1.AbsolutePosition
  Recordset1.MoveFirst()
  DO while (NOT Recordset1.EOF)
    if (cStr(Recordset1.Fields("proof_ID").value) = cStr(ATC_itemID))  then
      ATC_itemName = "" & cStr((Recordset1.Fields.Item("proof_client").Value)) & ""' column binding
      ATC_itemDescription = "" & cStr((Recordset1.Fields.Item("proof_desc").Value)) & ""' column binding
      ATC_itemWeight = cDbl("" & cStr((Recordset1.Fields.Item("proof_weight").Value)) & "")' column binding
      ATC_itemQuantity = "" & cStr(Request.Form("eCart1_1_Quantity_Add")) & ""' column binding
      ATC_itemPrice = cDbl("0")' column binding
      ATC_itemthumb = "" & cStr((Recordset1.Fields.Item("proof_thumb").Value)) & ""' column binding
      ATC_itemaddedshipping = cDbl("" & cStr((Recordset1.Fields.Item("proof_addedshipping").Value)) & "")' column binding
      ATC_itemquantity1 = cDbl("" & cStr((Recordset1.Fields.Item("proof_quantity1").Value)) & "")' column binding
      ATC_itemquantity2 = cDbl("" & cStr((Recordset1.Fields.Item("proof_quantity2").Value)) & "")' column binding
      ATC_itemquantity3 = cDbl("" & cStr((Recordset1.Fields.Item("proof_quantity3").Value)) & "")' column binding
      ATC_itemquantity4 = cDbl("" & cStr((Recordset1.Fields.Item("proof_quantity4").Value)) & "")' column binding
      ATC_itemquantity5 = cDbl("" & cStr((Recordset1.Fields.Item("proof_quantity5").Value)) & "")' column binding
      ATC_itemprice1 = cDbl("" & cStr((Recordset1.Fields.Item("proof_price1").Value)) & "")' column binding
      ATC_itemprice2 = cDbl("" & cStr((Recordset1.Fields.Item("proof_price2").Value)) & "")' column binding
      ATC_itemprice3 = cDbl("" & cStr((Recordset1.Fields.Item("proof_price3").Value)) & "")' column binding
      ATC_itemprice4 = cDbl("" & cStr((Recordset1.Fields.Item("proof_price4").Value)) & "")' column binding
      ATC_itemprice5 = cDbl("" & cStr((Recordset1.Fields.Item("proof_price5").Value)) & "")' column binding
      Exit DO
    end if
    Recordset1.MoveNext()
  Loop
  Recordset1.AbsolutePosition = ATC_RSPos
  if (IsNumeric(ATC_itemQuantity) AND ATC_itemQuantity <> 0)     then
    set eCart1 = eCart1_AddToCart(ATC_AddIfIn, ATC_RedirectIfIn, ATC_itemID, ATC_itemName, ATC_itemDescription, ATC_itemWeight, ATC_itemQuantity, ATC_itemPrice, ATC_itemthumb, ATC_itemaddedshipping, ATC_itemquantity1, ATC_itemquantity2, ATC_itemquantity3, ATC_itemquantity4, ATC_itemquantity5, ATC_itemprice1, ATC_itemprice2, ATC_itemprice3, ATC_itemprice4, ATC_itemprice5)
    if (ATC_RedirectAfter <> "" AND eCart1_redirStr = "")     then
      eCart1_redirStr = ATC_RedirectAfter
    end if
    Session("WAEC_ContinueRedirect") = cStr(Request.ServerVariables("Script_Name"))  & "?" & cStr(Request.QueryString())
  end if
end if
%>
<%
' WA eCart Redirect
if (eCart1_redirStr <> "")     then
  Response.Redirect(eCart1_redirStr)
end if
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>

<title>Databar Inc. - Client Library</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script id="PLHFOScript" language="JavaScript" src="../Pluginlab/Scripts/PLHFO.js">/* PLHFOMenu script */</script>

<style type="text/css">
<!--
.style1 {font-family: Verdana, Arial, Helvetica, sans-serif}
-->
</style>
<script src="../includes/cssmenus2/js/cssmenus.js" type="text/javascript"></script>
<script src="../includes/cssmenus2/js/cssmenus.js" type="text/javascript"></script>
<link href="../includes/cssmenus2/skins/colortest2/horizontal.css" rel="stylesheet" type="text/css" />
<script src="../includes/cssmenus2/js/animation.js" type="text/javascript"></script>
<meta name="keywords" content="design databar logo brochures corporate identity forms powerpoint presentations fonts designers designer custom newsletters flash animations animation edgewood washington">
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_reloadPage(init) {  //reloads the window if Nav4 resized
  if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
    document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
  else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);

function MM_swapImgRestore() { //v3.0
  var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&x.oSrc;i++) x.src=x.oSrc;
}

function MM_preloadImages() { //v3.0
  var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
    var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
    if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
}

function MM_findObj(n, d) { //v4.01
  var p,i,x;  if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
  if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
  for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
  if(!x && d.getElementById) x=d.getElementById(n); return x;
}

function MM_swapImage() { //v3.0
  var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3)
   if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];}
}
//-->
</script>
<script src="../includes/cssmenus2/js/cssmenus.js" type="text/javascript"></script>
<script src="../includes/cssmenus2/js/animation.js" type="text/javascript"></script>
<link href="../includes/cssmenus2/skins/aqua_blue/horizontal.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
.style2 {font-size: 12px; font-family: Verdana, Arial, Helvetica, sans-serif;}
-->
</style>
<script src="../Scripts/AC_RunActiveContent.js" type="text/javascript"></script>
</head>

<body onLoad="PLHFO_onload();MM_preloadImages('../images/librarydown.jpg','../images/emailartdeptdown.jpg')">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td><table width="100%" border="0" background="../images/spacermenu.jpg">
      <tr>
        <td width="1020" height="141" valign="top" background="../images/main.jpg"><script type="text/javascript">
AC_FL_RunContent( 'codebase','http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=7,0,19,0','width','159','height','64','src','../headers/Header3','quality','high','pluginspage','http://www.macromedia.com/go/getflashplayer','movie','../headers/Header3' ); //end AC code
</script><noscript><object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=7,0,19,0" width="159" height="64">
            <param name="movie" value="../headers/Header3.swf">
            <param name="quality" value="high">
            <embed src="../headers/Header3.swf" quality="high" pluginspage="http://www.macromedia.com/go/getflashplayer" type="application/x-shockwave-flash" width="159" height="64"></embed>
        </object></noscript></td>
        <td align="right" valign="bottom" nowrap class="style2"><%= FormatDateTime(Date, 1)%></td>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td width="100%" height="19" align="left" background="../menubar.jpg"><table width="100%" height="32" border="0" cellpadding="0" cellspacing="0" background="../images/menuspacer3.gif">
      <tr>
        <td width="100%" align="right"><%
  'Dynamic CSS Menu
  Response.write (DynCSSMenu1.render)
%></td>
      </tr>
    </table></td>
  </tr>
</table>
<table width="100%" border="1" cellspacing="0" cellpadding="0">
  <tr>
    <td valign="top"><table width="100%" border="0" cellspacing="10" cellpadding="10">
      <tr>
        <td valign="top"><div id="cssMenu2" class="horizontal">
          <ul class="aqua_blue">
            <li> <a href="client2.asp" title="All Records">All Records</a> </li>
            <li> <a href="clientbc2.asp" title="Business Cards">Business
                Cards</a> </li>
            <li> <a href="clientchecks2.asp" title="Checks">Checks</a> </li>
            <li> <a href="clientenvelopes2.asp" title="Envelopes">Envelopes</a> </li>
            <li> <a href="clientforms2.asp" title="Forms">Forms</a> </li>
            <li> <a href="clientlabels2.asp" title="Labels">Labels</a> </li>
            <li> <a href="clientlogos2.asp" title="Logos">Logos</a> </li>
            <li> <a href="clientpromo2.asp" title="Promo. Items">Promo.
                Items</a> </li>
            <li> <a href="clientnewsletters2.asp" title="Newsletters">Newsletters</a> </li>
            <li> <a href="clientstationary2.asp" title="Stationary">Stationary</a> </li>
            <li> <a href="clientwearables2.asp" title="Wearables">Wearables</a> </li>
          </ul>
          <br />
          <script type="text/javascript">
      <!--
    var obj_cssMenu2 = new CSSMenu("cssMenu2");
    obj_cssMenu2.setTimeouts(400, 200, 800);
    obj_cssMenu2.setSubMenuOffset(0, 0, 0, 0);
    obj_cssMenu2.setHighliteCurrent(true);
    obj_cssMenu2.setAnimation('slide');
    obj_cssMenu2.show();
   //-->
          </script>
        </div>
          <h3><span class="style1">Welcome <%= Session("kt_login_user") %> </span> <a href="loginupdate.asp" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image85','','../images/changeaccountdown.jpg',1)"><br>
            <br>
          </a><a href="loginupdate5.asp" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image7','','../images/changeaccountdown.jpg',1)"><img src="../images/changeaccountup.jpg" alt="change login info" name="Image7" width="130" height="26" border="0"></a><a href="loginupdate.asp" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image85','','../images/changeaccountdown.jpg',1)">  </a></h3>
          <form name="form1" method="post" action="">
            <label>Keywords
              <input name="keywords" type="text" id="keywords">
  </label>
            <label>
              <input type="submit" name="Submit" value="Search">
              </label>
            <br>
                  </form>
          <table width="300" border="1">
            <tr>
              <%
  ' Horizontal Looper version 3
  While ((Repeat_Recordset1__numRowsHL <> 0) AND (NOT Recordset1.EOF))
%>
                <td valign="baseline"><p><a href="<%=tNG_downloadDynamicFile("../", "../proofs/", "{Recordset1.proof_fileName}")%>"><img src="<%=tNG_showDynamicImage("../", "../thumbnails/", "{Recordset1.proof_thumb}")%>" /></a><br>
                  Date: <%=(Recordset1.Fields.Item("proof_date").Value)%><br>
                  Desc.: <%=(Recordset1.Fields.Item("proof_desc").Value)%><br>
                  Form #: <%=(Recordset1.Fields.Item("proof_formNumber").Value)%><br>
                  Keywords: <%=(Recordset1.Fields.Item("proof_keywords").Value)%><br>
                  <a href="emailassociate3.asp?IDNumber=<%=(Recordset1.Fields.Item("proof_ID").Value)%>&assoc=<%=(Recordset1.Fields.Item("proof_associate").Value)%>" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image75','','../images/emailassociatedown.jpg',1)"><img src="../images/emailassociateaboutjobup.jpg" alt="email associate about this job" width="190" height="26" border="0" align="absbottom"></a><a href="emailassociate2.asp?IDNumber=<%=(Recordset1.Fields.Item("proof_ID").Value)%>&assoc=<%=(Recordset1.Fields.Item("proof_associate").Value)%>" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image7','','../images/emailassociatedown.jpg',1)"></a><br>
                  <a href="emailartdept2.asp?IDNumber=<%=(Recordset1.Fields.Item("proof_ID").Value)%>" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image8','','../images/emailartdeptdown.jpg',1)"><img src="../images/emailartdeptup.jpg" alt="email art dept " name="Image8" width="190" height="26" border="0"></a><br>
                  <a href="reorder.asp?IDNumber=<%=(Recordset1.Fields.Item("proof_ID").Value)%>&assoc=<%=(Recordset1.Fields.Item("proof_associate").Value)%>">
                  <%
' Show IF Conditional region2
  If (Recordset1.Fields.Item("proof_reorder").Value) = 1 Then
%>
                    <img src="../images/ReorderButton.jpg" width="190" height="26" border="0">
                    <%
  End If
' End Conditional region2
%>
                  </a></p>
                    <%
' Show IF Conditional region1
  If IsNull((Recordset1.Fields.Item("cart_activate").Value)) Or (Recordset1.Fields.Item("cart_activate").Value) = "" Then
%>
                      <form name="eCart1_1_ATC_<%=Recordset1.Fields("proof_ID").value%>" method="POST" action="<%=cStr(Request.ServerVariables("SCRIPT_NAME"))%><%=WA_eCart_IIf((Request.ServerVariables("QUERY_STRING") <> ""), "?" & Request.ServerVariables("QUERY_STRING"), "")%>">
                        <input type="hidden" name="eCart1_1_ID_Add" value="<%=Recordset1.Fields("proof_ID").value%>" >
                        <select name="eCart1_1_Quantity_Add">
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity1").Value)%> selected><%=(Recordset1.Fields.Item("proof_quantity1").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity2").Value)%>><%=(Recordset1.Fields.Item("proof_quantity2").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity3").Value)%>><%=(Recordset1.Fields.Item("proof_quantity3").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity4").Value)%>><%=(Recordset1.Fields.Item("proof_quantity4").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity5").Value)%>><%=(Recordset1.Fields.Item("proof_quantity5").Value)%></option>
                        </select>
                        <input name="eCart1_1_ATC" type="image" value="Add to Cart" src="../WA_eCart/Images/Pacifica/Btn4_EN_addtocart.gif" align="middle" border="0">
                      </form>
                      <%
  End If
' End Conditional region1
%></td>
                <%
    'Horizontal Looper version 3
    Repeat_Recordset1__indexHL=Repeat_Recordset1__indexHL+1
    Repeat_Recordset1__numRowsHL=Repeat_Recordset1__numRowsHL-1
    Recordset1.MoveNext()
    if len(nested_Recordset1)<=0 then
      nested_Recordset1 = 1
    end if
    if ((Repeat_Recordset1__numRowsHL <> 0) AND (NOT Recordset1.EOF) AND (nested_Recordset1 mod 4 = 0)) then
      Response.Write "</tr><tr>"
    end if
    nested_Recordset1 = nested_Recordset1 + 1
    'end horizontal looper version 3
  Wend
%>
              </tr>
          </table>          <br>        </td>
        </tr>
    </table></td>
  </tr>
</table>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td valign="top"><font size="2" face="Arial, Helvetica, sans-serif"><br>
      </font><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><a href="http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash"><img src="../images/flashlogo.jpg" width="20" height="20"> Click
      here to download Flash</a><br>
            <br>
            <a href="http://adobeacrobat-8.com/index.asp?PID=02ea59e6-b795-4459-9c62-e26a4f2db084"><img src="../images/acrobatlogo.jpg" width="20" height="20"> Click
    here to download Acrobat Reader 8</a></font></td>
    <td align="right" valign="bottom"><font size="2" face="Arial, Helvetica, sans-serif"><a href="../loginsecure.asp" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image69','','../images/librarydown.jpg',1)"><br>
          <img src="../images/libraryup.jpg" name="Image69" width="200" height="36" border="0"><br>
           <br>
    </a><a href="filetransferclient.asp" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image6','','../images/Transfer-Logo.gif',0)"><img src="../images/Transfer-LogoUP.gif" name="Image6" width="200" height="80" border="0"></a><a href="../loginsecure.asp" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image58','','../images/librarydown.jpg',1)"><br>
    </a><br>
    <a href="login.asp"><img src="../images/copyright.gif" width="276" height="19" border="0"></a> </font></td>
  </tr>
</table>
<p>&nbsp;</p>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
<%
rsCSSMenu1.Close()
Set rsCSSMenu1 = Nothing
%>

Sorry for my late entry.  And also apologies if I've completely missed the mark here, but...

If you want *either* case to be NOT true then you should use OR instead of AND...

 <%
' Show IF Conditional region1
If Recordset1.Fields.Item("proof_shoppingcart").Value) = "Yes" OR (Recordset1.Fields.Item("cart_activate").Value = 1 Then
%>

I need both conditions to be true to display that region.  Proof_shoppingcart says that prices for that product have been entered.  Cart_activate says they paid to have a shopping cart so we've turned it on.  If both conditions are true the add to cart buttons will show.  What I don't understand is why if the second condition is false nothing at all shows in the looper.

Can you post just the malfunctioning code block so that I can look at it in isolation please?  I'm starting to grasp what you're saying but there's a lot of code on your page that isn't relevant to the question! :-)

Here is just the loop.  I need to account for nulls in the second condition (cart_activate) because it's a left joined table so many of the cart_activate values will be null.  I've played around with the Not IsNull condition but couldn't get it to work right.

Thank you,
Chris

<%
' Show IF Conditional region1
  If (Recordset1.Fields.Item("proof_shoppingcart").Value) = "Yes" And (Recordset1.Fields.Item("cart_activate").Value) = 1 Then
%>
                      <form name="eCart1_1_ATC_<%=Recordset1.Fields("proof_ID").value%>" method="POST" action="<%=cStr(Request.ServerVariables("SCRIPT_NAME"))%><%=WA_eCart_IIf((Request.ServerVariables("QUERY_STRING") <> ""), "?" & Request.ServerVariables("QUERY_STRING"), "")%>">
                        <input type="hidden" name="eCart1_1_ID_Add" value="<%=Recordset1.Fields("proof_ID").value%>" >
                        <select name="eCart1_1_Quantity_Add">
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity1").Value)%> selected><%=(Recordset1.Fields.Item("proof_quantity1").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity2").Value)%>><%=(Recordset1.Fields.Item("proof_quantity2").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity3").Value)%>><%=(Recordset1.Fields.Item("proof_quantity3").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity4").Value)%>><%=(Recordset1.Fields.Item("proof_quantity4").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity5").Value)%>><%=(Recordset1.Fields.Item("proof_quantity5").Value)%></option>
                        </select>
                        <input name="eCart1_1_ATC" type="image" value="Add to Cart" src="../WA_eCart/Images/Pacifica/Btn4_EN_addtocart.gif" align="middle" border="0">
                      </form>
                      <%
  End If
' End Conditional region1
%>

Rouchie, here is the SQL:

SELECT * FROM [proofs Query] WHERE proof_client = ? AND (proof_formNumber LIKE ? OR proof_date LIKE ? OR proof_desc LIKE ? OR proof_keywords LIKE ? OR proof_tab LIKE ?) ORDER BY proof_date DESC

I'm not sure why his logic is failing, but I think it has to do with the second clause of the SQL above and (as he says) null records.

Okay what data type are you using in the database for "proof_shoppingcart" and "cart_activate" ?

If you are using a YES/NO (bit) field the SQL will fail because ASP is comparing text for "proof_shoppingcart"

cart_activate is a number (long integer) and proof_shoppingcart is text.  I'm using drop down menus to change the values for both.

Your code snippet above is different to your original (full) code posting. I presume that's because you've been experimenting since the first post?!?

If you need to check for NULL values, then do it first, then check the values, like this:

IF NOT ISNULL(Recordset1.Fields.Item("cart_activate").Value) AND NOT ISNULL(Recordset1.Fields.Item("cart_activate").Value) THEN ' check for null first
    IF Recordset1.Fields.Item("proof_shoppingcart").Value = "Yes" AND Recordset1.Fields.Item("cart_activate").Value = 1 Then
        ' FORM CODE GOES HERE
    END IF
END IF

So first the IF statement checks that neither value is null.  If a NULL value is found then none of the code inside the IF block will execute.
Secondly the we check for the values "Yes" and 1.  If those aren't both present then again nothing inside will execute.  You can put an ELSE clause in there to double check your logic, like this:

IF NOT ISNULL(Recordset1.Fields.Item("cart_activate").Value) AND NOT ISNULL(Recordset1.Fields.Item("cart_activate").Value) THEN ' check for null first
    IF Recordset1.Fields.Item("proof_shoppingcart").Value = "Yes" AND Recordset1.Fields.Item("cart_activate").Value = 1 Then
        ' FORM CODE GOES HERE
    ELSE
        %>
        <p>Shopping cart not active!</p>
        <%
    END IF
END IF

Ok so here's my new if statement.  I've spent some more time testing and things are still acting crazy.  If cart_activate is null no records show. If cart_activate is set to 0 or 1 everything works fine.   If proof_shoppingcart is null everything still works fine as long as cart_activate is not null.

<%
' Show IF Conditional region1
  IF NOT ISNULL(Recordset1.Fields.Item("cart_activate").Value) AND NOT ISNULL(Recordset1.Fields.Item("proof_shoppingcart").Value) THEN
        If (Recordset1.Fields.Item("proof_shoppingcart").Value) = "Yes" And (Recordset1.Fields.Item("cart_activate").Value) = 1 Then
%>
                      <form name="eCart1_2_ATC_<%=Recordset1.Fields("proof_ID").value%>" method="POST" action="<%=cStr(Request.ServerVariables("SCRIPT_NAME"))%><%=WA_eCart_IIf((Request.ServerVariables("QUERY_STRING") <> ""), "?" & Request.ServerVariables("QUERY_STRING"), "")%>">
                        <input type="hidden" name="eCart1_2_ID_Add" value="<%=Recordset1.Fields("proof_ID").value%>" >
                        <select name="eCart1_2_Quantity_Add">
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity1").Value)%> selected><%=(Recordset1.Fields.Item("proof_quantity1").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity2").Value)%>><%=(Recordset1.Fields.Item("proof_quantity2").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity3").Value)%>><%=(Recordset1.Fields.Item("proof_quantity3").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity4").Value)%>><%=(Recordset1.Fields.Item("proof_quantity4").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity5").Value)%>><%=(Recordset1.Fields.Item("proof_quantity5").Value)%></option>
                        </select>
<input name="eCart1_2_ATC" type="image" value="Add to Cart" src="../WA_eCart/Images/Pacifica/Btn4_EN_addtocart.gif" align="middle" border="0">
                      </form>
                      <%
        End If
  End If
' End Conditional region1
%>

>> If cart_activate is null no records show. If cart_activate is set to 0 or 1 everything works fine.

What happens if you use the nz() function on cart_activate?

Here's what I tried.  Does this look right to you? It gave me the following error.

Microsoft VBScript runtime  error '800a000d'

Type mismatch: 'NZ'

/user/client3.asp, line 323

<%
' Show IF Conditional region1
        If NZ(Recordset1.Fields.Item("proof_shoppingcart").Value,"No") = "Yes" And NZ(Recordset1.Fields.Item("cart_activate").Value,0) = 1 Then
%>
                      <form name="eCart1_2_ATC_<%=Recordset1.Fields("proof_ID").value%>" method="POST" action="<%=cStr(Request.ServerVariables("SCRIPT_NAME"))%><%=WA_eCart_IIf((Request.ServerVariables("QUERY_STRING") <> ""), "?" & Request.ServerVariables("QUERY_STRING"), "")%>">
                        <input type="hidden" name="eCart1_2_ID_Add" value="<%=Recordset1.Fields("proof_ID").value%>" >
                        <select name="eCart1_2_Quantity_Add">
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity1").Value)%> selected><%=(Recordset1.Fields.Item("proof_quantity1").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity2").Value)%>><%=(Recordset1.Fields.Item("proof_quantity2").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity3").Value)%>><%=(Recordset1.Fields.Item("proof_quantity3").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity4").Value)%>><%=(Recordset1.Fields.Item("proof_quantity4").Value)%></option>
                          <option value=<%=(Recordset1.Fields.Item("proof_quantity5").Value)%>><%=(Recordset1.Fields.Item("proof_quantity5").Value)%></option>
                        </select>
<input name="eCart1_2_ATC" type="image" value="Add to Cart" src="../WA_eCart/Images/Pacifica/Btn4_EN_addtocart.gif" align="middle" border="0">
                      </form>
                      <%
  End If
' End Conditional region1
%>

No, it goes in the SQL, not the if

>> I've spent some more time testing and things are still acting crazy.  

Well it's Friday, and I like to make sure people can sleep over the weekend, so let's get cracking...!

>>If cart_activate is null no records show.
Yes this is correct regardless of the value of proof_shoppingcart

>>If cart_activate is set to 0 or 1 everything works fine.  
This is true as long as proof_shoppingcart also has a value.

>>If proof_shoppingcart is null everything still works fine as long as cart_activate is not null.
This is also true.

In your original post you said that both conditions had to be true for the form to show.  Is that not what is now happening?  If not please tell me what's still wrong and I'll amend the existing code.  Here is a revised version with custom error messages to help clarify what's getting passed from the database...

' Show IF Conditional region1
  IF NOT ISNULL(Recordset1.Fields.Item("cart_activate").Value) AND NOT ISNULL(Recordset1.Fields.Item("proof_shoppingcart").Value) THEN
        If (Recordset1.Fields.Item("proof_shoppingcart").Value) = "Yes" And (Recordset1.Fields.Item("cart_activate").Value) = 1 Then
                    %>
              <form name="eCart1_2_ATC_<%=Recordset1.Fields("proof_ID").value%>" method="POST" action="<%=cStr(Request.ServerVariables("SCRIPT_NAME"))%><%=WA_eCart_IIf((Request.ServerVariables("QUERY_STRING") <> ""), "?" & Request.ServerVariables("QUERY_STRING"), "")%>">
                <input type="hidden" name="eCart1_2_ID_Add" value="<%=Recordset1.Fields("proof_ID").value%>" >
                <select name="eCart1_2_Quantity_Add">
                  <option value=<%=(Recordset1.Fields.Item("proof_quantity1").Value)%> selected><%=(Recordset1.Fields.Item("proof_quantity1").Value)%></option>
                  <option value=<%=(Recordset1.Fields.Item("proof_quantity2").Value)%>><%=(Recordset1.Fields.Item("proof_quantity2").Value)%></option>
                  <option value=<%=(Recordset1.Fields.Item("proof_quantity3").Value)%>><%=(Recordset1.Fields.Item("proof_quantity3").Value)%></option>
                  <option value=<%=(Recordset1.Fields.Item("proof_quantity4").Value)%>><%=(Recordset1.Fields.Item("proof_quantity4").Value)%></option>
                  <option value=<%=(Recordset1.Fields.Item("proof_quantity5").Value)%>><%=(Recordset1.Fields.Item("proof_quantity5").Value)%></option>
                </select>
                        <input name="eCart1_2_ATC" type="image" value="Add to Cart" src="../WA_eCart/Images/Pacifica/Btn4_EN_addtocart.gif" align="middle" border="0">
              </form>
              <%
        Else
                   %><hr /><p>No form shown: proof_shoppingcart was not YES, or cart_activate was not 1</p><hr /><%
        End If
  Else
            %><hr /><p>No form shown: proof_shoppingcart was NULL, or cart_activate was NULL</p><hr /><%
  End If
' End Conditional region1

My if statement is supposed to control only my add to cart button.  All the records should always display in the looper but some will have add to cart buttons and some won't.  If both conditions are true the add to cart button will show.  The problem I'm encountering is that if cart_activate is null, which it usually will be since it's a left joined table, no records are shown in my looper.  The records should all still show they just shouldn't have the add to cart buttons.  I don't understand why no records are showing when only the add to cart button should be hidden.

IF you just want to hide the Add To Cart button but show the rest, then put the IF logic around the button and not the whole form.  So here I've moved the IF checks inside the form.  Therefore the form data still shows but the IF statement only controls the HTML that displays the button.

              <form name="eCart1_2_ATC_<%=Recordset1.Fields("proof_ID").value%>" method="POST" action="<%=cStr(Request.ServerVariables("SCRIPT_NAME"))%><%=WA_eCart_IIf((Request.ServerVariables("QUERY_STRING") <> ""), "?" & Request.ServerVariables("QUERY_STRING"), "")%>">
                <input type="hidden" name="eCart1_2_ID_Add" value="<%=Recordset1.Fields("proof_ID").value%>" >
                <select name="eCart1_2_Quantity_Add">
                  <option value=<%=(Recordset1.Fields.Item("proof_quantity1").Value)%> selected><%=(Recordset1.Fields.Item("proof_quantity1").Value)%></option>
                  <option value=<%=(Recordset1.Fields.Item("proof_quantity2").Value)%>><%=(Recordset1.Fields.Item("proof_quantity2").Value)%></option>
                  <option value=<%=(Recordset1.Fields.Item("proof_quantity3").Value)%>><%=(Recordset1.Fields.Item("proof_quantity3").Value)%></option>
                  <option value=<%=(Recordset1.Fields.Item("proof_quantity4").Value)%>><%=(Recordset1.Fields.Item("proof_quantity4").Value)%></option>
                  <option value=<%=(Recordset1.Fields.Item("proof_quantity5").Value)%>><%=(Recordset1.Fields.Item("proof_quantity5").Value)%></option>
                </select>
                <%
                ' Show IF Conditional region1
                        IF NOT ISNULL(Recordset1.Fields.Item("cart_activate").Value) AND NOT ISNULL(Recordset1.Fields.Item("proof_shoppingcart").Value) THEN
                              If (Recordset1.Fields.Item("proof_shoppingcart").Value) = "Yes" And (Recordset1.Fields.Item("cart_activate").Value) = 1 Then
                                    %>
                                    <input name="eCart1_2_ATC" type="image" value="Add to Cart" src="../WA_eCart/Images/Pacifica/Btn4_EN_addtocart.gif" align="middle" border="0">
                                    <%
                               End If
                        End If
                        ' End Conditional region1
                         %>
              </form>

I need the whole form to disapear because the drop down menu is also a part of the shopping cart feature.  I used your code and it doesn't do anything about the problem I'm having.  Please log into my website to see the problem I'm experiencing.  

Go to http://databardesign.com/loginsecure.asp and enter "test" for login and password.  This is exactly how it's supposed to work, and it does since cart_activate is not null in this case.  As you can see there are no shopping cart buttons for the first record since it's proof_shoppingcart value is not "Yes".

If you log in as test2 for the username and password you'll see the problem I'm having. This is what happens if cart_activate is null.  The same four records should still be there they just shouldn't have any shopping cart buttons.  Why do all the records disappear when only the shopping cart buttons are supposed to?

Unfortiontly, because of my left joined table, nearly all my cart_activate values are going to be null.

Where is the left join?  I can't find it in the SQL above.

Please excuse us for not getting this right straight away.  It's incredibly difficult to try and see the end result because we can't run the code without your full application being present on our machines.  Rest assurred we will fix this issue!

Now I've taken a look at the pages and I see that some products appear for TEST, but not for TEST2.  This has nothing to do with the form logic we have been working on, and must be coming from your SQL statement.  I can fix the AsP to hide the dropdown list no problem, but first let's get to the bottom of why your products aren't appearing.

Now sadly DW does a poor job of anything involving SQL statements, once you get beyond the basics.  I believe this could be one of the reasons why you are experiencing trouble, because you are no longer grabbing simple values, you are creating logical statement using multiple factors.  DW is not intelligent enough to understand what's happening.


The first job is to create the SQL statement as text and execute it directly within Access to find out what ASP is seeing under the hood.  So, underneath this line (approx line 56):


Recordset1_cmd.CommandText = "SELECT * FROM [proofs Query] WHERE proof_client = ? AND (proof_formNumber LIKE ? OR proof_date LIKE ? OR proof_desc LIKE ? OR proof_keywords LIKE ? OR proof_tab LIKE ?) ORDER BY proof_date DESC"


Add the following 3 lines:


Dim mySQL
mySQL = "SELECT * FROM [proofs Query] WHERE proof_client = " & Recordset1__MMColParam & " AND (proof_formNumber LIKE %" & Recordset1__sort & "% OR proof_date LIKE %" & Recordset1__sort2 & "% OR proof_desc LIKE %" & Recordset1__sort3 & "% OR proof_keywords LIKE %" & Recordset1__sort4 & "% OR proof_tab LIKE %" & Recordset1__sort5 & "%) ORDER BY proof_date DESC"
Response.Write("<hr/><span style=""font-weight:bold; color:red"">" & mySQL & "</span><hr/>")


You will now see 2 horizontal rules appear, and the SQL will be shown in bold red text.  Copy and paste that SQL statement into Access and run it, then tell me the result.  This will show us what ASP is passing to Access, and what Access returns to ASP to create RECORDSET1.  RECORDSET1 is the outer looper so once we are happy with the results here we can move to the next step.

It says 'Syntax error in query expression 'proof_client = 1 AND (proof_formNumber LIKE %%% OR proof_date LIKE %%% OR proof_desc LIKE %%% OR proof_keywords LIKE %%% OR proof_tab LIKE %%%)'.

Let me make sure I did this right.  I made a new querry in access.  Changed view to sql and pasted in your code.  Then ran it with the exclamation point button.  Did I miss anything?

To answer Jason's question, my left join is done in access.  I created relationships between the tables and then display them using a query.  I tried doing it in sql but DW doesn't like anything with the word join in it.

>> To answer Jason's question, my left join is done in access.

Ah-ha.  Can you post the SQL from that query here, please?

Here is my SQL from access

SELECT proofs.proof_ID, proofs.proof_date, proofs.proof_client, proofs.proof_desc, proofs.proof_formNumber, proofs.proof_AKA, proofs.proof_tab, proofs.proof_fileName, proofs.proof_thumb, proofs.proof_associate, proofs.proof_keywords, proofs.proof_sumNumber, proofs.proof_reorder, proofs.proof_shoppingcart, proofs.proof_quantity1, proofs.proof_quantity2, proofs.proof_quantity3, proofs.proof_quantity4, proofs.proof_quantity5, proofs.proof_price1, proofs.proof_price2, proofs.proof_price3, proofs.proof_price4, proofs.proof_price5, proofs.proof_addedshipping, proofs.proof_weight, proofs.proof_cartactive, cart.cart_ID, cart.cart_client, cart.cart_activate, cart.cart_reorder, proofs.proof_shipping1, proofs.proof_shipping2, proofs.proof_shipping3, proofs.proof_shipping4, proofs.proof_shipping5
FROM proofs LEFT JOIN cart ON proofs.proof_client=cart.cart_client;

Try this in Access and then see how things change in the web app:

SELECT proofs.proof_ID, proofs.proof_date, proofs.proof_client, proofs.proof_desc, proofs.proof_formNumber, proofs.proof_AKA, proofs.proof_tab, proofs.proof_fileName, proofs.proof_thumb, proofs.proof_associate, proofs.proof_keywords, proofs.proof_sumNumber, proofs.proof_reorder, proofs.proof_shoppingcart, proofs.proof_quantity1, proofs.proof_quantity2, proofs.proof_quantity3, proofs.proof_quantity4, proofs.proof_quantity5, proofs.proof_price1, proofs.proof_price2, proofs.proof_price3, proofs.proof_price4, proofs.proof_price5, proofs.proof_addedshipping, proofs.proof_weight, proofs.proof_cartactive, cart.cart_ID, cart.cart_client, nz(cart.cart_activate,0) AS cart-activate, cart.cart_reorder, proofs.proof_shipping1, proofs.proof_shipping2, proofs.proof_shipping3, proofs.proof_shipping4, proofs.proof_shipping5
FROM proofs LEFT JOIN cart ON proofs.proof_client=cart.cart_client;

The addition of the nz() function in Access should cause the Cart_activate nulls to come back as zeros instead.  If the problem in the ASP is the null checking, it won't happen anymore.

The downside is that you have to change references from cart_activate to cart-activate as the SELECT AS won't let you use the same reference (circular).

You are now finding out why I hate DW for doing database interrogation.  For some reason it creates each blank parameter, then assigns each one the value of "%" (a percent sign).  It then replaces the % sign with the real search value if one exists, but if it doesn't, it leaves the % sign, which causes the error in Access because that symbol is used for wildcard matching.

So currently you are presented with a problem whereby if no value is entered for searching, you get the wildcard %%% error.  You really need some better intelligence for constructing the SQL statement, but it will mean scrapping using DW to build the query, and having to work directly in Code View from this point on.
We could build the entire SQL statement using similar logic but miss out each part of the search if no value is entered.  It's just that DW wouldn't understand what is going on because it isn't in charge of creating the logic...

Let me know what you think

Alternatively before we do anything serious, can you make a copy of the file (in case this doesn't work), then alter the default character for each search value.   For each of the following lines of code take out the % and leave a blank instead, so:

Line 16:
   Recordset1__sort = "%"
becomes:
   Recordset1__sort = ""

and same for lines 23, 30, 37, 44.

The run the page again and execute the SQL in Access exactly as you did before.

>> You are now finding out why I hate DW for doing database interrogation.

They built it for CF/PHP/MySQL and added ASP/ASP.NET/Access integration afterwards.  The query logic works fine for the former, not so good for the latter.

Jason,

If I cut and paste your sql code into access I get the following error:

The SELECT statement includes a reserved word or an argument name is misspelled or missing, or the punctuation is incorrect.

If I take out the as cart-activate part it will save but probably doesn't do what you want it to.

Rouchie,

If I take out the %'s and run the resulting sql in access I recieve the following error:

Synatx error in query expression 'proof_client = Test AND (proof_formNumber LIKE %% OR proof_date LIKE %% OR proof_desc LIKE %% OR proof_keywords LIKE %% OR proof_tab LIKE %%)'

and it selects the first % it runs into.  I actually put the %%% in there on purpose because I thought the outside %'s meant it would find the keywords within strings and the middle % meant if no keywords were entered it would return every record.

I've been using this sql for some time now and I've never had a problem with it until I joined two tables and began working with queries.

>> If I take out the as cart-activate part it will save but probably doesn't do what you want it to.

Hmm.  Try it without the AS cart-activate in Access and see what it does.

Essentially, I'm doing this in Design View in Access:

In the Field row: cart-activate: nz([cart].[cart_activate],0)

That should return zeros in that column if the left join returns a null.

>> I thought the outside %'s meant it would find the keywords within strings and the middle %
>> meant if no keywords were entered it would return every record.

I believe this is the underlying source of the problem.  I don't use Access any more so I can't offer much advice I'm afraid.  Because of the way ASP constructs the SQL statement using ? signs, I don't think you can read out what the final SQL statement actual becomes.  Therefore you can't directly test in Access what ASP is passing to it, and therefore can't see if your query is working.

Now my angle of attack would be to scrap what DW is doing and manually construct the SQL statement.  If you do this then you get a cleaner statement that can be fired directly into Access.  Unless of course Jason, do you know if Access has any debugging tools that show what's being executed as it happens??

Again if Jason's suggestions don't work then this approach is a possible solution.  Just let us know.

>> do you know if Access has any debugging tools that show what's being executed as it happens??

You would have to use vbScript in a module to try, but a) Access is just not designed to do this and b) we are getting out of my depth.  I would have to ask my Access guy to take a look here.  It would be better for all involved to move to a pure SQL environment.

>> Synatx error in query expression 'proof_client = Test AND (proof_formNumber LIKE %% OR proof_date LIKE %% OR
>> proof_desc LIKE %% OR proof_keywords LIKE %% OR proof_tab LIKE %%)'

The % wildcard is SQL only.  For Access SQL, replace all % wildcards with *.  The statement above should look like this:

proof_client = Test AND (proof_formNumber LIKE ** OR proof_date LIKE ** OR proof_desc LIKE ** OR proof_keywords LIKE ** OR proof_tab LIKE **)

Even so, this still may not pass Access validation as there is no way to match LIKE **