-----Original Message-----
Dear Carlos:
I would like to concentrate on one statement you have made. You said:
"TestDate is a Medium format."
Now a format is the way data is displayed when you see it. It is not
at all descriptive of how the data is being stored. A query works on
data the way it is stored, and has no respect for your display format.
In effect "format" is perception while data type is reality. If your
data type is date/time but your format is medium date, then there can
be times stored in your data but you will not see them. However, your
query will definitely respect the time of day if it is there. You may
need to look at your data using a different format to see what is
really there. Otherwise, you're going to see some surprising and very
inexplicable results when you query the data.
If your DateRegistered is based on the Now() function, then it will
certainly contain times of day as well, even if the format you are
using to view the data suppresses showing it. Again, the query will
respect the time of day portions that ARE THERE even though you don't
see them because you have chosen a display format that hides them.
Now, a combination of [Date] and [DateRegistered] in which [Date] has
no time component, but in which [DateRegistered] (being based on
Now()) does have a time component, this is likely to be unique.
Perhaps you have placed a unique index on this to ensure it is always
unique.
I'm ready to begin creating come query components for this, based on
your response to the above. Please to check out whether [Date]
contains a non-zero time component and get back.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Dear Tom,
First of all thanks for your effort thinking about this.
I'd like to add some more information. TestDate is a
Medium format. TestNo is arranged according to one field
which I did not mention (i.e. DateRegistered, default
value Now()).
I got your point and seems not that easy for me. Would you
like to carry on your idea base on query? And just
curious, is that possible also in Recordset?
Again thanks. God bless
Carlos
-----Original Message-----
Dear Carlos:
I would like to help you tackle this one. It's not going
to be the
easiest.
First, it appears the DayDif can be readily calculated
from TestDate,
and is not needed. The TestNo column has only one real
effect, which
is to order the rows when there are two with the same
TestDate value.
These observations may be quite important, so I want to
get that
straight.
The next step is to establish the "groups". I propose to
do that with
a query that calculates the DayDif value between the
subject TestDate
and the previous TestDate. The objective will be to
produce a list of
the first date of each "group", that being every TestDate
where this
DayDif is > 14. On this basis, I will add a column to
the query that
numbers the groups. Finally, we can rank within each
group.
Before starting with the specifics of each step, I want
you to
understand these steps and agree (or not) that this
sounds like a way
to work toward the solution you want. Otherwise, we
could be spending
a lot of time getting nothing done.
One thing to check out is that every TestDate is a date
only, with no
time recorded other than 0:00:00. If your application
allows time
components on these dates now, or in the future, then
there is
additional work that needs to be done.
Sound OK with you?
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Tue, 30 Nov 2004 16:56:43 -0800, "Carlos"
Hi,
I have a problem and could someone please advise:
A table look similar to this:
TestNo TestDate DayDif
1 22/10/2004
2 23/10/2004 1
3 23/10/2004 0
4 25/10/2004 2
5 26/10/2004 1
6 31/10/2004 5
7 15/11/2004 15
8 17/11/2004 2
9 18/11/2004 1
10 20/11/2004 2
I want to create a query that would give me a column
GroupRank that would look like this: That is, if DayDif
14 days it will start to a new GroupRank and so fort.
TestNo TestDate DayDif
GroupRank
1 22/10/2004 1
2 23/10/2004 1 2
3 23/10/2004 0 3
4 25/10/2004 2 4
5 26/10/2004 1 5
6 31/10/2004 5 6
7 15/11/2004 15 1
8 17/11/2004 2 2
9 18/11/2004 1 3
10 20/11/2004 2 4
In the same situation (say I added a field GroupRank to
a
table, if I want to use Recordset, how does the code
look
like if I use like this approach ?
With rst
.MoveFirst
Do
.Edit
!GroupRank = (Code here)
.Update
.MoveNext
Loop Until .EOF
End With
Please advise. Thanks in advance.
Carlos
NB, I typed this Message using Word and copy paste to
this
box. Should this be unreadable, please reply so I can
resend. Thanks.
.
.
