Re: Does DateDiff Have A Bug

Beetle,

Hooray, I did some tweaking with the form and controls and everything is
working great. I changed the form (Members) RecordSource back to my Main
form, then used the query you showed me how to build as the control source
for my text box named txtDaysRemaining.

After some reading through the help files I put this string together for the
ControlSource of the txtDaysRemaining textbox which looks like this:

=DLookUp("DaysRemaining","qryExpire"," [Main_ID]=" _
& Forms![Members]!Main_ID)

Now I just use the results from the query and everything works better than
ever just like you said it would. I got rid of a lot of useless code
afterwards too. I'm ecstatic because I learned some new skills in Access
thanks to you. Major thanks my friend, you saved me big time.

KP

"Beetle" wrote:

Well, I can't give any specific examples that would apply
to your application since I know nothing about it, so this will
be just a simplistic example.

Suppose you have a table like the following;

tblMembers
*********
MemberID
FirstName
LastName
EntryDate

and the data looks like;

MemberID FirstName LastName EntryDate
1 John Smith 6/2/2008
2 Sally Jones 10/31/2008
3 Bill Clark 5/25/2009


Now you would go to Queries/Create query in Design View,
select tblMembers from the Show Table dialog, and add all
of the table fields to query design grid. Next, in the first empty
column to the right of the fields you just added, you would put
the following in the Field row;

DaysRemaining:DateDiff("d", Date(), DateAdd("yyyy", 1, [EntryDate]))

make sure the Show box is checked and save the query as
qryMembers. The resulting data set of this query would look
like;

MemberID FirstName LastName EntryDate DaysRemaining
1 John Smith 6/2/2008 0
2 Sally Jones 10/31/2008 151
3 Bill Clark 5/25/2009 357

Expanding on this further, let's suppose you wanted to see
the actual expiration date (again, a calculated value) as well
as the days remaining. You would create the query using
all the fields from tblMembers, and you would add two
calculated fields. The first calculated field would look like;

Expiration: DateAdd("yyyy", 1, [EntryDate])

and the second calculated field (which would use the first
calculated field within it's calculation) would look like;

DaysRemaining: DateDiff("d", Date(), [Expiration])

A partial example of the data set of this query would look like;

LastName EntryDate Expiration DaysRemaining
Smith 6/2/2008 6/2/2009 0
Jones 10/31/2008 10/31/2009 151
Clark 5/25/2009 5/25/2010 357

This query (qryMembers) can be used as the record source for
form or report in the same way you would use a table as a record
source, and the calculated fields from the query can be used
as a control source just the same as a field in a table. A calculated
field in a query *is a field*, just like a field in a table, it's just that
the result is not stored anywhere, only calculated as needed based
on the existing data in the table.

--
_________

Sean Bailey


"Keypad" wrote:

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
    ... 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
    ... I created the query with the code you suggested and it works great ... 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: Inconsistent MDX query performance
    ... I mean accessing the measure by displaying a large number of members. ... calculation for 8000+ member can take 8000 seconds!!!! ... optimized for your type of query or not. ... Each queries a cube on 13 axes. ...
    (microsoft.public.sqlserver.olap)
Loading