Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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