Date Dilemma

[lmcc007]

Some of the dates are incomplete—such as, 12/2010 or just 1999; therefore, I had to change from using a Date/Time datatype to using three separate number fields—that is, Month, Day, Year. The problem is they want the fields to default to the current date, but still be able to change the date. Can that be done? If so, how shall I approach it? Or, should I just tell them they will have to enter the date each time.

 

[pbaldy]

Sure; use the Day(), Month() and Year() functions respectively, along with the Date() function in the default values.

 

[lmcc007]

Sure; use the Day(), Month() and Year() functions respectively, along with the Date() function in the default values.

I'm not following you here.

I tried using an unbound hidden Date/Time field with Day, Month, and Year control source equals, but not working.

My mind is spinning--give it to me real basic.

 

[CEH]

The way I read it is you really want to use one field for date? Set the "Default value" in the field to Now() ..... use an input mask for short date.

 

[boblarson]

The way I read it is you really want to use one field for date? Set the "Default value" in the field to Now() ..... use an input mask for short date.

If you only want the date, don't use NOW(), just use DATE(). Use the right tool for the right purpose.

 

[pbaldy]

You said you had to switch to 3 separate number fields; month, day, year. I'm saying if you want them to default to the current date use:

Month(Date())
Day(Date())
Year(Date())

as the default values for those 3.

 

[MarkK]

I get a #Name error when I use ...

=Month(Date())

Not sure if you can replicate this but here are the conditions ...
I'm using Access 07. I bind a form to a table, bind a textbox to a text field in that table, and set the DefaultValue property of the textbox to

=Month(Date())

This yields a '#Name' error in the control in a new record.
If I set the the DefaultValue of the textbox to

=Month(Now())

I get '8' in a new record. I get the same result for the Day() and Year() functions. Date() fails, Now() works.

? Year(Date())

works in the immediate pane.
Curious if others can confirm this unexpected result.

 

[boblarson]

I think you'll find it won't work in most versions of Access.

Defaults at table level are very limited in scope as to what functions you can use.

=Date()

or

=Now()

will work but other things won't like
=Environ("username")

etc.

So I think Month() Day() and Year() are also ones that won't work like that.

now that being said, they WILL work as defaults at FORM level.

 

[lmcc007]

You said you had to switch to 3 separate number fields; month, day, year. I'm saying if you want them to default to the current date use:

Month(Date())
Day(Date())
Year(Date())

as the default values for those 3.

I am using Access 2007.

That's it. Thanks pbaldy!!!

 

[MarkK]

Bob:
I'm talking about the DefaultValue property of a textbox. Check it out. I'm curious if you can confirm it fails in that context.
Cheers,
Mark

 

[boblarson]

Bob:
I'm talking about the DefaultValue property of a textbox. Check it out. I'm curious if you can confirm it fails in that context.
Cheers,
Mark

Just built a table and form in 2007 and used

=Month(Date())

=Day(Date())

=Year(Date())

as defaults on the text box and it worked fine. They are bound to fields of a Long Integer datatype.

 

[pbaldy]

That's it. Thanks pbaldy!!!

Happy to help!