Strange happenings in date field

[Wasim Yasin via AccessMonster.com]

Dear All
I had a table having field 'Date' with 'Medium date' property.I don't know
how user entered something that now some records show date as '12-dec-1899'
but in table when i click the field the text converted to '00:00:09'and
when out the field it agains become 12-dec-1899. When i run an update query
to change the contents of '12-Dec-1899' it results '(0) rows updated.
Any help is appreciated.

 

[Alex Dybenko]

i think user enters only time portion, so full date is:
'12-dec-1899 00:00:09'
you can get such records using:
Where DateField between #12/12/1899# and #12/13/1899#

if like this
Where DateField <1

 

[Wasim Yasin via AccessMonster.com]

But the field is set to Middle Date. How is possible to enter time. I also
Masking the field by "99\-99\-9999";0.

 

[David C. Holley]

Anything with 1899 is an indicator that either the full date was entered
which would result in 12/31/1899 (m/d/y) or that the year was omitted.
To solve this problem ensure that you've set an appropriate InputMask
for the InputMask property.

Second, anytime you're doing a query using a date value for the criteria
,be certain that you've enclosed the value in pound signs #12/31/1899#

 

[David C. Holley]

If I'm not mistaken 9 indicates that the character is OPTIONAL, 0
indicates that the character is REQUIRED. Double check Help.

 

[Tim Ferguson]

I had a table having field 'Date'

'Date' is a really bad name for a field as it's a reserved word in VBA
and in SQL and you _will_ find yourself wrapped up in hard-to-find bugs.
Choose something descriptive like OpeningDate or EndOfTrialPeriod etc.

with 'Medium date' property.

The format property only affects the field's default display in a
control. The content is still a full date and time value.

I don't
know how user entered something that now some records show date as
'12-dec-1899' but in table when i click the field the text converted
to '00:00:09'

This looks like an Input Mask -- I strongly advise you to remove it
altogether. If you decide you really like IMs, then add them to the
controls on the form, rather than the table.

A date value of 1899/12/31 means a zero date value, and only a time
value. Not surprising if you have an IM that is forcing a time value and
not allowing the user to type a date.

What are you actually trying to achieve here?

All the best


Tim F