DateDiff

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to decifer how long a client has been using our services. I have a calculation in our form, but I am slowly converting my forms from table driven to query driven so that ages and service lengths will always be accurate

Dim BaseMonths As Intege
Dim EmpYears As Intege
Dim EmpMonths As Intege
Dim LengthHolder As Strin
BaseMonths = DateDiff("m", Me.Svc_Start, Date
EmpYears = BaseMonths \ 1
EmpMonths = BaseMonths Mod 1
Select Case EmpYear
Case
LengthHolder = "
Case
LengthHolder = "1 yr
Case Els
LengthHolder = EmpYears & " yrs
End Selec
LengthHolder = LengthHolder & EmpMonth
Select Case EmpMonth
Case Is <
LengthHolder = LengthHolder & " mo
Case Els
LengthHolder = LengthHolder & " mos
End Selec
Me.Svc_Length = LengthHolde

This is the code behind the event on my form but it doesn't work in the report that I am setting up. ???????

I also tried

=DateDiff("yyyy", [Birthdate], Now())+ Int( Format(now(), "mmdd") < Format( [Birthdate], "mmdd")

But that only yields years and I would like the year, mo format

Bail me out again Pretty Please ! ;)
 
Create and use a function in a standard module. You can then use this
function as a control source like:
=GetServYrMth([YourDateField])

Function GetServYrMth( pdatStart as Date) as String
Dim BaseMonths As Integer
Dim EmpYears As Integer
Dim EmpMonths As Integer
Dim LengthHolder As String
BaseMonths = DateDiff("m", pdatStart, Date)
EmpYears = BaseMonths \ 12
EmpMonths = BaseMonths Mod 12
Select Case EmpYears
Case 0
LengthHolder = ""
Case 1
LengthHolder = "1 yr "
Case Else
LengthHolder = EmpYears & " yrs "
End Select
LengthHolder = LengthHolder & EmpMonths
Select Case EmpMonths
Case Is < 2
LengthHolder = LengthHolder & " mo"
Case Else
LengthHolder = LengthHolder & " mos"
End Select
GetServYrMth = LengthHolder
End Function

--
Duane Hookom
MS Access MVP

René said:
I am trying to decifer how long a client has been using our services. I
Click to expand...
have a calculation in our form, but I am slowly converting my forms from
table driven to query driven so that ages and service lengths will always be
accurate.
Dim BaseMonths As Integer
Dim EmpYears As Integer
Dim EmpMonths As Integer
Dim LengthHolder As String
BaseMonths = DateDiff("m", Me.Svc_Start, Date)
EmpYears = BaseMonths \ 12
EmpMonths = BaseMonths Mod 12
Select Case EmpYears
Case 0
LengthHolder = ""
Case 1
LengthHolder = "1 yr "
Case Else
LengthHolder = EmpYears & " yrs "
End Select
LengthHolder = LengthHolder & EmpMonths
Select Case EmpMonths
Case Is < 2
LengthHolder = LengthHolder & " mo"
Case Else
LengthHolder = LengthHolder & " mos"
End Select
Me.Svc_Length = LengthHolder

This is the code behind the event on my form but it doesn't work in the
Click to expand...
report that I am setting up. ????????
I also tried

=DateDiff("yyyy", [Birthdate], Now())+ Int( Format(now(), "mmdd") < Format( [Birthdate], "mmdd") )

But that only yields years and I would like the year, mo format.

Bail me out again Pretty Please ! ;)
Click to expand...
 
Hello again

I created the module, but have something out of kittywampus somewher

On my report in the [Svc Length] Field I have the following

=GetServYrMth([Svc Start]

I am getting an error msg as for the parameter for GetServYrMt

Help -- Thanks ahea
 
You shouldn't have a Svc Length field on the report. I would expect that you
would have a text box with a control source as you suggested. This code
assumes you will always have a date in the field Svc Start.
 
Baby step

I named the module GetServYrMt

I created a text box and put in the control source =GetServYrMth([Svc Start]

No all records in the table have start dates because not all clients/perspective clients have started service . Does the not all records having a start date screw up the module

This report is set to auto print from command button filter to the current client form that is opened.

Help
 
First of all, the module must not have the same name as the function. This
is another good reason why we have naming conventions. Most modules are
saved with names like "modDateFuntions" or "basDateFunctions".
If it is possible that the date field might be null, then change your
function:
Function GetServYrMth( pdatStart as Variant) as String
If IsNull(pdatStart) Then
GetServYrMth = "unknown"
Exit Function
End If
'previous code lines


--
Duane Hookom
MS Access MVP


René said:
Baby steps

I named the module GetServYrMth

I created a text box and put in the control source =GetServYrMth([Svc Start])

No all records in the table have start dates because not all
Click to expand...
clients/perspective clients have started service . Does the not all records
having a start date screw up the module?
 
Thanks for the compliment. It makes a nice birthday present. I'll have to
tell my kids!
 
Back
Top