Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / May 2006

Tip: Looking for answers? Try searching our database.

Delete records

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ron - 27 May 2006 15:16 GMT
Hi,
It may be  trivial.

What is the right syntax in SQL to delete all records from " mytable" where
myfield is empty ?

What is the differance between null and empty string?

Thanks,
            Ron
Allen Browne - 27 May 2006 15:38 GMT
If no value has been entered into a field and there is no default value set,
the value is Null.

You can therefore delete those records with:
   DELETE FROM MyTable WHERE MyField Is Null;

A zero-length string (ZLS) is not the same as a Null.
Think of Null as meaning unknown, or not applicable.
For example, you leave the Phone field null if you don't know someone's
phone number.

But what if you know the person has no phone?
That data could be represented as a ZLS.
You can now create a query for all the people who have no phone by entering
the criteria:
   ""
This does not return the people whose phone number is unknown. To query for
them, the criteria would be:
   Is Null

In practice, a user looking at the data cannot see any difference between a
Null and a ZLS. For that reason, it is rarely a good idea to use a ZLS in a
database. In the last 5 years, I can only recall one scenario where I have
implemented a ZLS, because it is just too confusing for users.

Even people who create databases sometimes get confused about how to use
nulls, even though they are absolutely essentially, and incredibly useful.
this might help:
   Common Errors with Null
at:
   http://allenbrowne.com/casu-12.html

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi,
> It may be  trivial.
[quoted text clipped - 6 lines]
> Thanks,
>             Ron
Michel Walsh - 29 May 2006 12:38 GMT
Hi,

   Not all database engine allows a string with no character in it (a ZLS).
Joe Celko pointed out that the standard defines a string as a sequence of
one or more characters (form memory, and I didn't verify that affirmation,
believing Mr. Celko on his words). So, to add to the confusion, some data
source will then, internally, represent a null as a ZLS. A typical relevant
example could be Excel, which, while not being a database, may have supplied
data with a ZLS where it should have been a Null. To cover you against that
kind of problem, you can test:

...    WHERE  0=len(myField & "" )

since then, a NULL concatenated with & to a zero character string will
result into a string with a length of 0, and also would do a ZLS
concatenated to another ZLS.

On the other hand, that comparison won't use any index, and it is really
preferable to make it after all other criteria which can use any index.

Hoping it may help,
Vanderghast, Access MVP

> If no value has been entered into a field and there is no default value
> set, the value is Null.
[quoted text clipped - 38 lines]
>> Thanks,
>>             Ron
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.