Change The Formula - How and where?

[WhatTheFormula]

Hello Everyone,



so this is the situation:

I have a Access-File (.mdb).
Basically what it does is it takes numbers (time values) from a server and calculates the average.
Now what i want it to do for example is to add 10 minutes to that.

I searched the file for one month and tried to get help from various people -> No success.

To the specifics and the process:

There is a form where i can choose with drop-down "Just totals" and choose the person i want the average of.
It then generates a excel-file and opens it. There are the results.

How do i change the formula "normal" + 10 min.?
Sounds simple but i could not get to it.

I'm no expert in Access (as my problem shows) so i kindly request you not to use pro-terms as i can not understand them.

I hope some of you pro's can help, just tell me what you need.

Thank you and best regards.

[RuralGuy]

In what type of field do you have the "time values"; Integer, Long, Single, Double, DateTime?
What code are you using to "generate an excel-file"?

[WhatTheFormula]

I'm very sorry i forgot to mention: I am no expert and do not understand the terms. I dont know what you mean by Integer, Long, Single, Double, DateTime.

Do you want me to post the code of the form?

[RuralGuy]

Not to worry. The code of the form would be nice. The whole db even better; removing any sensitive data of course.

[WhatTheFormula]

I can't post the db (database?). I think that would be huge and i have no access, why should i, i have no idea what i'm doing.

Here the code of the form where you can choose "Nur Totale" (Just totals).
I marked the part i think is the important one.

[Also its from Germany but you should understand the code i think. Otherwise i will post some translations at the end of every post that has german terms in it.]

Private Sub Befehl1_Click()
' bestimmen der Variablen
If ((IsNull(Me.PLAYER) And(IsNull(Me.Gruppe))) Or IsNull(Me.Startdatum) Or IsNull(Me.Enddatum))Then
MsgBox "Bitte alle Felderausfüllen!"
Exit Sub
End If


'setzen der global variablen
reportdate = Me.Startdatum.Value
reportdateend = Me.Enddatum.Value
If (Me.PLAYER.Value > "")Then
reportplayer = Me.PLAYER.Value
reportplayerid =Me.PLAYER.Column(0)
reportdid = ""
reportgruppe = ""
Else
reportplayer = ""
reportplayerid = ""
reportdid = ""
reportgruppe = Me.Gruppe.Column(0)
End If


DoCmd.Hourglass (True)
'Berechnen der Statistik-Daten
fillCallPlayerStatistik
DoCmd.Hourglass (False)


'abhängig vom Modus reagieren wirunterschiedlich
If (Me.Modus.Value = "DetailsAccess Report") Then
'Report öffnen
DoCmd.OpenReport "CallPlayer",acViewPreview
ElseIf (Me.Modus.Value = "DetailsExcel") Then
'Excel Export erstellen
makeXLS
ElseIf (Me.Modus.Value = "nurTotale") Then
' DoCmd.OpenQuery"totaleViewPlayer"
makeXLSTotale "totaleViewPlayer",Me.Startdatum, Me.Enddatum
End If

End Sub


Private Sub Form_Current()
DoCmd.Close acReport, "CallPlayer"
End Sub


Private Sub fillCallPlayerStatistik()


Dim sqlQuery As String
' Open connection
Dim Cnxn As ADODB.Connection
Set Cnxn = New ADODB.Connection
'Cnxn.Open"Provider=SQLOLEDB;Server=" & ServerIP &";Database=" & databaseName & ";UID=" &UID & ";Pwd=" & pwd
Cnxn.Open"Provider=SQLOLEDB;Server=" & ServerIP & ";UID="& UID & ";Pwd=" & pwd &";ConnectTimeout=300"




'Bestehende Datensätze aus dertemp- Tabelle löschen
Dim db, dbint As ADODB.Recordset
Set db = New ADODB.Recordset
Set dbint = New ADODB.Recordset
db.Open "DELETE FROMtmp_playlist", CurrentProject.Connection, adOpenDynamic,adLockOptimistic

