null or ""

Hi Experts,
I would like to know the difference between null or "" 
for example:  should BDDate = "" or BDDate = null
                         date field or numeric field should use "" or should use Null
                         Text field should use Null not ""
am I correct?

any help would be very appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MouseCaptainHelp Desk AnalystCommented:
Null means the field does not exist for this dataset.
"" means the field exists but is empty.

Are you composing criteria for a query?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Null means no data exists for a given Field in a Table.
The data is unknown.

"" is a Empty Length String that has not been initialized.

Dim X as String
at this point. X="" ... which IS valid data.

Now it's initialized
urjudoAuthor Commented:
I tried to do the coding.  
If PDState = "Y" then
   PDUser = ""
end if

when I ran the query if PDUser is not null, one of the case shows in the query, but the PDUser is empty, so I changed to
If PDState = "Y" then
   PDUser = Null
end if
when I ran the same query, the query ran correctly.

that's why I wonder if I should use Null for datefield and text field or I shohld use "" for datefield and Null for text field.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I AVOID Empty Length Strings at all costs as Table Field values  because visually ... you cannot tell a Null from a ELS.

You can use this test

IF Nz(x,"")=""  Then  ' whatever


IF Nx(x,"") > "" Then ' whatever
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"if I should use Null for datefield and text field or I shohld use "" for datefield and Null for text field."

You should use Null for Both ... then you only have to worry about testing like this .... IsNull (SomeField)
urjudoAuthor Commented:
so I should never use "" in coding or I can use "" for if the field is a number field (like 12345)?
In the table definition you can set the AllowZeroLengthStrings property to No.  That will prevent you from saving a "".  Notice the name - Zero Length STRING.  As the name implies it is only valid for string data types in tables.  You can't set a numeric data type to "" so
MyDate = "" 
will raise an error.

When working with variables defined in code, keep in mind that VBA is a strongly typed language and if you say something is a string, it will not allow it to be null.  So, the only time you might run into a ZLS in code is if you need to clear out a variable.  If you want to allow nulls, you must use a variant data type.

In general, I never allow ZLS in string fields in tables but you may have to deal with them when working with controls on a form because controls are variants.  If a user types something into a control and wants to clear it, they might backspace rather than selecting the entire value and pressing the delete key.  Using the delete key will set the value to null but the backspace will make it a ZLS.  So, there are a couple of ways to simplify your code when you need to make sure a field isn't "empty"

If Me.SomeField & "" = "" Then
    Msgbox "this field may not be empty", vbOKOnly
    Cancel = True
    Exit Sub
End If

So concatenating a ZLS to a control that is null results in a ZLS so whether the field started out as null and you made it a zls or started out as a ZLS, all you need to test for is a ZLS.

One thing you have to consider when setting defaults of numeric fields to null (foreign keys should ALWAYS default to null) is how are you going to handle them when doing arithmetic.  For example, if you have an OrderTotal and a ShippingFee and you want to add them for a TotalDue.  
OrderTotal + ShippingFee would result in null if there is no shipping fee so in the query you would need:
OrderTotal + Nz(ShippingFee, 0) to get an actual total.  The Nz() (null to zero) function converts the shippingFee to 0 (or what ever value you used) if it is null.

So, perhaps you want to default ShippingFee to 0 to avoid having to use the Nz() function.  If you do that, you should make the ShippingFee required to prevent it from being null by accident.

But, then you need to consider, might you ever want an average of all shippingfees, if so, are you looking for an average of shippingFees in and of themselves or an average of the shippingFees across all orders.  Consider
Avg(45, null, 25) = 30
Avg(45, 0, 25) = 20

In my world, I always use null as the default for numeric fields because if you go with a 0 default, you can never go back because you can't tell which 0's were entered as defaults and which were entered as known values by users.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"so I should never use "" in coding or I can use "" for if the field is a number field (like 12345)?"

Well, there will likely be case wherein you want to initialize (re-initialize) a String variable to "" (reset it of sorts).

Dim X As String
'more code
'more code
X="" '
 and so on.  This might typically happen at the beginning of a For / Next Loop or other places.

As noted above, you cannot set a Numeric Data Type  (Byte, Integer, Long Integer etc)  to "", only a String/Text Data Type.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
We also use the technique Pat mentioned:

If Me.SomeField & "" = "" Then

It's short and easy to read, and it covers both Null and ZLS just to be safe.  But in practice we don't allow ZLS to be stored in tables.
urjudoAuthor Commented:
Thank you!!  I wish I can give you all 500 points each.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.