Can someone help me get this code to work?

[Guest_imported]

I've been trying for a week to get the code listed below to work. I keep getting an Undefined function 'Other'error (3085). When I get the error message, and I press the 'Debug' button, the Set statement is highlighted.

Set RSOth = DbOth.OpenRecordset("SELECT COUNT([DetailLines].[AccNum])
AS OTH1
FROM [DetailLines] WHERE (((([DetailLines].[quadrant])=1))
and ((Others([DetailLines].[CD Code])=TRUE)))&quot
====================================================
Private Function Others(invar As String) As Boolean

Select Case invar
Case "ANML", "HO", "LTWL", "OBJ", "OFF", "OTH",
"OTRN", "PC", "PED", "RA", "RALT", "RART",
"RE", "RTOR", "RTWL", "SSOD", "SSSD"
Others = True
Case Else
Others = False
End Select

End Function
=====================================================
If this won't work, I'd appreciate any alternative suggestion.

 

[tcurtis]

A little bit more details as to what you are trying to do with this code....

 

[UncleJack]

I would suggest you take your recordset statement:

"SELECT COUNT([DetailLines].[AccNum])
AS OTH1
FROM [DetailLines] WHERE (((([DetailLines].[quadrant])=1))
and ((Others([DetailLines].[CD Code])=TRUE)))"

and paste it into the SQL view of a new query to see if it will compile. If you paste it into SQL and then try to switch view to Design view and get an error it may tell you what's wrong.

Uncle Jack

 

[Guest_imported]

UncleJack, I'm not sure how to do this and still use the 'Other' function.
======================================================
tcurtis, What I want to accomplish is that when the 'Other' function is called, it will return a TRUE value if the conditions of the first CASE statement are met (otherwise a value of FALSE will be returned). Those values that are contained in the first CASE statement come from a larger list. I'm trying to create a query that will only list the records that meet the criteria that I specify.

I've done this using other applications before, but, I'm still learning VBA.

 

[UncleJack]

Can you use the IIF function to to set the true/false value? Or can you write a specific function that returns true/false and use that as criteria in your query?

Uncle Jack

 

[Sawatzky]

dear gkj,

change the word private in front of your function to public.

it seems that it is just not found because the query executing is another object than the place where you defined the function.

regards astrid