Problem showing only non-active records

[Cole3c]

I have (besides being a moron) a problem showing only the selected records that are not active (ie:still pending)
The select works and gives me the records that are pending and completed (active = 1). orderdetail.active is bit, default=0, null allowed

...clip
& "WHERE ordersubjects.CompID = '" & Session("CompID") &"' and orderdetail.archive = 0 and ordersubjects.subjectnumber = " & ID & " "_
...clip

But when I do this:

<% while objrst.eof = false
If Orderdetail.active < 1 then
%>
<table .....
<tr><td>......
<%
objrst.movenext
end if
wend
ObjRst.movefirst
%>
Thus far, no matter the syntax of my if statement, I get all the records selected.

suggestions?

TIA

Cole

 

[DotNetGnat]

what are you trying to do with this if condition...

If Orderdetail.active < 1 then

you mentioned that your field active is bit...so you can directly say

If OrderDetail.active then

which mean if OrderDetail.active is true or 1...

-DNG

 

[Cole3c]

I tried
if orderdetail.active = false then

and got all records

if not orderdetail.active then

and got all records

if orderdetail.active < 1 then

and got all records

if orderdetail.active = 0 then

and got all records

I am doing something wrong!

 

[DotNetGnat]

can you show your complete query...

also do a response.write and try to run the actual query in query analyzer...

-DNG

 

[Cole3c]

its not the query. The query gives me all 3 records. 2 not active and 1 active. I want to show just the non-active records in the display, show some other static text, and then start showing the active records.

But until I can get the orderdetail.active to determine whether or not to display, I'm sunk.

the full query:

objRst.Source = "SELECT ordersubjects.CompID, ordersubjects.DateRequested AS DATEREQUESTED, ordersubjects.sex, ordersubjects.address, ordersubjects.ClientRef, " _
& "ordersubjects.Fname, ordersubjects.Lname, ordersubjects.Mname, ordersubjects.SSN, ordersubjects.DOB, "_
& "orderdetail.OrderID AS ORDERID, orderdetail.ServiceNumber, orderdetail.active, orderdetail.ClientID AS ClientID, "_
& "CrimCivDetail.State AS CrimState, CrimCivDetail.County AS CrimCounty, services.ServiceCode, "_
& "services.Dropdown, services.ReportOrder, MvrDetail.State AS MVRState, MVRDetail.Dlnumber AS DLnumber, "_
& "Orderdetail.Datefilled, Orderdetail.results "_
& "FROM ((ordersubjects INNER JOIN (orderdetail LEFT JOIN CrimCivDetail ON orderdetail.OrderID = "_
& "CrimCivDetail.OrderID) ON ordersubjects.SubjectNumber = orderdetail.SubjectNumber) "_
& "INNER JOIN services ON orderdetail.ServiceNumber = services.ServiceID) LEFT JOIN MVRDetail "_
& "ON orderdetail.OrderID = MVRDetail.OrderID "_
& "WHERE ordersubjects.CompID = '" & Session("CompID") &"' and orderdetail.archive = 0 and ordersubjects.subjectnumber = " & ID & " "_
& "ORDER BY ordersubjects.ClientRef,ordersubjects.Lname, ordersubjects.Fname, orderdetail.active, services.ReportOrder"
'& " and Orderdetail.Datefilled >= '" & NDaysAgo & "' "_

yada, yada, yada

<% while objrst.eof = false
if orderdetail.active = false then
%>

yada, yada, yada
The results

FEL/MISD pima AZ PENDING 8/23/2005 4:05:22 PM
MVR 2781gh CO PENDING 8/23/2005 4:05:22 PM
SPECIAL PENDING 8/23/2005 4:05:22 PM


and the last record is active (I've checked directly in the DB, and with query analyzer.