Getting a custom function to work in a criteria

[fawilson]

Hello all,

I have written the following function and I want to use it in the
criteria of a query. However, when placing "BaseFacility()" without
quotes in the [FAC_ID] field's criteria nothing is returned.

Please help. Thank you.

Fred


Function BaseFacility() As String
Dim frmReport As Form
Set frmReport = Forms("frmReports")
Dim dblBinary As Double
dblBinary = 0

If frmReport!cboFacility = "[All Facilities]" Then
If frmReport!chkMUTC = -1 Then dblBinary = dblBinary + 1
If frmReport!chkJSTEC = -1 Then dblBinary = dblBinary + 2
If frmReport!chkSTRENGTH = -1 Then dblBinary = dblBinary + 4

Select Case dblBinary
Case 0
BaseFacility = "Like '*'"
Case 1
BaseFacility = "Not like 'M_*'"
Case 2
BaseFacility = "Not like 'J_*'"
Case 3
BaseFacility = "Not like 'M_*' AND not like 'J_*' AND not
like '*STRENGTH*'"
Case 4
BaseFacility = "Not like '*STRENGTH*'"
Case 5
BaseFacility = "Not like 'M_*' AND not like '*STRENGTH*'"
Case 6
BaseFacility = "Not like 'J_*' AND not like '*STRENGTH*'"
Case 7
BaseFacility = "=Not like 'M_*' AND not like 'J_*' AND not
like '*STRENGTH*'"
End Select

Else
BaseFacility = frmReports!cboFacility
End If

End Function

 

[Rob Parker]

Try "=BaseFacility()" (without the quotes) in the criteria field of the
query.

HTH,

Rob

 

[fawilson]

Yes Sir, I tried that and it did not work. That one really stumped me.

Thank you,

Fred

 

[Rob Parker]

Sorry Fred,

I tried the equivalent of what you posted (regarding the syntax for the
criteria in a query, and referring to a function to provide the criteria
entry) in a test database and it worked for me. I don't have any other
suggestions - except perhaps to re-post, since other potential responders
may think this has already been answered.

Again, sorry I couldn't help,

Rob

 

[fawilson]

Rob,

Heck I thought it should've worked like that too.

Stumped.

Fred

 

[Rob Parker]

Fred,

If you care to send me a copy of your database (can be stripped down to only
include the relevant tables, queries, forms, module), I'll take a look at it
and see if I can come up with anything. No promises, but I'll give it a
shot - and I'm intrigued by it not working; like you, I think it should.
You can get my email address by removing the obvious antispam bits from my
posting address, which is (e-mail address removed)

Rob

 

[David Cox]

There are only a limited number of inbuilt functions that are available to
the query builder at build time, and I do not know of a way to add custom
ones to those.

 

[Gary Walter]

I don't know if this will help, but...
to use a function you wrote in a query,
the function must exist in a code module
(not form module) and must be declared
as "Public"

Public Function BaseFacility() As String

good luck,

gary