Date Field Format Adjustment

[Guest]

Hi all,

I need some help to reformat a date/time field that I have in my database
and I need a good way to do it.

Currently I have a date/time field with no additional formatting, just the
defaults. Since day 1 of the database I have been updating this field using
the Now() function, making the field display date/time.

I want to go back and restructure the information in this field to just
display the Access Default date values(mm/dd/yyyy)

What is an easy way to adjust this field back to original access defaults?

Thanks.

 

[Rick Brandt]

Hi all,

I need some help to reformat a date/time field that I have in my
database and I need a good way to do it.

Currently I have a date/time field with no additional formatting,
just the defaults. Since day 1 of the database I have been updating
this field using the Now() function, making the field display
date/time.

I want to go back and restructure the information in this field to
just display the Access Default date values(mm/dd/yyyy)

What is an easy way to adjust this field back to original access
defaults?

Thanks.

Access dates are not stored "formatted". That is strictly a display
attribute.

However; to do what you want use a default value of Date() instead of Now().
To fix existing values run an update query...

UPDATE TableName
SET TableName.FieldName = DateValue(TableName.FieldName)

DateValue() will strip the time portion (set it to midnight) while retaining
the date portion.

 

[Guest]

Thanks Rick, I may try that.

The reason for this change is that I am getting unexpected results when
running queries with date criteria. The time seems to have an adverse affect
on my results when looking for dates.

I'm forced to use the format() function, or dateserial() function to produce
the proper results when pulling between certain dates.

This just won't do when running reports on the fly from the data. This is
Access 2000 btw.

 

[Rick Brandt]

Thanks Rick, I may try that.

The reason for this change is that I am getting unexpected results
when running queries with date criteria. The time seems to have an
adverse affect on my results when looking for dates.

I'm forced to use the format() function, or dateserial() function to
produce the proper results when pulling between certain dates.

Well that's one way to do it, but mostly I just use something like...

WHERE DateField >= #SomeDate#
AND DateField < #SomeDatePlusOneDay#

This just won't do when running reports on the fly from the data.
This is Access 2000 btw.

Why won't it do?

 

[Jeff L]

Use Date() instead of Now(). If you want to update your data, design
an update query and in the Update To box of your field put
Date(YourFieldName).

Hope that helps!

 

[Guest]

When I use format([date],"mm/dd/yy') and ,my criteria is mm/dd/yy I receive
results for a particular date.

When I use he field with no format() clause, and my criteria is #mm/dd/yyy#
I don't receive any returns.

Access seems to force me to use the format() or dateserial() functions to
have returns on specified date critera.

 

[Guest]

Thanks Jeff. Im in the process of making changes to the code in my forms to
migrate from Now() to Date().

I'll try and utilize the functions you and Rick presented to get the best
results.

 

[Guest]

I tried the date(fieldname) function however I receive a wrong number of
arguments error. Am I missing like "short" or some other formatting name to
place in the clause?

 

[Jeff L]

My mistake. Try Format(DateFieldName, "Short Date") instead.