Undefined Function error - Complex

[David]

Hello all, Not sure where to start so.....When I use the function described
at the bottom, I receive error message:
Undefined function 'fGetCaseType_RULE' in expression.

I have This SQL code to parse out a CLOB field to multiple rows.

select trim(replace(rule_text,';',chr(10))) as "content"
from table_rule
where table_rule.operation = 'DISPATCH'

I save it as a text file. Access links to this table fine. I am trying to
create a function that I can use in a Query that will parse out data in this
field.

Public Function fGetCaseType_RULE(strIn) As String

Dim iPos1 As Long
Dim iPos2 As Long
Dim len1 As Long
Dim strReturn As String

iPos1 = InStr(1, strIn, "'") 'start of Level1

iPos2 = InStr(iPos1 + 1, strIn, "'") 'end of level1

If iPos1 = 0 Then
'field is Null Attribute does not exist
Else
'Trim off all the stuff before the attribute we are
'looking for
len1 = iPos2 - iPos1

strReturn = Mid(strIn, (iPos1 + 1), (len1) - 1)
fGetCaseType = strReturn

End If

End Function



In my Query I have

testagain: fGetCaseType_RULE([Field1])

Receive error: Undefined function 'fGetCaseType_RULE' in expression.


Thank you in advance for your assistance.

David

 

[Clifford Bass]

Hi David,

Perhaps the problem is that it will not compile? Or it results in an
error condition? I see that you have "fGetCaseType = strReturn" at the end,
which really should be "fGetCaseType_RULE = strReturn".

You can have Access point these kinds of errors out to you by using:

Option Explicit

At the beginning of your module placed before or after the Option
Compare... line.

The other possible issue is that you do not have the function inside a
regular module. If it is in a form or report module, the query tool will not
be able to find it.

Clifford Bass

 

[David]

Thank you.

Hi David,

Perhaps the problem is that it will not compile? Or it results in an
error condition? I see that you have "fGetCaseType = strReturn" at the end,
which really should be "fGetCaseType_RULE = strReturn".

You can have Access point these kinds of errors out to you by using:

Option Explicit

At the beginning of your module placed before or after the Option
Compare... line.

The other possible issue is that you do not have the function inside a
regular module. If it is in a form or report module, the query tool will not
be able to find it.

Clifford Bass

Hello all, Not sure where to start so.....When I use the function described
at the bottom, I receive error message:
Undefined function 'fGetCaseType_RULE' in expression.

I have This SQL code to parse out a CLOB field to multiple rows.

select trim(replace(rule_text,';',chr(10))) as "content"
from table_rule
where table_rule.operation = 'DISPATCH'

I save it as a text file. Access links to this table fine. I am trying to
create a function that I can use in a Query that will parse out data in this
field.

Public Function fGetCaseType_RULE(strIn) As String

Dim iPos1 As Long
Dim iPos2 As Long
Dim len1 As Long
Dim strReturn As String

iPos1 = InStr(1, strIn, "'") 'start of Level1

iPos2 = InStr(iPos1 + 1, strIn, "'") 'end of level1

If iPos1 = 0 Then
'field is Null Attribute does not exist
Else
'Trim off all the stuff before the attribute we are
'looking for
len1 = iPos2 - iPos1

strReturn = Mid(strIn, (iPos1 + 1), (len1) - 1)
fGetCaseType = strReturn

End If

End Function



In my Query I have

testagain: fGetCaseType_RULE([Field1])

Receive error: Undefined function 'fGetCaseType_RULE' in expression.


Thank you in advance for your assistance.

David

 

[Clifford Bass]

Hi David,

You are welcome. I hope that helped.

One final thing. Make sure your module name is not the same as the
function name (fGetCaseType_RULE). This can create problems in certain
instances.

Clifford Bass