Date/Time will not hold

[Jan Il]

Hi all - Access 2000, Windows ME

I have a data entry form that has a control to enter the date/time. In the
table the form is based on the date/time is set to General, which would be
08/10/03 12:20:00 PM

Every time I open the db, the date/time will not hold, it reverts to date
only. I can copy from a previous date/time entry and manually change the
date, but, it will not allow me to enter a date and then the time. I get an
error that says it is the wrong format.

This is the third time this has happened. I just can not find a way to make
the date/time to hold, either in the table or the control. The format of the
date/time is used to update a balance amount by item, be it an addition or a
subtraction transaction. When I try to format the control on the entry form,
It does not show the date/time format, just a date. The date/time format
just will not hold. In the format selection on the table it shows the
time/date as the General Date. But, when I try to reset the time/date for
the table, it won't change it from the short date, with no time. I just
can't understand why it will not hold from one time to the next, and won't
change the table format. It is really very frustrating.

This SQL for the query the form is based on is.

SELECT T.BeginBal, T.CheckNo, T.CheckDate, T.Transaction, T.CheckAmt,
T.DepositAmt, T.TransactionType, T.Comment, (SELECT SUM(Nz(DepositAmt, 0) -
Nz(CheckAmt, 0) + Nz(BeginBal,0))
FROM MyCheckRegister T1
WHERE T1.CheckDate <= T.CheckDate) AS RunningBalance
FROM MyCheckRegister AS T
ORDER BY T.CheckDate;

I would truly appreciate it someone could tell me where the problem is, or
provide me with a KB article or webs site that might shed some light on this
type of issue.

Best regards,
Jan

 

[John Vinson]

I have a data entry form that has a control to enter the date/time. In the
table the form is based on the date/time is set to General, which would be
08/10/03 12:20:00 PM

Every time I open the db, the date/time will not hold, it reverts to date
only. I can copy from a previous date/time entry and manually change the
date, but, it will not allow me to enter a date and then the time. I get an
error that says it is the wrong format.

Check the Input Mask property of the field. I suspect it may be a
date-only mask. I find masks on date fields to be more hassle than
benefit; if there is one just delete it.

 

[John Vinson]

When I
reset everything again, it works ok, but the next time I open the app, this
one thing seems to just becomes nullified, or disabled, for some reason.

I've NEVER seen this happen, and it seems very wierd. Do you have any
code in the database's Startup form that might do this? or any code
attached to this control?

An Access Date/Time is stored internally as a number, a count of days
and fractions of a day since midnight, December 30, 1899. It's not
stored with "general date" or "long date" or any OTHER format - the
format is applied afterwards for display purposes. I suspect that you
might be typing something which Access can't recognize as a date;
could you give an example of the literal keystrokes which generate
this error?

 

[Jan Il]

Hi John!

I've NEVER seen this happen, and it seems very weird. Do you have any
code in the database's Startup form that might do this? or any code
attached to this control?

An Access Date/Time is stored internally as a number, a count of days
and fractions of a day since midnight, December 30, 1899. It's not
stored with "general date" or "long date" or any OTHER format - the
format is applied afterwards for display purposes. I suspect that you
might be typing something which Access can't recognize as a date;
could you give an example of the literal keystrokes which generate
this error?

In the Format section of the bottom dialog box of the table, I selected
General Date
format listed there, which displays as 7/6/2003 12:00:04 PM - data type is
set for Date/Time

8/19/2003 - this is all that it will now allow me to keystroke enter.

If I copy the date/time displayed from a record line prior to the problem
coming up, it will allow me to manually change the date and time, as long as
it stays in that format (i.e., can't elide the time or the AM/PM). But, it
will not allow me to manually enter that same format for a new entry.

There is no special code in the startup, just to use an opening form. Nor,
is there any code associated with the control, it is in a data entry form,
and the record source is the CheckDate in the table.

It started when I had to go back to 2000 after upgrading to 2002 XP in order
to resolve some other program issues for now. The first time I opened the
app after going back to 2000, this problem appeared. Everything else works
fine. I thought it might be something to do with the way the two programs
treated the date/time format. I just don't understand why it worked just
fine before, but, it won't now. It is the same format, I have not added or
changed anything in the app anywhere. I pulled everything into a new
database and tested it there, same thing. This is the only app in which I
use this format, and all other date functions in my other six apps are
working fine, but, they all just have date formats.

Jan

 

[Jan Il]

Hi John!

I've NEVER seen this happen, and it seems very weird. Do you have any
code in the database's Startup form that might do this? or any code
attached to this control?

An Access Date/Time is stored internally as a number, a count of days
and fractions of a day since midnight, December 30, 1899. It's not
stored with "general date" or "long date" or any OTHER format - the
format is applied afterwards for display purposes. I suspect that you
might be typing something which Access can't recognize as a date;
could you give an example of the literal keystrokes which generate
this error?

I have finally resolved the problem. I pulled all the tables, queries, forms
and macros into a new database again, yet, the problem persisted. I then
deleted the table and recreated it one field at a time exactly as the first
one, and tested each entry to see what the results would be, thinking this
might indicate where the problem might be. Then, I re-entered all the
records to the table, then tested entries from the entry form to see if it
would allow such entry from that form. It did allow me to manually enter the
08/15/03 12:00:00 PM date/time into the control, and it was saved correctly
to the table, and all balance update calculations worked correctly in the
Registry form.

I still don't understand what happened in the original db to create the
problem...everything I did in the new database was *exactly* as I did in the
original, but, for some reason...it worked as it was supposed to in the new
one., whereas, it will not in the original db.

Thank you very much for all your time to assist with this issue, I truly
appreciate it.

Best regards,
Jan

 

[John Vinson]

I have finally resolved the problem. I pulled all the tables, queries, forms
and macros into a new database again, yet, the problem persisted. I then
deleted the table and recreated it one field at a time exactly as the first
one, and tested each entry to see what the results would be, thinking this
might indicate where the problem might be. Then, I re-entered all the
records to the table, then tested entries from the entry form to see if it
would allow such entry from that form. It did allow me to manually enter the
08/15/03 12:00:00 PM date/time into the control, and it was saved correctly
to the table, and all balance update calculations worked correctly in the
Registry form.

VERY VERY wierd; I'm going to chalk it up to Access2000 subtle
corruption and congratulate you on your hard work solving a baffling
problem!