Solved SQL problem with 'WHERE a AND b' when b is null

[Brother Arnold]

Hi,

I've spent far too long trying to get this to work and googling on line for an answer so it's your turn if you will to confirm why I'm feeling stupid at this moment and put me out of my misery.

So, in my inexperienced Access mind the following statement looks pretty simple and should work but it doesn't:

SELECT sum(payment_amount) AS total_paid
FROM tblPayment
WHERE (payment_type<>'Voluntary Contribution' AND client_ID=100);

The total_paid comes back as 0 but it should be £280. Payment_type is not mandatory and in a lot of cases is often left blank. Client 100 has null payment_type in all its payment records but if I change the payment_type in one of the records to 'Cash' for example then the payment_amount for that record is included in the sum.

So the solution seems to be the following statement:

SELECT sum(payment_amount) AS total_paid
FROM tblPayment
WHERE (payment_type<>'Voluntary Contribution' OR (ISNULL(payment_type))) AND client_ID=100

Is that right? Can anyone explain why null values cause this issue please?

 

[Minty]

The use of <> will exclude Nulls because nothing equals a Null.

You can prove this in the immediate window, you would expect evaluating (Null = Null) to be true but it isn't :

? (1 = 1)
True
? (Null = Null)
Null

This proves that there is nothing that equals a null, and is why you need to ask ISNULL() in a very specific way.

 

[ebs17]

Write SQL:
ISNULL(payment_type)
payment_type Is Null

 

[Pat Hartman]

Perhaps it is time to add a default for Payment_type since it is actually not optional.

 

[ebs17]

NULL stands for unknown, and with good reason. If you enter something as a standard value just so that the field is not empty, this can greatly falsify the content of the statement.
It makes a difference whether your sponsor's net worth is unknown or 0.

 

[Pat Hartman]

I am not suggesting that everything needs nor should have a default. I am very aware that 0 has a meaning. But, some fields should be required. Payment_Type is one of them. When you receive the payment, you know the type so it should be entered. To minimize data entry, you might decide to provide a default value if the majority of values will be a specific type.

 

[Brother Arnold]

I am not suggesting that everything needs nor should have a default. I am very aware that 0 has a meaning. But, some fields should be required. Payment_Type is one of them. When you receive the payment, you know the type so it should be entered. To minimize data entry, you might decide to provide a default value if the majority of values will be a specific type.

Originally it wasn't mandatory but as soon as I got my hands on the database I made it mandatory so sorry I wasn't clearer there. I've got over 20 years of data which would have provided some excellent analysis if the original developer had made fields mandatory and written some rules

 

[Pat Hartman]

How did you make it mandatory? You need to find all the null or ZLS values and provide update them with the default unless there is some other way to identify what the value might be. Then set the field as required at the table level.

"Blank" has no meaning. If the field is numeric, it might be null but if it is text, it could be either Null or a ZLS. You will have to deal with both. Also, you need to change the allowZLS property to No. Otherwise, there is no point in making the field required.

 

[Brother Arnold]

How did you make it mandatory? You need to find all the null or ZLS values and provide update them with the default unless there is some other way to identify what the value might be. Then set the field as required at the table level.

"Blank" has no meaning. If the field is numeric, it might be null but if it is text, it could be either Null or a ZLS. You will have to deal with both. Also, you need to change the allowZLS property to No. Otherwise, there is no point in making the field required.

Sorry, not being clear again. I built rules into the form. I'm relatively new to Access and I didn't step back quick enough when they asked someone to volunteer to maintain it.

 

[Pat Hartman]

Ok, that's why you still have the problem caused by your predecessor. You put a bandaid on the problem. You didn't actually fix it. You might try what I suggested and start by fixing the data. That allows you to ultimately fix the problem permanently.