Report that displays only "#Name?"!!! 1

[DaiDreamer]

Hi!

I'm new to the whole VBS and Access Programming thing. Can some one help me? I'm trying to count the number of times a table feild comes up with true. But when i try to display the value in the form, it only says "#Name?" in where I call the function and where I try to display something from the table.

Here's my code:


Function MineTypeFun(MineType As String) As String

Dim DBL As Database
'Dim WS As Workspace
Dim RS As Recordset

Dim Counter As Integer
Dim Counter500 As Integer

'Set WS = Workspaces(0)

Set DBL = CurrentDb()
Set RS = DBL.OpenRecordset("tblInsp99&quot
Counter = 0
Counter500 = 0
RS.Index = "My Index"
Debug.Print "Testing"
RS.MoveFirst
Do Until RS.EOF
If RS.Fields(MineType) = True Then
Counter = Counter + 1
End If
RS.NextRecordset
Loop
DBL.Close
RS.Close
'WS.Close
MineTypeFun = "Testing"
End Function

 

[nickjar2]

it would probably be easier to use the dlookup function

=dLookUp("FieldName","Table","[fieldName] = true&quot

u may have to substitue the = true with = -1

I haven't got access in front of me sorry.

Nick

 

[tempclerk]

I think Nick means DCount.

 

[nickjar2]

whoops!
Thanks tempclerk.

 

[JaneC]

Assuming the field you are looking at is Yes/No then is is a logical field so you need to change your code to only say

If RS.Fields(MineType) Then

HTH

Jane

 

[VogonPoet]

Instead of looping through each record with code, why not let the Access query engine to the work for you:

Function MineTypeFun(MineType As String) As String
Dim db as dao.database
dim rs as dao.recordset
dim strSql as string
dim intCnt as integer

set db = currentdb
strSql = "Select count(*) as cnt from tblInsp99 where (((tblInsp99.MineType)=True))
set rs = db.openrecordset(strSql, dbOpenForwardOnly)

intCnt=rs.fields("cnt&quot

MineTypeFun=Format$(intCnt,"#,##0)

rs.close
set rs = nothing
set db = nothing
End Function

(I don't have Access open so there may be some typos in the code)

The query with the count function should be much faster than looping through each record and testing the value.

I hope this is helpful.

Michael Hodes
michaelhodes@managedcare.com