Null value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Invoice Date which has default value = Null and Allow Size Length = Yes.
When the record is created, I move Null to this field: InvoiceDate = Null.
When I use DCount on this field, DCount always returns 0
Please advise how to test
 
Joshua said:
I have a Invoice Date which has default value = Null and Allow Size Length = Yes.
When the record is created, I move Null to this field: InvoiceDate = Null.
When I use DCount on this field, DCount always returns 0.
Please advise how to test.
Click to expand...

Null is not zero and not even nothing.
In general it is not a good idea to have a field *ever* set to null and I
would think an invoice date should always have a value.
You could use the NZ function but dcount will not count nulls.

If the date fields is important set it to a far distant or far past date.
If not use a text field and set it to "Not assigned" or something.
 
I have a Invoice Date which has default value = Null and Allow Size Length = Yes.
When the record is created, I move Null to this field: InvoiceDate = Null.
When I use DCount on this field, DCount always returns 0.
Please advise how to test.
Click to expand...

It's not necessary to set the Default to NULL - that's the "default
default" already. I assume that by "allow size length" you mean "allow
zero length"; that applies only to text fields, not date fields, and
should be left at No.

NULL is a funny beast. It means "this value is undefined, unknown; it
could be anything". Therefore ANY logical expression involving NULL
returns NULL; [Fieldname] = NULL or [Fieldname] <> NULL are neither
true nor false, they are NULL.

The correct syntax would be

DCount("*", "[tablename]", "[Invoice Date] IS NULL")

or [Invoice Date] IS NOT NULL to return all records where there is a
date entered.
 
I set the invoice date field to a text field so that I can move the date and time to it when the invoice is printed. However before the invoice is printed, I move NULL to this field. I did try your suggest but DCount does return 0 to me. It seems DCount does not recongize NULL. Any other suggestion?
 
I set the invoice date field to a text field so that I can move the date and time to it when the invoice is printed. However before the invoice is printed, I move NULL to this field. I did try your suggest but DCount does return 0 to me. It seems DCount does not recongize NULL. Any other suggestion?
Click to expand...

Please post your actual DCount expression, and indicate how you're
"moving" NULL to the field. Unless you set the field to some value
it's ALREADY null in a new record!
 
Hi John

In the database, the Invoice Date is defined as text and allow zero length = yes. And also I set the default to NULL too
At the time of creating the record, I move null and the code is "InvoicePrintedOn = Null"
When I try to test if any un-printed invoices, I use DCount and the code is as following
If 0 = DCount("[OrderNumber]", "qryOrder", "[InvoicePrintedOn] = Null") Then ....
The return result is always 0. That means all invoices have been printed which is not true
Please advise what I have done wrong.
 
In the database, the Invoice Date is defined as text and allow zero length = yes. And also I set the default to NULL too.
Click to expand...

These are CONTRADICTORY. Allow zero length lets you put a "" - an
empty string - in the field. It LOOKS the same as a zero length string
but IT IS DIFFERENT.
At the time of creating the record, I move null and the code is "InvoicePrintedOn = Null".
Click to expand...

Why?

When the record is created, the field contains NULL. It ALREADY
CONTAINS NULL. Setting it to NULL accomplishes nothing.
When I try to test if any un-printed invoices, I use DCount and the code is as following:
If 0 = DCount("[OrderNumber]", "qryOrder", "[InvoicePrintedOn] = Null") Then .....
The return result is always 0. That means all invoices have been printed which is not true.
Please advise what I have done wrong.
Click to expand...

For one thing, you've ignored my previous message. As I said there,
the expression

[InvoicePrintedOn] = Null

has a value of NULL. It's not true; it's not false; it's NULL. The
effect is that it's treated as being false.

The correct expression would be

DCount("[OrderNumber]", "qryOrder", "[InvoicePrintedOn] IS Null")

The IS NULL operator (or its dual, IS NOT NULL) is the *only* way to
get a TRUE or FALSE result with NULL.
 
Hi John, thanks for your advise. These are the steps I have just amended
1. Change the Allow Zero Length of InvoicePrintedOn to N
2. Change my test to InvoicePrintedOn IS Nul
3. Because my previous test was not working properly, so actually I move 0 to initialize the InvoicePrintedOn, I deleted the zero of each record
4. I create a new record using after changing the field control (after step 1
5. I run my test again, this time the DCount return all records to me. That means DCount says none of the order has invoice printed. But actually there are 2 orders have invoiced. These 2 orders have the invoice date (e.g 29/03/2004) stored in InvoicePrintedOn

Please kindly advise again.
 
Hi John, finally I got the DCount problem solved according to your advise. Thanks again
But I got stuck in another test method of the same. The code is
If (Me!OrderSummary.Column(5) Is Null) Then ...
This time the InvoicePrintedOn appears on list box and Column(5) is the said InvoicePrintedOn. I got a runtime error '424' Object required

Appreciate your further advise.
 
Back
Top