How to set default date format in Access 07

[Nih]

I have an Access form that my users enter new customers into and it
automatically generates the date by using the Now() function, the problem is
the date that it auto inserts has the time after it, for instance 3/20/2008
8:35AM. How do I get this to only show the date ie 3/20/2008 and cut the
timestamp off?

I have tried filters on the date field but they do not work, is Now() set up
to always include the time, if so what would be a better function to just
insert today's date? I tried Date() but that didn't work

 

[Klatuu]

use Date()
Date() includes only the date
Now() includes date and time
And, would you believe
Time() returns only the time

 

[Douglas J. Steele]

Use the Date function instead.

 

[John W. Vinson]

I have an Access form that my users enter new customers into and it
automatically generates the date by using the Now() function, the problem is
the date that it auto inserts has the time after it, for instance 3/20/2008
8:35AM. How do I get this to only show the date ie 3/20/2008 and cut the
timestamp off?

I have tried filters on the date field but they do not work, is Now() set up
to always include the time, if so what would be a better function to just
insert today's date? I tried Date() but that didn't work

What "didn't work"? Now() does in fact return the date and time; that's what
it's FOR. Date() returns the current date.

Please explain how you tried to use Date() and what errors you got. Do note
that you can get strange results if you have a Field or form control named
Date, since Access can get confused about which Date you mean.

 

[JulieS]

I have an Access form that my users enter new customers into and it
automatically generates the date by using the Now() function, the
problem is
the date that it auto inserts has the time after it, for instance
3/20/2008
8:35AM. How do I get this to only show the date ie 3/20/2008 and cut
the
timestamp off?

I have tried filters on the date field but they do not work, is Now()
set up
to always include the time, if so what would be a better function to
just
insert today's date? I tried Date() but that didn't work

Setting the format in the form to =Date() works fine in my database.
What didn't work when you tried it?

Julie

 

[Nih]

I tried Date() before but Access gave me an error. I will check the error
when I get back to work and post exactly what it says. This is Access 07, is
Date() no longer valid on that version?

 

[Klatuu]

It is valid.
What error did your get?
Where did you get it?
Do you have any field, control, form, report or anything in your database
named Date?

More info gets better answers.

 

[Evi]

You need to type Date() next to Default Value in your *Table's* Design View.
Is that what you did?

The last time Date() didn't work for me, it was when I had some missing
References.
Open a Module (a blank new one will do), go to Tools, References and tell us
which items are ticked. There may be something absent there.
Are any of them marked as MISSING?

Evi

 

[Nih]

It is a very small database with 1 form and this form does not have a "date"
field on it. The only table in it does have a field called "date" which is
the field I want Access to autofill with today's date.

I go to the design view of the table and click on date, in the default value
field I typed in Date (), Date(), =Date(0, etc and every time I try to save
the table i get "unknown function 'date' ini validation expression or default
value on 'Active.Customers.date".

 

[Nih]

I also changed the date field's name to "customer date" to see if that fixed
it...it didn't

 

[Klatuu]

Well, some improvement. You went from a disasterous name to a really bad name.

First, you were using a reserved word as a name - Date
That will confuse Access

Now you have a name with spaces in it. Names should not have spaces or any
other special characters. They should have only numbers, letters, and the
underscore character.

Now, check the name of the control on your form the field is bound to.
Unless you changed it since you created the form, it is probably also named
Date. Change that as well.

What is
Active.Customers.date ?


Then in the DefaultValue property it should be
=Date()

 

[Nih]

Active Customers is the name of the table the date field is located in. I
changed the name of the date field to "creation" so there are no issues with
spaces or reserved names.

There is only 1 form which is used to enter new customer info in and this
form does not have a "date" field. I want the date to be automatically
entered when the user creates a new customer record, that is why I wanted the
auto date function, so the users won't have to continuously enter today's
date.

 

[Klatuu]

That is exactly what the DefaultValue property of a control does.
When you create a new record, it automatically assigns the default value to
the control as soon as you type any character in any bound control on the
form.

 

[Nih]

Ok I understand what you mean. What is the best way to add the field to my
existing form and have it bound to the customer's first name so that when the
user enters a customer name the date appears on the form?

Do I put the =Date() as the default value in the control on the form or in
the properties of the field within the table? Everytime I try to put =Date()
in the default value of the table's field I get that error. I can use =Now()
but like I said that gives me the time as well and I don't want that

 

[Klatuu]

The Default Value property applies to the control it is set in. You your
case, you have a field in your table you want to populate with the current
date when the record is created. It is or should be bound to a control on
your form. That is the control you put the =Date() in the Default Value
property.

Maybe I confused you. You don't have to type anything into that control.
When you create a new record, you will see only empyt controls.
As soon as you type the first character in ANY control on the form, you will
see the date show up in the control.

 

[Nih]

I created the control on the form and just like the table if I put =Now() in
the default value it will give new records the correct date (with time) but
if I put =Date() in the control I get #Name?.

I really think Access 07 has a problem with that =Date() function

 

[Douglas J. Steele]

Did you check the References collection, like Evi suggested?

 

[Nih]

I actually don't see the "references" option anywhere. the toolbars in 07
are different, where would I find this option in 07?

 

[Nih]

I found the references section and it did show something MISSING, it refered
to some kind of utility. I cleared the checkbox and GASP it appears to be
working.

Thank you so much everyone for your help.

 

[Evi]

Ulp! you cleared the check box?? I don't think that is what you should have
done that. it's like throwing away your fire alarm because its beeping.
You need to find the missing file (s) and add it/them (unless they have
reappeared by themselves).
It sounds harder than it is, they may be in the list already and when they
are ticked will simply be added when you close and re-open the list.

if not we can tell you how to find them and, if necessary, re-register them.
it is all fixable.

If you look on
http://allenbrowne.com/ser-38.html
Allen has a list of the references you ought to see in Acc2007. Add them if
they aren't there and ensure they are in the order he gives.
For me the one that seemed to screw up Date() was, if I remember correctly,
not having Microsoft DAO 3.6

BTW, to put a Default Value in your Table, you shouldn't need the =Date()
(unless Acc2007 is different)
Just type
Date()
next to Default Value in Table Design.
to get that Default value into your table.

Evi