Can somebody help me? convert vba code to function

N

Nino

Pls. Do you know how to convert vba code function, into a query function to
use in a text field in a a report?

Thenks in advance.
br

Nino
 
N

Nino

Pls. Do you know how to convert vba code function, into a query function to
use in a text field in a report?

Thanks in advance.
br

Nino
 
R

Rick Brandt

Nino said:
Can you give me an example?
Click to expand...

Function AddOneSample(inValue as Long) as Long
AddOneSample = inValue + 1
End Function

In a query I can now use...

NewField: AddOneSample(SomeNumberField)

....just as I would be able to use a built in function like DateAdd() or CStr(),
etc..
 
N

Nino

I'm sorry, but I don't understand very well.
Below, there is the vba code:
It runs good. But now my problemis: How can I write the in a function to
use in a text field on a report?

Re-thanks.

Nino


'**********************************
Option Compare Database
Dim ggCom
Dim MyVal
Dim MyMsg As String
Dim cntCol, NumRec As Integer
Dim Esci As Boolean

Sub CalcGGDso()
On Error GoTo CalcGGDso_Err

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim rsCurr As DAO.Recordset

'---------------------------------------------------------------
'Apre il database e le tabelle necessarie
'---------------------------------------------------------------

Set db = CurrentDb
Set rst1 = db.OpenRecordset("tblDatiReport", dbOpenDynaset)

NomeTab = rst1.Name
NumRec = 0

rst1.MoveFirst

' ---------------------------------------
' lettura dati
' ---------------------------------------
Do Until rst1.EOF

Esci = False
ggCom = 0

Cliente = rst1("sdan8")
RagSoc = rst1("abalph")
Saldo = rst1("saldo")
SaldoS = rst1("saldos")
SaldoN = rst1("saldon")

''''MyMsg = MsgBox("Codice " + cliente, vbInformation)

If rst1("saldo") = 0 Then
GoTo ProssimoRec
Else
If rst1("saldo") < 0 Then
GoTo ProssimoRec
Else
If rst1("saldos") < 0 Or rst1("saldon") < 0 Then
ggCom = "-1"
GoTo ProssimoRec
End If
End If
End If

'''

If rst1("NS") = "S" Then
If rst1("SCIVA") > 0 Then
MyVal = rst1("saldos") - (rst1("saldos") / 100) * rst1("SCIVA")
Else
MyVal = rst1("saldos")
End If
Else
If rst1("SCIVA") > 0 Then
MyVal = rst1("saldon") - (rst1("saldon") / 100) * rst1("SCIVA")
Else
MyVal = rst1("saldon")
End If
End If

cntCol = 1

Do While Not cntCol > 14
MyVal = MyVal - rst1("Col" & cntCol)
If MyVal < 0 And Not Esci Then
If rst1("Col" & cntCol) <> 0 Then
ggCom = ggCom + (((rst1("Col" & cntCol) - (MyVal * -1)) /
rst1("Col" & cntCol)) * Day(rst1("DataCol" & cntCol)))
cntCol = 15
Esci = True
Else
ggCom = ggCom + 0
End If

Else
ggCom = ggCom + Day(rst1("DataCol" & cntCol))
cntCol = cntCol + 1
End If
Loop

ProssimoRec:
rst1.Edit
rst1("GGDso") = ggCom

' aggiorna e incrementa il contatore
'
rst1.Update
NumRec = NumRec + 1

rst1.MoveNext

Loop

MyMsg = MsgBox("Update Giorni DSO in " + NomeTab + " completata
correttamente!", vbInformation)
MyMsg = MsgBox("Aggiornati num. " & NumRec & " recs. in tabella " + NomeTab
+ "!", vbInformation)


Set tdfCurr = Nothing
Set dbCurr = Nothing

CalcGGDso_Exit:
Exit Sub

CalcGGDso_Err:
MsgBox Error$
Resume CalcGGDso_Exit
End Sub
'**********************************
 
P

Pieter Wijnen

Just add a freference to it in the Report_Open Event Procedure

ie

Private Sub Report_Open(Cancel As Integer)
CalcGGDso()
End Sub

HtH

Pieter
 
Top