Date Field in Table

[sara]

I have a table with lots of dates. I would like ONE date
to default to the current date, or, better, the day before
the current date (I figure the user is usually entering
work from prior days).

My problem: I have said =Today(), =Date() and =Now() and
all give me date AND time in the table. I ONLY want to
store mm/dd/yyyy in the table. Is there any way to do
this? On the fields where I Don't Default, I get just
what I want.

Thanks,
Sara

 

[Rick B]

The table stores a code that corresponds to a date and time. How you OUTPUT
that data is where you would determine whether you see date and time, date,
etc.

You need to be doing this coding in the FORM where users are creating the
records. There, you can set up the format to display only the date. You
should not be worrying about formats at the table level, just data types and
sizes.

Rick B

 

[sara]

My problem was entering the field on a form - when I later
went to access the field, I never got a match since the
field was date and time and I was only passing date as the
parameter. How can I have the data entry form only take
date? How can I pass only the date part of the parameter
to a query, or as strlinkcriteria to use to open a form?
Thanks
Sara

 

[John Vinson]

My problem was entering the field on a form - when I later
went to access the field, I never got a match since the
field was date and time and I was only passing date as the
parameter. How can I have the data entry form only take
date? How can I pass only the date part of the parameter
to a query, or as strlinkcriteria to use to open a form?

You can use the AfterUpdate property of the datefield textbox on the
form to store only the date portion:

Private Sub txtDate_AfterUpdate()
Me!txtDate = DateValue(Me!txtDate)
End Sub

It might help to understand how Access stores date/time data: what's
actually stored is a double-float number, a count of days and
fractions of a day (times) since midnight, December 30, 1899. The
DateValue function strips off the fractional portion.

Or, if you want to store the time - e.g. by having a Default value of
Now() on the form - but still search for all records on a given date,
you must search for all date/time values between midnight at the start
of the desired date and the next midnight. One easy way to do this is
to use a criterion

= DateValue([Enter date:]) AND < DateAdd("d", 1, DateValue([Enter date:]))

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps