I was aware of the date vs time format. But since you raise it, your
comment that .75 is 6:00 pm and is good between dates 1/1/100 and
31/12/9999 -- wouldn't .75 in dates before 1900 (ie, negative FP values) be
6:00 *AM* ??? Or does time always progress throughout the day from .0
through .9999 in the positive direction, regardless of whether the date
portion is negative?
--
Cheers,
Lyn.
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:fpednaxCoM9jQkLcRVn-hA@rogers.com...[color=blue]
> Dates are actually stored as 8 byte floating point numbers, where the
> integer part represents the date as the number of days relative to 30 Dec,
> 1899, and the decimal part represents the time as a fraction of a day
> (6:00 AM is .25, Noon is .5, 6:00 PM is .75 and so on). They're good for
> dates between 1 Jan, 100 and 31 Dec, 9999 (although, as Trevor's pointed
> out, it's questionable of what value they are prior to the calendar
> reforms)
>
> --
> Doug Steele, Microsoft Access MVP
>
http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Lyn" <lhancock@ihug.com.au> wrote in message
> news:crnvu2$evn$1@lust.ihug.co.nz...[color=green]
>> Trevor,
>> Thank you for your response. This makes things a lot clearer.
>>
>> I didn't realise that the default date was 30/12/1899. Which means that
>> earlier dates are negative. I suppose that this doubles the number of
>> dates available. I used to work with Progress DB where the zero date was
>> 01/01/1900, so I shouldn't be surprised. However, working in a modern
>> business, I never had to deal with dates before about 1980!
>>
>> Thanks again.
>> --
>> Cheers,
>> Lyn.
>>
>> "Trevor Best" <nospam@besty.org.uk> wrote in message
>> news:41de9799$0$14272$db0fefd9@news.zen.co.uk...[color=darkred]
>>> Lyn wrote:
>>>> I am trying to get my head around the concept of default, special or
>>>> empty values that appear in Access VBA, depending on data type. The
>>>> Access Help is not much (help), and the manual that I have is not much
>>>> help here either. Googling has given me a little help.
>>>>
>>>> This is my current understanding -- I would appreciate any comments or
>>>> corrections...
>>>>
>>>> "" -- this means an empty string when applied to String data type, and
>>>> also to Variant type when it is assumed to contain a string. It is
>>>> also the default value for String type.
>>>
>>> Correct, in the case of a variant, you would have to purposely put ""
>>> into it.
>>>
>>>> 0 -- this is the default value for any numeric data type.
>>>
>>> Correct
>>>
>>>> 00:00:00 -- this is the default (zero) value for any Date data type (if
>>>> not specifically formatted). I think that a "zero" date refers to the
>>>> base date and time of "1/1/100 00:00:00". I was using #1/1/100# as a
>>>> flag to indicate an unknown date, and I was getting an "unknown date"
>>>> hit when the date value was "empty".
>>>
>>> No, the base date (for zero) is 30 Dec 1899
>>>
>>>> Empty -- this is the default value for Variant data type. I presume
>>>> that this value has been defined because until the field has been
>>>> initialized as numeric or string, Access doesn't know whether to use 0
>>>> or "".
>>>
>>> Yes, not only that but variants can be objects as well, hence not
>>> initialised to null.
>>>
>>>> Null -- this applies to Variant data type and is there (I am guessing)
>>>> as a way of setting the value to "unknown" without specifying whether
>>>> the field should contain string or numeric data. It also applies to
>>>> Boolean data type when the normal value of True or False is not known
>>>> (tri-state).
>>>
>>> For a checkbox yes but a yes/no field in a Jet database cannot be null.
>>>
>>> For nulls in general, the value is not present, it doesn't imply unknown
>>> because you can put "unknown" in to a text field to say the value is
>>> unknown, null simply means the data isn't present (see Codd's 3rd rule).
>>> Some people do take it to mean the value is unknown though, particularly
>>> for non text fields where you cannot enter "unknown".
>>>
>>>> Nothing -- this applies to objects and is not really a value. Setting
>>>> an object name to Nothing simply "disconnects" the object name from any
>>>> resources. You cannot test (eg) "If RS = Nothing" as this does not
>>>> make sense. I am not sure how to test whether the object name is
>>>> currently associated with an object.
>>>
>>> Nothing and null are quite similiar in this respect, a variable can be
>>> nothing, or be null but cannot =nothing or =null, to test is simple:
>>>
>>> if objVariable is nothing then...
>>>
>>> if Isnull(variable) then...
>>>
>>>> If I have missed out any important "special values", please feel free
>>>> to add. Also to comment on or correct the above. It will help me get
>>>> a better picture of how and when to use these values.
>>>>
>>>> Thanks in advance.
>>>
>>> All column data types except boolean (yes/no) can be and are initialised
>>> to null in a table (unless a default value is specified).
>>>
>>> The corresponding data types in VBA, (string, integer, long, date,
>>> doubler, etc) cannot be null.
>>>
>>> Variants can be null but are not initialised to null as they can be
>>> objects too so are initialised as empty (test with isempty() function)
>>>
>>> Object variables (dim x as object, and defined class objects, such as
>>> recordset, form, querydef, etc) are initialised to nothing (test with if
>>> variable is nothing then...)
>>>
>>>
>>> --
>>> This sig left intentionally blank[/color]
>>
>>[/color]
>
>[/color]
