Exclude from SQL query

What type of field is pdate?  If Numeric, don't use quotes.

Also, I would use Raynard's suggestion with the void field.  Using LIKE (or NOT LIKE) and not including wildcard characters is kinda pointless.

the pdate part works fine, it is when I add on the voided part, that causes the problems

Raynard7, that does not give any errors, it just returns nothing.

When you say void - do you mean void or is it null?
ie

where pdate="0" and voided is null

can you run

SELECT count(*) FROM Receiptinfo WHERE pdate="0" And voided <> "VOID";


does this at least return a number?

no the value needs to be "VOID"  This is a receipt program, and I am using this so the user can void receipts, but I still want the receipt to show, I just do not want the values counted when everything is added up.

SELECT count(*) FROM Receiptinfo WHERE pdate="0" And voided <> "VOID";

This returns a "0"

I understand that you do not want a count.

I'm trying to work out why you are getting "nothing"

generally you should either get an error or get something - even if that is not what you expect.

count queries should always return a value - even if it is 0 because nothing meets the criteria.

If your field is a string field and it is indeed "VOID" then your criteria looks good - now you may be trying to sum something that can not be summed or there could be a problem with how you are running the query - if you run count it at least helps me (and potentially others) find what exactly your problem is.

And yes, there are rows in the database that match these search criteria.

how do you know they meet the exact criteria?

Before I added the VOID column to the database, I was using the following to add up the total column, and it worked fine.

SELECT Sum(total)
FROM Receiptinfo
WHERE pdate="0"

the pdate column has a value of "0" and the voided column does not have a value of "VOID"

if you do

SELECT Sum(total)
FROM Receiptinfo
WHERE pdate="0" or voided = "VOID"

do you get a total?

FYI if I change the <> to an = sign, it accurately counts 4 records

yes i do

That is telling me that your criteria is not working.

If changing the sign works then it means that the query is valid - it just means that perhaps you do not have anything in the table that is not "VOID"

even if I take the pdate part out, it still finds nothing.  I even tried deleteing all the void values that I put in, so there was absolutely no rows in the db with a "void" value in the voided column, and still nothing.  I think the problem is in the <>

I tried changing the void to a 1, and just using that, still nothing when i use <>

<> means not equal - you are using access so this is the syntax

!= is the sql server syntax for not equal

The other thing that you may try is

instead of

<> "VOID"

try

NOT IN ("VOID")

Nope, same problem, I even erased the old query and made a new one, since I added the voided column recently, still nothing.

There must be something going on that I do not understand.  This is pretty basic SQL and access generally has a pretty good time with stuff like this.

I've never heard of the sql engine being wrong.

can you create a new table (newTable)
and have two columns:

val1 - being a number field
val2 - being a string (text) field.

can you then insert the following data

val1  val2
=======
1      VOID
2               <----- Just an empty string
3      NOT VOID
5      VOID

can you then run the query

select sum(val1) from tableName where val2 <> "VOID"

This should return the value of 5

and then run

select sum(val1) from tableName where val2 = "VOID"

which should return the value of 6

This works with my version of access 2003.

Ok, I tested it with a different field, Like i have a field called "company"  Most of the fields values are "Nationwide" So I did company<>("Nationwide") and it worked.....so it is only doing this for this new column.....

I know, this should be simple....but I cant figure out why it will not work, I even deleted the "voided" column, and remade it, same thing...

I dont understand either

I even copied and pasted the "firstname" column which I knew works, and renamed it "voided" and it works as firstname, but not after I paste it.

maybe it is a naming issue. I'm not aware or voided being a reserved word but if you change in your query to

[tableName].[voided]

does this work??

Ok what if I try to do this query right in the vb code? I need it to appear in textbox1, but this code doesnt work.

