Thankyou very much for putting me on the right track, I will study this
and
give it a go. By the way, the website with all the useful tips is
fantastic.
Deb
:
Do not store the StartNumber in your table. Instead, have Access look
up the
starting value from yesterday's record for you.
This is the only way to ensure that the values are always right. For
example, if there is no entry for May 21 (Sunday), it will read the
previous
value for you. But it it does turn out later that there was an entry
for May
21, once that entry is entered, the database will still work correctly
and
locate the previous ending value for you. There is no simple way to get
that
kind of scenario right 100% of the time if you are actually storing the
start number again, so it has to be adjusted if another entry is
inserted.
So, how do you look up the previous date? The answer will depend on
your
table and fields, but for this example we will assume a table named
"tblReading" with these fields:
ReadingDate Date/Time primary key
EndNumber Number required.
Copy the ELookup() function from this page:
http://allenbrowne.com/ser-42.html
Then on your form, put this expression into the ControlSource of a text
box:
=ELookup("EndNumber", "tblReading", "[ReadingDate] < " &
Format(Nz([ReadingDate], #1/1/1900#), "\#mm\/dd\/yyyy\#"),
"[ReadingDate] DESC")
(You need the ELookup() because the DLookup() built into Access lacks
the
ability to specify which value to look up, and DMax() fails when a
meter is
replaced so that yesterday's reading is no longer the highest.)
If you want to do this in a query, you could type this into a fresh
column
in the Field row:
StartNumber: (SELECT TOP 1 EndNumber
FROM tblReading AS Dupe
WHERE Dupe.ReadingDate < tblReading.ReadingDate
ORDER BY Dupe.ReadingDate DESC)
The query results will be read-only. It is called a subquery.
If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
Microsoft has other suggestions in this article:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Ok am having trouble wording this as you can see from the subject
title!
so I
couldnt even search very well for the answer.
On my form, there are 2 text boxes called EndNumber and StartNumber.
The StartNumber I want to automatically be filled in because I want
it to
be
the previous records EndNumber. This form is only filled in once a
day so
each day, the StartNumber needs to somehow take the previous days
entry
EndNumber so the new record for the day when the form is opened will
already
have in the StartNumber text box yesterdays EndNumber. Hope this
makes
sense
and can anyone help. thanks
