Posted on 2005-05-10
Last Modified: 2010-05-02

I have a field named dDate which has a datetime datatype in my table. How am i gonna insert an empty value (not null) in this field. Any function to do this? Tnx


Question by:zack_online
    LVL 8

    Expert Comment

    there is no way to do that. it must be null or a valid date.
    LVL 8

    Assisted Solution

    if you must have an empty value you will need to change the data type to a char or varchar then use ''. if you do this you will loose the ability to compare dates in that field efficiently. I would leave it as a datetime data type and just allow nulls, put a null in the field.
    LVL 18

    Accepted Solution

    You can insert a value 0 that will be internally converted into a date by SQL. However, I am not sure whether you can read it as zero because SQL will return a value like 01-01-1900 or something.
    LVL 18

    Expert Comment

    If IsEmpty(dDate) Then
        myField= vbNull
        myField= dDate
    End If
    LVL 6

    Assisted Solution

    I absolutely agree with wraith. Let the field accept Null values. Besides you can't get any emptier than Null.

    Sethi's suggestion can work too. He's correct too that 0 gives a 01-01-1900 datetime value. Next time you do a SELECT, all with dates 01-01-1900 are to be considred empty which means probably additional code for checking.

    Now the other DUDE's suggestion makes no sense at all.
    LVL 18

    Expert Comment


    Author Comment

    Thanks guys.  Expert always gimme incredible and unexpected answer that's why i tried asking this kind of question even though i tried it to myself. I juss wanna clear my doubt regarding this from u guys.

    Field1 varchar(50)

    Field1 = null = 50
    Field1 = empty = 1
    Is it true that if Field1 is null it consume the max 50 and if it is an empty string only 1 byte?

    - zack
    LVL 20

    Assisted Solution

    >>> Is it true that if Field1 is null it consume the max 50 and if it is an empty string only 1 byte? <<<

    Not true for SQL Server... unsure about other db's, though.

    In SQL Server, nullable columns have a "null map" for each row that requires, I thnik, 1 bit per nullable column.  For each nullable column, its corresponding bit in the map is either on or off to indicate whether that column does or does not contain real data.

    Depending on a configuration option in SQL Server (ANSI_PADDING), varchar columns may or may not be expanded to their fully defined size when only partially occupied... this has to do with whether trailing spaces are automatically truncated or added.

    If a varchar column is used in an index expression, then for that index (and the space it requires) the column is considered to be fully expanded with trailing spaces (in the index area only).

    The book, Inside SQL Server by Kalen Delaney, explains this (and dozens of other intricacies about the internals of SQL Server) in extreme detail if you're interested.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now