Re: Does DateDiff Have A Bug

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

"Beetle" wrote:

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:DateDiff("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


"Keypad" wrote:

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

"Beetle" wrote:

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:DateDiff("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


"Keypad" wrote:

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!

"Gina Whipp" wrote:

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

"Keypad" <Keypad@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C3E7CD6D-74BD-47CB-96B4-D34110F374C8@xxxxxxxxxxxxxxxx
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


"Keypad" wrote:

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.



.

Relevant Pages

  • Re: Does DateDiff Have A Bug
    ... a calculated field in a query is the way to go here. ... But here's why I added the DaysRemaining ... I'm trying to write the result of lngDays when it reaches zero days ...
    (microsoft.public.access.formscoding)
  • Re: Does DateDiff Have A Bug
    ... Now I just use the results from the query and everything works better than ... MemberID FirstName LastName EntryDate DaysRemaining ... calculated field within it's calculation) would look like; ... if there are zero days left which in ...
    (microsoft.public.access.formscoding)
  • Re: Does DateDiff Have A Bug
    ... Now you would go to Queries/Create query in Design View, ... MemberID FirstName LastName EntryDate DaysRemaining ... calculated field within it's calculation) would look like; ... I'm trying to write the result of lngDays when it reaches zero days ...
    (microsoft.public.access.formscoding)
  • Re: Does DateDiff Have A Bug
    ... Now you would go to Queries/Create query in Design View, ... MemberID FirstName LastName EntryDate DaysRemaining ... calculated field within it's calculation) would look like; ... I'm trying to write the result of lngDays when it reaches zero days ...
    (microsoft.public.access.formscoding)
  • Re: Does DateDiff Have A Bug
    ... But here's why I added the DaysRemaining ... have only days left before a membership expires. ... I'm trying to write the result of lngDays when it reaches zero days ...
    (microsoft.public.access.formscoding)