• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

Don't Load/Show Null or 0 value in recordset

For:
ASP Classic / MS Access Database

This is what I have now when I load the database:
Recordset1.Source = "SELECT * FROM Detail WHERE CardNum='1621706607' AND AdvALREADY_CHRGED='1'"

Now when I run that I get alot of returns in a record named: "AdvAmt" that show up as 0's". I don't want them to show up at all.

I know this is wrong but something like this:
Recordset1.Source = "SELECT * FROM Detail WHERE CardNum='1621706607' AND AdvALREADY_CHRGED='1' AND IF NOT AdvAmt='0' " // Like AdvAmt=!'0' "

Thanks for any help....
0
CompCobalt
Asked:
CompCobalt
  • 4
  • 2
1 Solution
 
Bill PrewCommented:
Not sure exactly what you are after, but is it something like this?  If not, then can you share the layout of the Detail table.

Recordset1.Source = "SELECT * FROM Detail WHERE CardNum='1621706607' AND AdvALREADY_CHRGED='1' AND AdvAmt<>'0' "

this assumes AdvAmt is a character column, not numeric.  If numeric then typicall would be:

Recordset1.Source = "SELECT * FROM Detail WHERE CardNum='1621706607' AND AdvALREADY_CHRGED='1' AND AdvAmt<>0"

lastly, if there are nulls as well as zeroes in that column, then perhaps something like this:

Recordset1.Source = "SELECT * FROM Detail WHERE CardNum='1621706607' AND AdvALREADY_CHRGED='1' AND AdvAmt<>'0' AND advAmt IS NOT NULL"

~bp
0
 
CompCobaltAuthor Commented:
That did it! Thanks!
0
 
CompCobaltAuthor Commented:
How on to question number too...
I added:
Recordset1.Source = "SELECT * FROM Detail WHERE CardNum='1621706607' AND AdvALREADY_CHRGED='1' AND AdvAmt<>0"

And it works, now if the page is empty and no records are found. how do i redirect them to a diffrent page ??? Do u want me to open a new question ?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
CompCobaltAuthor Commented:
Nevermind i got it, here is the code:


<%
If (Recordset1.RecordCount > 0) Then
    Response.Write("Record(s) are in the RecordSet")
Else
    //Response.Write("RecordSet in empty")
 		Response.Redirect("norecords.asp")
End If
%>

Open in new window

0
 
CompCobaltAuthor Commented:
Sorry remove the //Response.Write("RecordSet in empty")
and change it to 'Response.Write("RecordSet in empty")
It's a comment... :-)
0
 
Bill PrewCommented:
Okay, glad my original response was helpful.  Thanks.

~bp
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now