Function not known!

  • Thread starter Thread starter PayeDoc
  • Start date Start date
P

PayeDoc

Hello All

I have the function below in a module, but when I try to use it in a query I
get a message "Undefined function ConvertDays in expression", or when I try
to use it on a form it just returns "#error".

The sql of the query is:
SELECT staffs.practice, staffs.[ssp days], ConvertDays([ssp days]) AS Expr1
FROM staffs
WHERE (((staffs.practice)=[Forms]![frm x main]![prac name]));

The function in the module is:
Public Function ConvertDays(Days As String) As String
Dim inc As Integer
For inc = 1 To 7
If Mid(Days, inc, 1) = "Y" Then
ConvertDays = ConvertDays & inc
End If
Next inc
End Function

What have I done wrong?
Hope someone can help.
Many thanks
Leslie Isaacs
 
Hi,
try this:

Public Function ConvertDays(Days As String) As String
Dim inc As Integer, strResult as string
For inc = 1 To 7
If Mid(Days, inc, 1) = "Y" Then
strResult = strResult & inc
End If
Next inc
ConvertDays=strResult
End Function

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
 
Hello Alex

Thanks for your reply.

Unfortunately, replacing my code with yours had no effect - I get exactly
the same problem.

Curiously, if I use the expression-builder to create the field in the query,
I can select the module that contains the ConvertDays function, and then I
can see ConvertDays listed there: but when I select the function and add the
parameter 'ssp days' and try to run the query, it doesn't recognise the
function!

How can that be?

Hope you can help.
Thanks again
Les


Alex Dybenko said:
Hi,
try this:

Public Function ConvertDays(Days As String) As String
Dim inc As Integer, strResult as string
For inc = 1 To 7
If Mid(Days, inc, 1) = "Y" Then
strResult = strResult & inc
End If
Next inc
ConvertDays=strResult
End Function

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


PayeDoc said:
Hello All

I have the function below in a module, but when I try to use it in a query
I
get a message "Undefined function ConvertDays in expression", or when I
try
to use it on a form it just returns "#error".

The sql of the query is:
SELECT staffs.practice, staffs.[ssp days], ConvertDays([ssp days]) AS
Expr1
FROM staffs
WHERE (((staffs.practice)=[Forms]![frm x main]![prac name]));

The function in the module is:
Public Function ConvertDays(Days As String) As String
Dim inc As Integer
For inc = 1 To 7
If Mid(Days, inc, 1) = "Y" Then
ConvertDays = ConvertDays & inc
End If
Next inc
End Function

What have I done wrong?
Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
 
You didn't happen to name the module ConvertDays, did you?

Modules cannot have the same names as functions or subs.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


PayeDoc said:
Hello Alex

Thanks for your reply.

Unfortunately, replacing my code with yours had no effect - I get exactly
the same problem.

Curiously, if I use the expression-builder to create the field in the
query,
I can select the module that contains the ConvertDays function, and then I
can see ConvertDays listed there: but when I select the function and add
the
parameter 'ssp days' and try to run the query, it doesn't recognise the
function!

How can that be?

Hope you can help.
Thanks again
Les


Alex Dybenko said:
Hi,
try this:

Public Function ConvertDays(Days As String) As String
Dim inc As Integer, strResult as string
For inc = 1 To 7
If Mid(Days, inc, 1) = "Y" Then
strResult = strResult & inc
End If
Next inc
ConvertDays=strResult
End Function

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


PayeDoc said:
Hello All

I have the function below in a module, but when I try to use it in a query
I
get a message "Undefined function ConvertDays in expression", or when I
try
to use it on a form it just returns "#error".

The sql of the query is:
SELECT staffs.practice, staffs.[ssp days], ConvertDays([ssp days]) AS
Expr1
FROM staffs
WHERE (((staffs.practice)=[Forms]![frm x main]![prac name]));

The function in the module is:
Public Function ConvertDays(Days As String) As String
Dim inc As Integer
For inc = 1 To 7
If Mid(Days, inc, 1) = "Y" Then
ConvertDays = ConvertDays & inc
End If
Next inc
End Function

What have I done wrong?
Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
Hello Douglas

No: the module is - a little unimaginatively - called "Module5"!

What else could be going on here?
Les


Douglas J. Steele said:
You didn't happen to name the module ConvertDays, did you?

Modules cannot have the same names as functions or subs.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


PayeDoc said:
Hello Alex

Thanks for your reply.

Unfortunately, replacing my code with yours had no effect - I get exactly
the same problem.

Curiously, if I use the expression-builder to create the field in the
query,
I can select the module that contains the ConvertDays function, and then I
can see ConvertDays listed there: but when I select the function and add
the
parameter 'ssp days' and try to run the query, it doesn't recognise the
function!

How can that be?

Hope you can help.
Thanks again
Les


Alex Dybenko said:
Hi,
try this:

Public Function ConvertDays(Days As String) As String
Dim inc As Integer, strResult as string
For inc = 1 To 7
If Mid(Days, inc, 1) = "Y" Then
strResult = strResult & inc
End If
Next inc
ConvertDays=strResult
End Function

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Hello All

I have the function below in a module, but when I try to use it in a query
I
get a message "Undefined function ConvertDays in expression", or when I
try
to use it on a form it just returns "#error".

The sql of the query is:
SELECT staffs.practice, staffs.[ssp days], ConvertDays([ssp days]) AS
Expr1
FROM staffs
WHERE (((staffs.practice)=[Forms]![frm x main]![prac name]));

The function in the module is:
Public Function ConvertDays(Days As String) As String
Dim inc As Integer
For inc = 1 To 7
If Mid(Days, inc, 1) = "Y" Then
ConvertDays = ConvertDays & inc
End If
Next inc
End Function

What have I done wrong?
Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
Anything else named ConvertDays in your database?

If you go to the Immediate Window (Ctrl-G) and type, say,

?ConvertDays("YNYNYNY")

then hit Enter, do you get 1357?
 
Hello Douglas

Thanks for your suggestion: and of course you are right!!
Somehow I had a duplicate 'module5' - each with function ConvertDays: so far
from the function not being defined, it was in fact defined twice!!
Having got rid of the duplication, all is well.

Many thnaks once again.
Les
 
Back
Top