30/12/99 date problem

[Jon Lewis]

I know that the above represents a date of 0 as Access stores this type of
data but why is the Date() function failing when used as follows?

A data entry form has a textbox (locked = yes, enabled = no) with the
default value set to "=Date()" (in the textbox control not the field) and
Format "dd/mm/yy". The text box is bound to a field Date/Time data type,
"dd/mm/yy" format.

Works as it should most of the time but suddenly fails and displays 30/12/99
then persits this on subequent new records.

TIA

 

[Allen Browne]

Very strange behavior, Jon.

The setup looks fine. Presumably the quotes around =Date() were just for the
post and not in the property. The format should not be a problem (though I
do prefer the yyyy.)

One has to conclude that something else is going on here.
Is there anything else in the form named Date?
Perhaps a control? A field? A variable? A sub or function?

Is there anything that could be assigning a value to the control?
The BeforeInsert or Current event of the form?
Or an event of another control?

As always, you will want to make sure that the Name AutoCorrect boxes are
unchecked under:
Tools | Options | General

A compact would be a good idea.

When it fails, it might be worth checking your references (Tools |
References from a code window.)

Not sure any of those will pin it down for you, but it is a very odd one.

 

[Guest]

Its 30 December 1899 which is day-zero in Access in fact. I suspect the
problem in your case might be do to with the fact that the DefaultValue
property of a control is a string expression regardless of the data type in
question. The Date() function returns the current date formatted in the
local short date format. What I think might be happening here is that the
date in this format is being interpreted as an arithmetical expression, so
today (in European format) would be the arithmetical expression 20/7/6 which
= 0.476190476190476. This represents a date/time value of 30 December 1899
11:25:43, which when formatted as a short date is 30/12/99.

Try setting the default value property to Format(Date(),"dd/mm/yy")

This will return a string expression so should set it correctly.

If setting the DefaultValue property of a control in code it should always
be wrapped in literal quotes. A common requirement is to set the
DefaultValue for a date to the value entered in the last record entered since
the form was opened. This can be done in the form's AfterInsert event
procedure like so:

Me.txtDate.DefautValue = """" & Me.txtDate & """"

If the literal quotes were omitted the same error as you are getting would
arise.

Ken Sheridan
Stafford, England

 

[Jon Lewis]

Very strange indeed Allen!

There IS other code that assigns a value to the control:
Me.txtDateAdded.DefaultValue = "" and Me.txtDateAdded.DefaultValue = Date in
the Filter and ApplyFilter events of the Form (it's a data edit as well as a
data entry form) during a customised filter by form routine to remove the
default Date() from the control as it's not used as a filter field and in
fact that's what's causing the problem (should have deduced that before!).
The 30/12/99 default only occurs after Filter by Form has been used.

Stupid mistake! Should be Me.txtDateAdded.DefaultValue = "=Date()"

Thanks for the help

 

[Steve Schapel]

Jon,

I am wondering about the use of the '=' in your Default Value setting.
To be honest, I don't know how this works. Just that I have many times
done similar functionailty, and never a problem, but I have always
entered the Default Value simply like this...
Date()
No ""s, no =, just Date(). I would try it.

 

[Jon Lewis]

Hi Allen

I replied to your post yesterday but have just noticed that for some reason
my reply doesn't seem to be displayed.

There was indeed some code re-setting the DefaultValue after removing it
during a custom filter by form procedure.

Me.txtDateAdded.DefaultValue = Date

Should of course be:

Me.txtDateAdded.DefaultValue = "=Date()"

Should have spotted it myself.

Thanks fo you help.

 

[Allen Browne]

Good news, Jon.

I did see your earlier reply. However, I am not seeing many of my won posts
at present, so you're not alone there.