Dim Da As OleDb.OleDbDataAdapter

        Try
            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/backup/receiptprogram/receiptmanagement/receipt.mdb"
            con.Open()
            Cmd.Connection = con
        Catch ex As Exception
        End Try
        Try
            JournalDataSet.Clear()
            Cmd.CommandText = "SELECT sum(total) FROM Receiptinfo WHERE pdate = "0" And voided<>"VOID" Cmd.CommandType = CommandType.Text
            Da = New OleDb.OleDbDataAdapter(Cmd)
            TextBox1.DataSource = JournalDataSet.Receiptinfo
            Da.Fill(JournalDataSet.Receiptinfo)

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
            Cmd.Connection = Nothing
        End Try

No, i thought of that earlier and changed the column to firstname1 and the values to 1 instead of "VOID" and it still did not work.

Now, if I take the firstname column, and just rename it to voided, and change a few of the values to VOID...IT WORKS!!!, so why, if I creat a new column named "voided" is it not working????

I really do not know - I'd suggest that you put a pointer question in the Ms Accesss area and see if anyone has seen this before.  It is very bizarre behaviour.

If I change the data type to Yes/No and say Where pdate="0" And voided<>yes then that works too..... But the whole reason I wanted it to say VOID, was so I could display that in search results in the program.

But If I change the data type back to text, and use Yes and No, it doesnt work.

If you have a yes no you could just change your queries that refer to it by having

iif(voided = true, "VOID", "NOT VOID")

which would display void if it was true (yes) and "NOT VOID" if it is not void.

where would i put that?

<<But the whole reason I wanted it to say VOID, was so I could display that in search results in the program.

where you want to display this in the search results - put the above code instead of just the field name.  

The search was in an access query too, I am trying to bring it to the vb code... It says data type mismatch

Try
            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/backup/receiptprogram/receiptmanagement/receipt.mdb"
            con.Open()
            Cmd.Connection = con
        Catch ex As Exception
        End Try
        Try
            PdateDataSet1.Clear()
            Cmd.CommandText = "SELECT ReceiptNo, PolicyNo, firstname, lastname, pmt1, type1, pmt2, type2, pmt3, type3, total, company FROM Receiptinfo WHERE pdate='0'"

            Cmd.CommandType = CommandType.Text
            Da = New OleDb.OleDbDataAdapter(Cmd)
            DataGridView1.DataSource = PdateDataSet1.Receiptinfo
            Da.Fill(PdateDataSet1.Receiptinfo)

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
            Cmd.Connection = Nothing
        End Try

Ok, this code is working, now where do I enter that line of code?

Try
            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/backup/receiptprogram/receiptmanagement/receipt.mdb"
            con.Open()
            Cmd.Connection = con
        Catch ex As Exception
        End Try
        Try
            PdateDataSet1.Clear()
            Cmd.CommandText = "SELECT ReceiptNo, PolicyNo, firstname, lastname, pmt1, type1, pmt2, type2, pmt3, type3, total, company FROM Receiptinfo WHERE pdate='0'"

            Cmd.CommandType = CommandType.Text
            Da = New OleDb.OleDbDataAdapter(Cmd)
            DataGridView1.DataSource = PdateDataSet1.Receiptinfo
            Da.Fill(PdateDataSet1.Receiptinfo)

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
            Cmd.Connection = Nothing
        End Try

Have you checked for case sensivity? I.e. is 'VOID' actually 'void' or 'Void'?

Debug print all the rows in the "voided" column and see what you get.

Cmd.CommandText = "SELECT ReceiptNo, PolicyNo, firstname, lastname, pmt1, type1, pmt2, type2, pmt3, type3, total, company, iif(voided = true, 'VOID', 'NOT VOID') FROM Receiptinfo WHERE pdate='0'"

would display if it is void or not

what do you get with this:

SELECT voided, count(*) FROM Receiptinfo WHERE pdate="0" Group By voided;

AW

That was it Sancler, thank you.  I was going crazy over here.....I will split points as Raynard7 gave me the sql query to enter, and you helped make it work.  Thanks to both of you.