Refering to calculated fields in a visual basic function

[Guest]

I have a visual basic function (below) that I am trying to use in a query.
I am trying to refer to the values in the calculated fields Startwk And
Leftwk but the function does not provide any results only 0's.

If I change the field names to values e,g, 10 and 38 the function seems to
work.

Do I have to refer to the fields in a special way?

Many thenaks in advance for any help.

ian


Public Function CountOnestest(Field10 As String) As Integer
Dim iPos As Integer
Dim GTiwk As Integer
CountOnestest = 0
GTiwk = 6
For iPos = 1 To Len(Field10)
If GTiwk >= Startwk And GTiwk <= Leftwk Then
If Mid(Field10, iPos, 1) = "1" Then
CountOnestest = CountOnestest + 1
End If
End If
GTiwk = GTiwk + 1
Next iPos
End Function

 

[Guest]

Hi,
If the "Startwk" and "Leftwk" are columns in your table that you are trying
to reference to. Your method will not work. They will be treated as
variables in your function and evaluated to either null or 0.
However, if you pass the "Startwk" and "Leftwk" as part of parameters that
you pass to your function. Then it will work.

Like:
CountOnestest(Field10, Startwk, Leftwk) when calling your function

Public Function CountOnestest(Field10 As String, Startwk as variant, Leftwk
as variant) As Integer
Dim iPos As Integer
Dim GTiwk As Integer
CountOnestest = 0
GTiwk = 6
For iPos = 1 To Len(Field10)
If GTiwk >= Startwk And GTiwk <= Leftwk Then
If Mid(Field10, iPos, 1) = "1" Then
CountOnestest = CountOnestest + 1
End If
End If
GTiwk = GTiwk + 1
Next iPos
End Function


Hope this helps.

 

[ian]

Many thanks, works perfect

ian

-----Original Message-----
Hi,
If the "Startwk" and "Leftwk" are columns in your table that you are trying
to reference to. Your method will not work. They will be treated as
variables in your function and evaluated to either null or 0.
However, if you pass the "Startwk" and "Leftwk" as part of parameters that
you pass to your function. Then it will work.

Like:
CountOnestest(Field10, Startwk, Leftwk) when calling your function

Public Function CountOnestest(Field10 As String, Startwk as variant, Leftwk
as variant) As Integer
Dim iPos As Integer
Dim GTiwk As Integer
CountOnestest = 0
GTiwk = 6
For iPos = 1 To Len(Field10)
If GTiwk >= Startwk And GTiwk <= Leftwk Then
If Mid(Field10, iPos, 1) = "1" Then
CountOnestest = CountOnestest + 1
End If
End If
GTiwk = GTiwk + 1
Next iPos
End Function


Hope this helps.



.