'Einfügen Daten
sqlQuery = "SELECT abandon,LEFT(CallLocalTimeString,8) AS Calldate, RIGHT(CallLocalTimeString,6)AS CallTime, FirstPlayer AS ID_TV, DNIS AS DID, REPLACE(ANI,'@audiocodes.com','') AS ANI, ConvDuration AS ConvTime,WrapUpDuration AS WrapupTime, WaitDuration AS Waittime,RerouteDuration AS ExternalTime FROM HN_Ondata.dbo.ODCalls "
'#" & Jahr & "-"& i & "-1#
'str_datum = "#" &Right(Me.Startdatum.Value, 4) & "/" &Mid(Me.Startdatum.Value, 3, 2) & "/" &Mid(Me.Startdatum.Value, 1, 2) & "#"
str_datum =Right(Me.Startdatum.Value, 4) & Mid(Me.Startdatum.Value, 3, 2) &Mid(Me.Startdatum.Value, 1, 2)
If (IsNull(Me.Enddatum)) Then
end_datum = str_datum
Else
end_datum =Right(Me.Enddatum.Value, 4) & Mid(Me.Enddatum.Value, 3, 2) &Mid(Me.Enddatum.Value, 1, 2)
End If

If (Me.PLAYER.Value > "")Then
sqlQuery = sqlQuery &"WHERE INDICE>0 AND FirstPlayer='" & Me.PLAYER.Value& "' AND Left(CallLocalTimeString,8) >= '" &str_datum & "' AND Left(CallLocalTimeString,8) <= '"& end_datum & "'"
ElseIf (Me.Gruppe.Value > "")Then
sqlQuery = sqlQuery &"WHERE INDICE>0 AND Left(CallLocalTimeString,8) >= '"& str_datum & "' AND Left(CallLocalTimeString,8) <='" & end_datum & "' "
sqlQuery = sqlQuery & "ANDFirstplayer IN (SELECT PlayerId FROM HN_Admin.dbo.vwSupervisionPlayerWHERE SuperviseGroupId = " & Me.Gruppe.Value & ")"
End If

If (Me.DID.ItemsSelected.Count >0) Then
inlist = "("
For i = 0 ToMe.DID.ItemsSelected.Count - 1
If (i = 0) Then
inlist = inlist &"'" & Me.DID.ItemData(Me.DID.ItemsSelected.Item(i)) &"'"
Else
inlist = inlist &", '" & Me.DID.ItemData(Me.DID.ItemsSelected.Item(i)) &"'"
End If
Next
inlist = inlist & ")"
sqlQuery = sqlQuery & "AND DNIS IN " & inlist
End If

dbint.Open sqlQuery, Cnxn,adOpenDynamic, adLockReadOnly
Do Until dbint.EOF

sqlQuery = "INSERT INTOtmp_playlist (abandon, Calldate, Calltime, ID_TV, DID, ANI, ConvTime,Waittime, ExternalTime, WrapupTime) VALUES "
sqlQuery = sqlQuery & "("& dbint![Abandon] & ", '" & dbint![Calldate] &"','" & dbint![Calltime] & "'," &dbint![ID_TV] & ",'" & dbint![DID] & "','"& dbint![ANI] & "'"
sqlQuery = sqlQuery & ","& dbint![ConvTime] & "," & dbint![WaitTime] &"," & dbint![ExternalTime] & "," &dbint![WrapUpTime] & " );"

'MsgBox sqlQuery
db.Open sqlQuery,CurrentProject.Connection, adOpenDynamic, adLockOptimistic
dbint.MoveNext
Loop

' clean up
dbint.Close
Cnxn.Close
Set Cnxn = Nothing
End Sub


Private Sub Gruppe_Change()
If (Me.Gruppe.Value > 0) Then
Me.PLAYER.Value = ""
End If
End Sub


