DateTime field showing unwanted Time

[dgifford]

All of a sudden, my date fields are showing a datetime with the time portion
the same in every table and database I open.

The time portion is 12:17:04 AM regardless of what table or database I open.

If I run a query on a date field and convert it to a double format,
"cdbl([the date])", the number has no decimal portion!
The date is stored as a whole number.

If I enter a date in a datetime field the time is automatically appended to
the
date in the display but the time portion is not stored in the data file.

I am able to open the same mdb's on another computer, and there is no
problem with the date fields.

I have played with changing my system Regional Setting and problem still
exhists.

The only thing I was able to do is go into a database table design mode and
add shortdate to the format of each date field.
This fix is temporary because I do not want to have to change 100+ mdb's.

One strange thing I came across was: If I open a query view and enter
Expr1:CStr(#12/11/2002#)
then exit the field, the system automatically changes my
entry to Expr1: CStr(#12/11/2002 12:17:04 AM#).

This anomaly is being created by the OS or Access somehow!

Any suggestions will be greatfully appreciated.

 

[bhicks11 via AccessMonster.com]

Access date field includes the time. If you are viewing the date with a
query or in control box you can use format:

format(datefield,"mmddyy")

Bonnie
http://www.dataplus-svc.com

All of a sudden, my date fields are showing a datetime with the time portion
the same in every table and database I open.

The time portion is 12:17:04 AM regardless of what table or database I open.

If I run a query on a date field and convert it to a double format,
"cdbl([the date])", the number has no decimal portion!
The date is stored as a whole number.

If I enter a date in a datetime field the time is automatically appended to
the
date in the display but the time portion is not stored in the data file.

I am able to open the same mdb's on another computer, and there is no
problem with the date fields.

I have played with changing my system Regional Setting and problem still
exhists.

The only thing I was able to do is go into a database table design mode and
add shortdate to the format of each date field.
This fix is temporary because I do not want to have to change 100+ mdb's.

One strange thing I came across was: If I open a query view and enter
Expr1:CStr(#12/11/2002#)
then exit the field, the system automatically changes my
entry to Expr1: CStr(#12/11/2002 12:17:04 AM#).

This anomaly is being created by the OS or Access somehow!

Any suggestions will be greatfully appreciated.

 

[Klatuu]

You are correct about all date fields showing a time; however, they should
not be showing 12:17:04 AM, they should be showing 12:00:00 AM. Either there
is something somewhere in the OP's app that is inserting that time or there
is corruption in his database.

--
Dave Hargis, Microsoft Access MVP

Access date field includes the time. If you are viewing the date with a
query or in control box you can use format:

format(datefield,"mmddyy")

Bonnie
http://www.dataplus-svc.com

All of a sudden, my date fields are showing a datetime with the time portion
the same in every table and database I open.

The time portion is 12:17:04 AM regardless of what table or database I open.

If I run a query on a date field and convert it to a double format,
"cdbl([the date])", the number has no decimal portion!
The date is stored as a whole number.

If I enter a date in a datetime field the time is automatically appended to
the
date in the display but the time portion is not stored in the data file.

I am able to open the same mdb's on another computer, and there is no
problem with the date fields.

I have played with changing my system Regional Setting and problem still
exhists.

The only thing I was able to do is go into a database table design mode and
add shortdate to the format of each date field.
This fix is temporary because I do not want to have to change 100+ mdb's.

One strange thing I came across was: If I open a query view and enter
Expr1:CStr(#12/11/2002#)
then exit the field, the system automatically changes my
entry to Expr1: CStr(#12/11/2002 12:17:04 AM#).

This anomaly is being created by the OS or Access somehow!

Any suggestions will be greatfully appreciated.

 

[Jerry Whittle]

That is strange. I tried playing with Expr1:CStr(#12/11/2002#) in a query in
A07 and didn't see that behavior.

Autocorrect? Maybe go up to the Office Button, Access Options button,
Proofing. Click on the AutoCorrect options and see if something like
#12/11/2002# is listed there. Very strange.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

You are correct about all date fields showing a time; however, they should
not be showing 12:17:04 AM, they should be showing 12:00:00 AM. Either there
is something somewhere in the OP's app that is inserting that time or there
is corruption in his database.

--
Dave Hargis, Microsoft Access MVP

Access date field includes the time. If you are viewing the date with a
query or in control box you can use format:

format(datefield,"mmddyy")

Bonnie
http://www.dataplus-svc.com

All of a sudden, my date fields are showing a datetime with the time portion
the same in every table and database I open.

The time portion is 12:17:04 AM regardless of what table or database I open.

If I run a query on a date field and convert it to a double format,
"cdbl([the date])", the number has no decimal portion!
The date is stored as a whole number.

If I enter a date in a datetime field the time is automatically appended to
the
date in the display but the time portion is not stored in the data file.

I am able to open the same mdb's on another computer, and there is no
problem with the date fields.

I have played with changing my system Regional Setting and problem still
exhists.

The only thing I was able to do is go into a database table design mode and
add shortdate to the format of each date field.
This fix is temporary because I do not want to have to change 100+ mdb's.

One strange thing I came across was: If I open a query view and enter
Expr1:CStr(#12/11/2002#)
then exit the field, the system automatically changes my
entry to Expr1: CStr(#12/11/2002 12:17:04 AM#).

This anomaly is being created by the OS or Access somehow!

Any suggestions will be greatfully appreciated.