DateDiff issue

[Dave]

I was helped here awhile back with a select case issue and got it fixed
(Thanks)

Private Sub Q1_AfterUpdate()
Dim age

age = DateDiff("d", [Q1], Date) / 365
Select Case age
Case Is < 40
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Case Is > 72
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Sub


However it was pointed out that this only give the correct answer ½ the
time.
A solution was generously provided.

Function GetAge(dtmBD as Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + _
(Date < DateSerial(Year(Date), Month(dtmBD), Day(dtmBD)))
End Function

Basically, this counts on the fact that a True expression has a value of
-1, and so if the current date is less than the birthdate value in the
current year, it subtracts one from the year difference between the two.

If you're using Access 95 or 97, use a Date variable rather than a
variant.

--Ken

GetAge = DateDiff("yyyy", dtmBD, Date) +(Date < DateSerial(Year(Date),
Month(dtmBD),Day(dtmBD)))


I just don't know how to really implement it.

Can anyone point me in the right direction on how to use this in my above
code.

Any help here will be appreciated.

Thanks in advance
Dave

 

[Dave]

This is what I have tried with no luck

Private Sub Q1_AfterUpdate()

Function GetAge(dtmBD As Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + (Date < DateSerial(Year(Date),
Month(dtmBD), Day(dtmBD)))
End Function

Select Case GetAge
Case Is < 40
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Case Is > 72
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Sub

 

[Dave]

And this:

Private Sub Q1_AfterUpdate()

Function GetAge(Q1 As Date) As Integer
GetAge = DateDiff("yyyy", Q1, Date) + (Date < DateSerial(Year(Date),
Month(Q1), Day(Q1)))
End Function

Select Case GetAge
Case Is < 40
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Case Is > 72
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Function

 

[BruceM]

I haven't really studied the GetAge function, but it needs to be outside of
the other function. It is a stand-alone, user-defined function. If you are
going to use it in multiple places throughout the database, place the
function in a standard module. If it is going to be used in just one form
or report you could put it in that form's module. I usually put it up top,
but it doesn't really matter as long as you know where it is.

Note that the first line of the function contains something in parentheses
(dtmBD As Date). That is called an argument. In this case you are
supplying the birthdate. The function expects it, and won't work without
it.

Here is the function by itself:

Function GetAge(dtmBD As Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + (Date < DateSerial(Year(Date),
Month(dtmBD), Day(dtmBD)))
End Function

Here's one way to call it. I wil assume your date of birth field is named
DoB, and that the value is to be displayed in the text box txtAge.

Private Sub Q1_AfterUpdate()

txtAge = GetAge(Me.DoB)

End Sub

A bit more refined:

Private Sub Q1_AfterUpdate()

dim datDoB as Date
datDoB = Me.DoB
txtAge = GetAge(datDoB)

End Sub

Or in the control source of txtAge:

=GetAge([DoB])

You could also use it in a query. At the top of a blank column:

AgeCalc: GetAge([DoB])

The advantage of the query is that it is available for every record. If you
use the After Update vent of Q1 only you will see the same date until the
next time you update Q1.

This is what I have tried with no luck

Private Sub Q1_AfterUpdate()

Function GetAge(dtmBD As Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + (Date < DateSerial(Year(Date),
Month(dtmBD), Day(dtmBD)))
End Function

Select Case GetAge
Case Is < 40
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Case Is > 72
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Sub

I was helped here awhile back with a select case issue and got it fixed
(Thanks)

Private Sub Q1_AfterUpdate()
Dim age

age = DateDiff("d", [Q1], Date) / 365
Select Case age
Case Is < 40
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Case Is > 72
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Sub


However it was pointed out that this only give the correct answer ½ the
time.
A solution was generously provided.

Function GetAge(dtmBD as Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + _
(Date < DateSerial(Year(Date), Month(dtmBD), Day(dtmBD)))
End Function

Basically, this counts on the fact that a True expression has a value of
-1, and so if the current date is less than the birthdate value in the
current year, it subtracts one from the year difference between the two.

If you're using Access 95 or 97, use a Date variable rather than a
variant.

--Ken

GetAge = DateDiff("yyyy", dtmBD, Date) +(Date < DateSerial(Year(Date),
Month(dtmBD),Day(dtmBD)))


I just don't know how to really implement it.

Can anyone point me in the right direction on how to use this in my above
code.

Any help here will be appreciated.

Thanks in advance
Dave

 

[Bob Quintal]

This is what I have tried with no luck

Private Sub Q1_AfterUpdate()

Function GetAge(dtmBD As Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + (Date <
DateSerial(Year(Date), Month(dtmBD), Day(dtmBD)))
End Function

Select Case GetAge
Case Is < 40
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Case Is > 72
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Sub

I was helped here awhile back with a select case issue and got it
fixed (Thanks)

Private Sub Q1_AfterUpdate()
Dim age

age = DateDiff("d", [Q1], Date) / 365
Select Case age
Case Is < 40
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Case Is > 72
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Sub


However it was pointed out that this only give the correct answer
½ the time.
A solution was generously provided.

Function GetAge(dtmBD as Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + _
(Date < DateSerial(Year(Date), Month(dtmBD), Day(dtmBD)))
End Function

Basically, this counts on the fact that a True expression has a
value of -1, and so if the current date is less than the
birthdate value in the current year, it subtracts one from the
year difference between the two.

If you're using Access 95 or 97, use a Date variable rather than
a variant.

--Ken

GetAge = DateDiff("yyyy", dtmBD, Date) +(Date <
DateSerial(Year(Date), Month(dtmBD),Day(dtmBD)))


I just don't know how to really implement it.

Can anyone point me in the right direction on how to use this in
my above code.

Any help here will be appreciated.

Thanks in advance
Dave

You cannot insert a function or sub-procedure into another function
or sub-procedure.one must end before the other begins.

Rearrange your code to

Function GetAge(dtmBD As Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + _
(Date < DateSerial(Year(Date), _
Month(dtmBD), Day(dtmBD)))
End Function

Private Sub Q1_AfterUpdate()

Select Case GetAge(Birthday)
Case Is < 40
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Case Is > 72
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Sub

 

[Dave]

Thanks for the help.
I seem to be closer but may still have some logic not right.
Supposed to kick them to the ineligible pg if younger then 40 or older then
74
Seems to work ok on the 40 end but not so good on the 74 end.
does this function work "Exact"?
here is what I have now

Function GetAge(dtmBD As Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + (Date < DateSerial(Year(Date),
Month(dtmBD), Day(dtmBD)))
End Function
Private Sub Q1_AfterUpdate()

txtAge = GetAge(Q1)

Select Case txtAge
Case Is < 39
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
Case Is > 75
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Sub

see any issues with the logic or is the function not precise

Thanks
dave

 

[BruceM]

You should be clear about what is not working. "Not so good" is vague. The
function returns an integer, so there is nothing between, say, 74 and 75.
In that sense it is exact.

Your logic is inconsistent. If you are trying to exclude 39 and younger or
75 and older, either:
Case Is <= 39
Case Is >= 75

or:

Case Is < 40
Case Is > 74

There are a few other points. For one, you should declare your variables.
Add Option Explicit below Option Compare Database at the top of the code
module. To assure this is part of every code module, in the VBA editor
click Tools > Options. Click the Editor tab, and check the box for Require
Variable Declaration. These instructions are for pre-2007. I'm not sure
how to handle it in Access 2007. Undeclared variables, if the option is
allowed, are variants, which may not always be what you want. Also,
spelling and typing errors can be very difficult to track down. Any
misspelling, of a function or anything else, is treated as a variable of
variant type. If the "variable" has no other value it is null, which can
cause all sorts of problems that are very hard to find in code that compiles
correctly.

Also, I would be as explicit as possible about the argument passed to
GetAge. It seems Access is recognizing that it is a field, but I would
either assign the Q1 value to a variable or use the Me.Prefix.

Private Sub Q1_AfterUpdate()

Dim intAge as Integer ' the function returns an integer
Dim datAge as Date

datAge = Me.Q1
intAge = GetAge(datAge)
' or intAge = GetAge(Me.Q1)

Select Case intAge
Case Is < 40
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
Case Is > 74
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Sub

Thanks for the help.
I seem to be closer but may still have some logic not right.
Supposed to kick them to the ineligible pg if younger then 40 or older
then 74
Seems to work ok on the 40 end but not so good on the 74 end.
does this function work "Exact"?
here is what I have now

Function GetAge(dtmBD As Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + (Date < DateSerial(Year(Date),
Month(dtmBD), Day(dtmBD)))
End Function
Private Sub Q1_AfterUpdate()

txtAge = GetAge(Q1)

Select Case txtAge
Case Is < 39
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
Case Is > 75
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Sub

see any issues with the logic or is the function not precise

Thanks
dave

I was helped here awhile back with a select case issue and got it fixed
(Thanks)

Private Sub Q1_AfterUpdate()
Dim age

age = DateDiff("d", [Q1], Date) / 365
Select Case age
Case Is < 40
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Case Is > 72
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Sub


However it was pointed out that this only give the correct answer ½ the
time.
A solution was generously provided.

Function GetAge(dtmBD as Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + _
(Date < DateSerial(Year(Date), Month(dtmBD), Day(dtmBD)))
End Function

Basically, this counts on the fact that a True expression has a value of
-1, and so if the current date is less than the birthdate value in the
current year, it subtracts one from the year difference between the two.

If you're using Access 95 or 97, use a Date variable rather than a
variant.

--Ken

GetAge = DateDiff("yyyy", dtmBD, Date) +(Date < DateSerial(Year(Date),
Month(dtmBD),Day(dtmBD)))


I just don't know how to really implement it.

Can anyone point me in the right direction on how to use this in my above
code.

Any help here will be appreciated.

Thanks in advance
Dave

 

[Dave]

Sorry - you are correct it was not a clear indication of the issue
I had originally had
Case Is < 40
Case Is > 74
And for some illogical reason (seemed right at the time) I changed it to
Case Is < 39
Case Is > 75

I have now changed it back
Aslo my testing process was not thinking correctly also.

NEway
I dimed the variable as you suggested, fixed the ages and tested more
accurately and it is working fine now.

Thanks to all

dave

You should be clear about what is not working. "Not so good" is vague.
The function returns an integer, so there is nothing between, say, 74 and
75. In that sense it is exact.

Your logic is inconsistent. If you are trying to exclude 39 and younger
or 75 and older, either:
Case Is <= 39
Case Is >= 75

or:

Case Is < 40
Case Is > 74

There are a few other points. For one, you should declare your variables.
Add Option Explicit below Option Compare Database at the top of the code
module. To assure this is part of every code module, in the VBA editor
click Tools > Options. Click the Editor tab, and check the box for
Require Variable Declaration. These instructions are for pre-2007. I'm
not sure how to handle it in Access 2007. Undeclared variables, if the
option is allowed, are variants, which may not always be what you want.
Also, spelling and typing errors can be very difficult to track down. Any
misspelling, of a function or anything else, is treated as a variable of
variant type. If the "variable" has no other value it is null, which can
cause all sorts of problems that are very hard to find in code that
compiles correctly.

Also, I would be as explicit as possible about the argument passed to
GetAge. It seems Access is recognizing that it is a field, but I would
either assign the Q1 value to a variable or use the Me.Prefix.

Private Sub Q1_AfterUpdate()

Dim intAge as Integer ' the function returns an integer
Dim datAge as Date

datAge = Me.Q1
intAge = GetAge(datAge)
' or intAge = GetAge(Me.Q1)

Select Case intAge
Case Is < 40
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
Case Is > 74
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Sub

Thanks for the help.
I seem to be closer but may still have some logic not right.
Supposed to kick them to the ineligible pg if younger then 40 or older
then 74
Seems to work ok on the 40 end but not so good on the 74 end.
does this function work "Exact"?
here is what I have now

Function GetAge(dtmBD As Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + (Date < DateSerial(Year(Date),
Month(dtmBD), Day(dtmBD)))
End Function
Private Sub Q1_AfterUpdate()

txtAge = GetAge(Q1)

Select Case txtAge
Case Is < 39
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
Case Is > 75
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Sub

see any issues with the logic or is the function not precise

Thanks
dave

I was helped here awhile back with a select case issue and got it fixed
(Thanks)

Private Sub Q1_AfterUpdate()
Dim age

age = DateDiff("d", [Q1], Date) / 365
Select Case age
Case Is < 40
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Case Is > 72
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Out of Age Range"
End Select
End Sub


However it was pointed out that this only give the correct answer ½ the
time.
A solution was generously provided.

Function GetAge(dtmBD as Date) As Integer
GetAge = DateDiff("yyyy", dtmBD, Date) + _
(Date < DateSerial(Year(Date), Month(dtmBD), Day(dtmBD)))
End Function

Basically, this counts on the fact that a True expression has a value of
-1, and so if the current date is less than the birthdate value in the
current year, it subtracts one from the year difference between the two.

If you're using Access 95 or 97, use a Date variable rather than a
variant.

--Ken

GetAge = DateDiff("yyyy", dtmBD, Date) +(Date < DateSerial(Year(Date),
Month(dtmBD),Day(dtmBD)))


I just don't know how to really implement it.

Can anyone point me in the right direction on how to use this in my
above code.

Any help here will be appreciated.

Thanks in advance
Dave