General Date problem is back

[Jan Il]

Hi all - Access 2000 - Windows ME

A few weeks ago I posted a strange problem I was having with a table
regarding the General Date data type of a field in the table for the
Transaction date.

The General Date format, which is for both the date and time (08/01/03
12:00:00 PM) is necessary in order for the total balance field to be updated
for each line transaction (withdrawal or deposit).

Here is the SQL for the query that the form is based on:

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 also have a data entry form from which all transactions are recorded, and
based upon the table.
Here are the fields of the MyCheckRegister table:

CheckID - Auto-number - Data Type - PK
CheckNo - Number - Data Type - Indexed - Yes (No Duplicates)
CheckDate - Date/Time - Data Type (format General Date)
Trasnaction - Text - Data type
CheckAmt - Currency - Data Type
DepositAmt - Currency - Data Type
Transaction Type - Text - Data Type
Comment - Text - Data Type
BeginBal - Currency - Data Type
TransactionSign - Text - Data Type

My problem is that, I can not enter the General Date information in the
CheckDate field using the General Date format. But, If I copy the format
from a previous row of the table with this format, it will allow me to
change the date and time. But, I can not enter this from scratch in a new
entry. This happened before, and I was able to resolve it by just
recreating the table and reentering all the records. But, this time, it is
not working. :-(

Is there a way to hard code this format, or to somehow use a different
method to make this format hold in this field. It is the basis for the way
the calculations need to work to give me the by line item balance in the
registry running balance field.

I have redone and recreated the table, pulled all data into a new database,
started from scratch, lit Puce and Black candles, ran chickens around the
house a few times, threw boxes of salt over my shoulder, chanted numerous
OhhhhhmMyyyyss, hummed the theme song Star Wars...Battle for Endor
(actually, just to relax my mind a bit...), then ..with nothing working, I
did an uninstall and reinstall of the program....in hopes that..whatever it
might have forgotten to load last time it would remember this time. I have
done as much troubleshooting and research as I can, but..dang....nothing is
working. I am beginning to think that the program may have caught my CRS,
and it has affected the home Office. ????

Sheesh....I just can't figure out what I'm doing wrong? What have I not
tried? I would truly appreciate any suggestions or references...this really
should be such a simple thing, but, for some reason...I am just not able to
find the key to the right solution.

Best regards,
Jan

 

[Jan Il]

Hi Duane!

What do you mean by "I can not enter the General Date information in the
CheckDate field"? Are you getting an error message? Keep in mind that the
format of the field has nothing to do with the way dates are actually stored
in your table. Formats are for display purposes only. A value store in a
field with a format of Short Date is stored in the same way as a value with
General or Long Date.

What I mean by can't enter the General Date is: The format of the General
Date in the Format section of the box at the bottom of the screen when you
select the CheckDate field in the table in design view, show the date format
as
6/19/1994 5:34:23 PM as how it will be displayed. Which is correct as I
need the time element along with the date. But, when I enter the date
06/19/94 into the date field, it does not display the time along with the
date in the date filed on the form as it once did, and without the time
element, the balance will not update per each separate entry with the query
if I have more than one entry for that same date as it did before.

I may not be explaining to well, so if I may, I assume that you may use a
check book at some point. When you record a check you have written you
enter the check number, the date, the person/company it was written to, the
check amount and the new balance by subtracting that check amount on the
same line as the check was recorded. I believe under most circumstances that
is how most check registries work, showing a new balance for each check
written, and not after the last check is written.

The time element as it is used is supposed to allow the balance to be
updated according to the amount of the check for each check written on the
same date, by adding 1 to the time, such as 12:00:00 PM, and then 12:00:01
PM, in order to make each transaction for that date unique. It no longer
does that, because it will no longer add in the time element.

I am getting no error messages.

Can you enter a value into any field in the query? Is the query

updateable?

No, the query is not updateable. The entry form is based on the table.
Everything else works fine, it is just this factor that continues to change
at some point. It worked fine once I was able to resolve the problem
before, but, it is back again. I have made no changes to anything in the db
at all.

Thank you very much for your time and assistance, I truly appreciate it.

Best regards,
Jan

 

[Jan Il]

Hi Duane!

In addition to my perhaps confusing explanation, I am adding the response
from Dan Artuso who, along with Tom Ellison helped me with the original
project. Perhaps Dan's explanation to me in this reply to my original post
will on this part of the format with give you a better understanding of how
and why the time element with the date works in updating the balance.

***********************Beginning of Dan's
Response***************************

Original Message -----

From: "Dan Artuso" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Tuesday, July 29, 2003 5:30 PM
Subject: Re: Just one laat step to finish

Hi Jan,
Ahhhh, but if you include the time in your date field then each record becomes unique
and chronological so you no longer need to have anything but CheckDate in the Where statement
as in:

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

**********************************End off Response**********************

Thank you.

 

[Duane Hookom]

You stated you are only entering the date. How do you expect a time to be
entered also?

 

[Jan Il]

Hi John!

Ummmm... What time SHOULD it display? If you enter only a date, the
time assumed is midnight at the beginning of that day; how can Access
deduce what time you want to choose, if you enter only a date and not
a time?

I'm truly sorry for the confusion on that part...I'll try to explain better
for both you and Duane;

I am typing in the date AND the time, using a generic 12:00:00 PM as a time
for the first entry of a date.

However, when I type in the date and time as I did before, it will not
maintain the time when I move to the next field. When I move to
the next field, the time drops off by itself and just the date remains,
thus, the balance for that entry will not update correctly. It maintains
the same balance amount for every entry made on that same date
until a new date is entered. Then, it updates the balance amount
to the correct total, but, more like a daily total, not by individual entry.

BUT, if I copy one of the date and times from a entry that was made
at a time that it was working correctly, it will let me change the
date and time to whatever I want and the time will not drop off when
I move to the next field. It is pretty much the same problem I had
with the time not remaining a part of the date and time entry before
John, and I was finally able to resolve it by recreating the table
from scratch. But, now it is dropping the time again. I guess it
is more a matter of it will let me type it in, but, it will not
maintain the time portion of the entry. I don't have an input mask.

As for the time element, I use the date the check was written and then use
12:00:00 PM as a time, just to have a chronological time frame to establish
a unique entry, then for each additional entry for the same date, I just add
one number up in the seconds portion of the time for each additional entry
for the same date. Everything works perfect, as long as the time is there.

But, that is where the problem is....the time will not remain in the entry
once I move to the next field. I type in 9/6/2003 12:00:00 PM and as soon
as I move to the next field, it changes by itself to just 9/6/2003. The
time part is dropped off.

You explained to me very clearly in another post on this same issue not long
ago, about how Access deals with time, and how it is displayed. That I
understand, and when the time portion of the date and time entry does not
drop off, the entire process works perfectly. The problem is, it does not
continue to work as it should, as if something keeps changing somewhere. I
don't understand how it just drops the time in the date and time field all
of a sudden like this. There really should be no reason why the General Date
format I have selected for that field should not continue to work. But, it
doesn't. That is why I asked if it might be possible to use code to set a
date and time format somehow to prevent the time from contusing to be
dropped like this?

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

Best regards,
Jan

 

[Tim Ferguson]

I am typing in the date AND the time, using a generic 12:00:00 PM as a
time for the first entry of a date.

General Date will drop the timevalue part if it's midnight. Solution is
either
(a) to enter a time value: for example 2/3/2003 14:33

(b) fix the date format: for exampe "mm/dd/yyyy hh:nn P"



If you want to practise, just do something like this in the immediate
window:

? format(Now(),"General Date")

? format(Date(), "General Date")


HTH


Tim F

 

[Jerry]

Jan;

I put a check book regester in a program for a customer a
while back. I set it up like Quicken's regester. You are
using the time stamp to reorder the entries to keep the
balance correct. Quicken uses the date, then the category
to keep entries ordered correctly. You may have several
entries on the same date, so let the categories then do
the ordering. If you forget to add in a transaction, and
are using the date stamp, you will either have to go back
and find what time to use to put it in the correct order,
or let it stay out of order. Categories would be: Ck #,
ATM, Deposit, ect.
The way I set it up is for the after update event of the
deposit or the payment to look at the date, and then the
category to see if it will need to be reordered. If it
does then a requery is run to reorder, then the procedure
to refigure the balance is run. Without this in place, if
you entered a check number you forgot, or a date of a
transaction from a previous date, reorder would completely
through off the balance. When you get it set up it works
perfectly. I remember your post earlier on how to set up
the balance part. I am curious how you ended up getting
it to work. I advised my client about re-inventing the
wheel, put he had the money, wanted it and I sat down and
figured out how to do it.

Jerry

 

[Jan Il]

Hi Tim!

General Date will drop the timevalue part if it's midnight. Solution is
either
(a) to enter a time value: for example 2/3/2003 14:33

(b) fix the date format: for exampe "mm/dd/yyyy hh:nn P"



If you want to practise, just do something like this in the immediate
window:

? format(Now(),"General Date")

? format(Date(), "General Date")

I tried your suggestions for the immediate window, and it did give the
date and time for the Now that I wanted.

I then entered the date format fix (b) you suggested above in the Format and
it is working so far. <vbg>

I will monitor this and see if it remains stable. I would think it should
with this format in there now. Hopefully, this will put this problem to bed
once and for all.

Thank you so very much for your help with this issue, Tim, I really do
appreciate
it.

Best regards,
Jan

 

[Jerry]

Jan;

If you are interested in how to, e mail me at
(e-mail address removed)

Jerry.

 

[Jan Il]

Hi Jerry,

Jan;

If you are interested in how to, e mail me at
(e-mail address removed)

Jerry.

On it's way....Thank you very much, I am always interested
to learn more about different ways of doing things.

Jan

 

[Jan Il]

Hi Tim!

General Date will drop the timevalue part if it's midnight. Solution is
either
(a) to enter a time value: for example 2/3/2003 14:33

(b) fix the date format: for exampe "mm/dd/yyyy hh:nn P"



If you want to practise, just do something like this in the immediate
window:

? format(Now(),"General Date")

? format(Date(), "General Date")


HTH


Tim F

I made a copy of the db and I have been putting it through some paces
testing, and so far all is holding and still working perfectly.

Thank you so much... ;-))

Very best regards,

Jan