employee total work periods

[mas123]

Thank you for this great forum in the affairs of Ms Access
I'm doing a program to calculate the total length of service of staff and I have one problem and it's very light.
It is related to the function add_diffs in the Vba code.
.....
What is required?
Why the result of the function is not properly if we give it the result of another function (empDsum)
And appear correctly if you give it the text, which the function (empDsum) produces only??!!
This is in the sub test

thanks for all your help
Mas

 

[vbaInet]

Three things we need from you:

1. Copy and paste the function here, enclosing it in Code tags -->
http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015
2. Explain what it does
3. and tell us what you are trying to do.

 

[mas123]

Thanks vbaInet
in the attached file there is many functions and one procedure (sub test())

Function date_diff(oldd As Date, newd As Date) As String

to calculate the years and months and days between two dates.

Function empDsum(fld As String) As String

to gather many records from field of the query , this field a result of using date_diff function.

Function add_diffs(ParamArray strDiffs() As Variant) As String

to extract the numbers from the text results from the empDsum function and sum the years, month and days for all work periods.

the sub test is to test the results
my question is:
why the result of

 Debug.Print add_diffs(empDsum("1"))

deffer from the result of this

Debug.Print add_diffs("07 years 07 months 10 days", "-01 years 00 months 00 days", "-00 years 08 months 28 days", "01 years 03 months 24 days")

although the text passed to add_diffs function is the result of the function

empDsum("1")

that's all
thanks for your desire to help
Mas

 

[spikepl]

Now the only thing missing is the actual code of the function or functions pasted in here, as asked for in #2.

 

[mas123]

Now the only thing missing is the actual code of the function or functions pasted in here, as asked for in #2.

the actual code of the function or functions in the attached file called mas_add_date_diffs.zip in the first post

can't you see it??!!
or you can't solve the problem??!!

never mind
I'll try to get what I want
thanks for your great efforts

 

[vbaInet]

We need to know what you are trying to accomplish. It's easier than trying to debug your code.

Also, like previously asked, copy and paste the functions on here. We are helping many people on this site so we don't have time to download everybody's database unless it's absolutely necessary.

 

[mas123]

this is the code

Option Compare Database

Function date_diff(oldd As Date, newd As Date) As String
Dim years, months, days As Integer
years = Year(newd) - Year(oldd)
If Month(newd) < Month(oldd) Then
years = years - 1
months = (Month(newd) + 12) - Month(oldd)
Else
months = Month(newd) - Month(oldd)
End If
If Day(newd) < Day(oldd) Then
months = months - 1
days = (Day(newd) + 30) - Day(oldd)
Else
days = Day(newd) - Day(oldd)
End If
date_diff = Format(years, "00") & " years " & Format(months, "00") & " months " & Format(days, "00") & " days"
End Function
Function add_diffs(ParamArray strDiffs() As Variant) As String
Dim yy, mm, dd, years, months, days As Integer
Dim dif  As Variant
For Each dif In strDiffs()
'dif = CStr(dif)
yy = IIf(Left("" & dif & "", 1) = "-", GetNumber("" & dif & "", 0) * -1, GetNumber("" & dif & "", 0))
mm = IIf(Left("" & dif & "", 1) = "-", GetNumber("" & dif & "", 1) * -1, GetNumber("" & dif & "", 1))
dd = IIf(Left("" & dif & "", 1) = "-", GetNumber("" & dif & "", 2) * -1, GetNumber("" & dif & "", 2))
years = years + yy
If (months + mm) > 12 Then
years = years + ((months + mm) \ 12)
months = (months + mm) Mod 12
ElseIf (months + mm) < 0 Then
years = years - 1
months = months + mm + 12
Else
months = months + mm
End If
If (days + dd) > 30 Then
months = months + ((days + dd) \ 30)
days = (days + dd) Mod 30
ElseIf (days + dd) < 0 Then
months = months - 1
days = days + dd + 30
Else
days = days + dd
End If
Next
add_diffs = Format(years, "00") & " years " & Format(months, "00") & " months " & Format(days, "00") & " days"
End Function
Function GetNumber(str As String, n As Integer) As Integer
Set regex = CreateObject("vbscript.regexp")
regex.Global = True
regex.Pattern = "\d{2}"
GetNumber = regex.Execute(str)(n)
End Function
Function empDsum(fld As String) As String
Dim rst As New ADODB.Recordset, cnn As ADODB.Connection, difs As Variant
Set cnn = CurrentProject.Connection
strQuery = "SELECT empname, startdt, enddt, IIf(worktype=2, ""-"" & date_diff([startdt],[enddt]), date_diff([startdt],[enddt])) AS addif FROM tbl1 WHERE empname=" & fld & ";"
rst.Open strQuery, cnn
Do While Not rst.EOF
difs = difs & """" & rst![addif] & """, "
rst.MoveNext
Loop
Set rst = Nothing
Set cnn = Nothing
'Debug.Print Left(difs, Len(difs) - 2)
empDsum = "" & Left(difs, Len(difs) - 2) & ""
'empDsum = add_diffs(Left(difs, Len(difs) - 2))
End Function
Sub test()
Debug.Print add_diffs(empDsum("1"))
Debug.Print add_diffs("07 years 07 months 10 days", "-01 years 00 months 00 days", "-00 years 08 months 28 days", "01 years 03 months 24 days")
End Sub

but the query records in the table in the attached file
so you need to open the attached file to understand why the result is different in the sub test()

 

[mas123]

any body here??!!

 

[spikepl]

Learn more and read more, you will be as prophets. allah said : oh Yahya , take the book strongly.

This is a technical forum for technical issues - please take politics and religion elsewhere.

 

[mas123]

what about the vba code??!!
Do not worry about these things.
Why the different result of the function if it passed the text or have passed the output of another function??!!

 

[mas123]

Thanks for all.
I solved the problem.

 

[vbaInet]

any body here??!!

It's the weekend. We're not here 24/7.

 

[vbaInet]

Thanks for all.
I solved the problem.

Happy to hear.

 

[smig]

We're not here 24/7.

Aren't you ?
LOL

 

[vbaInet]

Aren't you ?
LOL

Almost