Private Sub Player_Change()
If (Me.PLAYER.Value > 0) Then
Me.Gruppe.Value = ""
End If
End Sub

- Startdatum = Start-date (Date of start)
- Enddatum = End-date (Date of end)

[RuralGuy]

Okay, this should work. How about posting the code for: makeXLS.

[WhatTheFormula]

Well i already tried to find anything with that name. I have nothing.
I also looked for it just because of...well..logic...but i can't find anything with the name makeXLS.

All i have is a "union query" with the name "totaleViewPlayer". But thats not what you're looking for, right?

[RuralGuy]

Did you use the Edit/Find function in any code page set to *project"?

[RuralGuy]

It is probably in a Standard Module along with another Function "makeXLSTotale"

[WhatTheFormula]

Well i just looked at the code of the module an found two things, i really hope that is what you're looking for:

1:

{...
Public Sub makeXLSTotale(view As String, startdate As String, enddate As String)
Dim rst As DAO.Recordset
Dim customQuery As String
Dim cnt As Integer

Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range

Dim fileName As String


Set appExcel = Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Add
Set wks = wbk.Worksheets(1)
Set rng = wks.Range("A3:B5")
customQuery = "SELECT * FROM " & view

If (IsEmpty(startdate) = False) Then
startdate = Mid(startdate, 1, 2) & "." & Mid(startdate, 3, 2) & "." & Right(startdate, 4)
End If
If (IsEmpty(enddate) = False) Then
enddate = Mid(enddate, 1, 2) & "." & Mid(enddate, 3, 2) & "." & Right(enddate, 4)
End If

Range("A1").Select
ActiveCell.Offset(0, 0) = "Periode: " & startdate & " bis " & enddate

Set rst = CurrentDb.OpenRecordset(customQuery)
If (rst.RecordCount > 0) Then
cnt = 1
For Each fld In rst.Fields
wks.Cells(2, cnt).Value = fld.Name
cnt = cnt + 1
Next fld
Call rng.CopyFromRecordset(rst, 4000, 26)
End If

'hier hängen wir die Formel für das Total rein
'Range("A4").Select
'Selection.end(xlDown).Select
'ActiveCell.Offset(1, 0) = "Totale:"


rst.Close
Set rst = Nothing
End Sub
...}

and 2:

{...
Public Sub makeXLS()
Dim rst As DAO.Recordset
Dim customQuery As String
Dim cnt As Integer

Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range

Dim fileName As String


Set appExcel = Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Add
Set wks = wbk.Worksheets(1)
Set rng = wks.Range("A2:B5")
customQuery = "SELECT * FROM xlsExportView"

'Spalten fghi mit entsrechendem Format versehen
'Columns("F:I").Select
'Selection.NumberFormat = "[m]:ss"

Set rst = CurrentDb.OpenRecordset(customQuery)
If (rst.RecordCount > 0) Then
cnt = 1
For Each fld In rst.Fields
wks.Cells(1, cnt).Value = fld.Name
cnt = cnt + 1
Next fld
Call rng.CopyFromRecordset(rst, 4000, 26)
End If

'hier hängen wir die Formel für das Total rein
'Range("A4").Select
'Selection.end(xlDown).Select
'ActiveCell.Offset(1, 0) = "Totale:"


'fileName = "c:\test.xls"
'wks.SaveAs (fileName)


rst.Close
Set rst = Nothing
End Sub
...}

[RuralGuy]

Yes, that is what we need. Now we just have to figure out how to pass your 10 minute offset.

[WhatTheFormula]

That would safe my life. Like i said i'm trying for good five weeks now.

[RuralGuy]

Can you find a Sub somewhere for "Call rng.CopyFromRecordset(rst, 4000, 26)

[RuralGuy]

It looks like whoever wrote this database (DB) knew what they were doing. At least so far.

[WhatTheFormula]

Where could i find such subs?