Beetle,
Sounds great if it can work the way you say. My problem is in using
your
approach, I would not know where to begin because I can't see it
through your
eyes. I lack the foresight you have in the implementation. Do you
have any
examples I could look at, some way to see things in action so to speak.
I'm
all pumped right now thinking about how your approach would work
though.
Sounds pretty awesome. I'll post back here later, need to run some
errands.
Thanks a million for your reply.
KP
:
First, a minor correction to the example I posted for a
calculated field in a query. You would referencce the field
name directly, not Me.txtVoidDate, so it would look like;
DaysRemaining
ateDiff("d",Date, DateAdd("yyyy", 1, [VoidDate]))
Second, a calculated field in a query is the way to go here.
You can create a simple query using all the necessary fields from
you table, then add another calculated field using the example above.
You can then use this query as the recordsource for your form
or report and you can reference this calculated query field in the
same way that you would reference a field in a table. The
advantage is that the value is not stored, just calculated on the
fly,
so it is always accurate and you don't have to go to all the extra
work to try and update/manage a stored value in a table. This
is the reason that, with a few exceptions, it is generally poor
practice to attemt to store a calculated value. The stored value
is *not* automatically recalculated if the underlying values are
changed, so you and Access have to work harder than necessary
to try to keep the stored value accurate.
Additionally, you can then use this calculated DaysRemaining field
to sort records, filter records, apply criteria, etc. in order to
see only the records you want, in any order that you want. You
can do this directly in the query, or you can do it at the
form/report
level. It will give you much more flexibility without the extra brain
damage.
--
_________
Sean Bailey
:
Beetle,
Yep, calculated value it is. But here's why I added the
DaysRemaining
field. What if I want to know which members have expired
memberships or who
have only days left before a membership expires. I want to be able
to create
a report that shows me that data. The only thing I could think of
that would
allow me to do that was to include the DaysRemaining field in my
table.
My approach might be the wrong way to accomplish this, but I can't
think of
a better way to do it. Any suggestions on a different, simpler
approach are
surely welcome. I'm going nuts trying to get it to work my way.
Thanks for
the input too.
KP
:
I would question why you are going to all this trouble.
The days remaining is just a calculated value that shouldn't
be stored in the table in the first place. You could do this with
simple calculated field in a query;
DaysRemaining
ateDiff("d",Date, DateAdd("yyyy", 1,
Me.txtVoidDate))
Use the query as the record source of your form, then in the
Current event of your form call your function if days remaining
is less than 1;
Private Sub Form_Current ()
If DaysRemaining < 1 Then
MembershipExpired()
End If
End Sub
--
_________
Sean Bailey
:
Gina,
I am still unclear as to the results you are trying to see.
Are you trying
to prevent the '0' that defaults on creating a new record?
No, I'm trying to write the result of lngDays when it reaches
zero days
left. For example, if lngDays equals 32 days left I want to
put that value
in table field DaysRemaining so that DaysRemaining shows 32
days left before
expiration. Likewise, if there are zero days left which in
this case would
mean a membership has expired I also want to put that value
into field
DaysRemaining.
When DaysRemaining = 0, I want to call function
MembershipExpired(). The
problem is when lngDays reaches zero and I call my UpdateDays()
function to
input the zero value into DaysRemaining it doesn't get input
right away like
it does with any of the other values. Instead, I have to move
between
records and Minimize the form window then the value zero will
get saved.
It's weird because values other than zero get input
immediately.
I discovered this by opening my Main table and resizing it to
see both the
form results and table results. In all cases a value other
than zero got
input into the table and I saw the value show immediately, but
not with the
zero value. Go figure!
:
Keypad,
I am still unclear as to the results you are trying to see.
Are you trying
to prevent the '0' that defaults on creating a new record?
If that is the
case, in your table, go to the field and below in the
properties section,
remove the default value (leave it blank). If you are trying
to prevent
something else, please explain...
--
Gina Whipp
"I feel I have been denied critical, need to know,
information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina, John,
I was wrong about DiffDate as being the culprit. Turns out
that the SQL
string I'm using to write the value I get from DiffDate
will write the
number
of days left between dates except zero. I have to move
back and forth
between records before the number zero get's input into the
table. This
is
what I have:
Function CheckVoidDate()
lngDays = Abs(DateDiff("d", DateAdd("yyyy", 1,
Me.txtVoidDate),
Date))
Select Case lngDays
Case 0 To 7
Call UpdateDays(strName, lngDays)
End Select
End Function
Function UpdateDays(strName As String, lngDays As Long)
CurrentDb.Execute "UPDATE Main SET DaysRemaining = "
_
& lngDays & " WHERE (((Full_Name) = " & strName &
"))"
End Function
:
Hello,
Although I cannot confirm it, but I think DateDiff
function may have a
bug.
I tried using this function with different dates by
changing day and year
parts and I get mixed results.
I don't trust the number of days the function returns
after testing it by
changing dates around. It may also be my coding but I'm
not experienced
enough to know how to narrow down the problems.
Can someone look at my database and tell me what you
think.
Kidding aside,