Trying to use a custom function in an update query

  • Thread starter Thread starter Hanif Merali
  • Start date Start date
H

Hanif Merali

Hello I'm trying to use a custom function that I've writtin within an
update query. The function takes in a string as a parameter and
ouputs a string. I saved the function in a module called Formatting.
When I open the update query in design view and fill in all the update
to field with
FormatUnitNum([fieldname]) it says function does not exist. Am I
supposed to save the function somewhere else or call the module
somethign else?
Here is what the code looks like

Public Function FormatUnitNum(strAddress As String) As String
Dim strTokens As Variant
Dim numTokens As Integer
Dim output As String

strTokens = Split(strAddress, " ")
numTokens = UBound(strTokens)

output = strTokens(numTokens) + "-"

For i% = 1 To numTokens - 1
output = ouput + " " + strTokens(j%)
End Sub


FormatUnitNum = output

Return

End Function

Thanks in advance for your help.

Sincerly,
H.M.
 
Hello I'm trying to use a custom function that I've writtin within an
update query. The function takes in a string as a parameter and
ouputs a string. I saved the function in a module called Formatting.
When I open the update query in design view and fill in all the update
to field with
FormatUnitNum([fieldname]) it says function does not exist. Am I
supposed to save the function somewhere else or call the module
somethign else?
Here is what the code looks like

Public Function FormatUnitNum(strAddress As String) As String
Dim strTokens As Variant
Dim numTokens As Integer
Dim output As String

strTokens = Split(strAddress, " ")
numTokens = UBound(strTokens)

output = strTokens(numTokens) + "-"

For i% = 1 To numTokens - 1
output = ouput + " " + strTokens(j%)
End Sub
Click to expand...

You're looping on i% but using j% as your subscript - Access won't
know what j is!

I'd suggest a) Dim'ing all your variables; b) using Option Explicit
before the name of the function to *force* you to Dim all your
variables; and c) if you haven't done so, select Debug... Compile
<project> to catch all compile errors before trying to execute the
code.
 
Hello I'm trying to use a custom function that I've writtin within an
update query. The function takes in a string as a parameter and
ouputs a string. I saved the function in a module called Formatting.
When I open the update query in design view and fill in all the update
to field with
FormatUnitNum([fieldname]) it says function does not exist. Am I
supposed to save the function somewhere else or call the module
somethign else?
Here is what the code looks like

Public Function FormatUnitNum(strAddress As String) As String
Dim strTokens As Variant
Dim numTokens As Integer
Dim output As String

strTokens = Split(strAddress, " ")
numTokens = UBound(strTokens)

output = strTokens(numTokens) + "-"

For i% = 1 To numTokens - 1
output = ouput + " " + strTokens(j%)
End Sub


FormatUnitNum = output

Return

End Function

Thanks in advance for your help.

Sincerly,
H.M.
Click to expand...

The function should be placed in a Module.
The Module name should not be the same as the function name.

Also, as written, your code wont compile.
.... The lines that include i% and j% ..
Neither i% nor j% have been Dimmed.
(I don't think you actually need j.)

You use the variable 'output' then later mis-spell the word as
'ouput'.

Then you have "End Sub" written within the function, where Next i
should be.

And then "Return" is stuck in there for some reason.

As written your function doesn't work.

You could have caught this by simply using
Option Explicit
in the Declarations section of the code window.

What is it you are trying to accomplish?
Let us have a few examples of the data and what you want the result of
the function to be.
 
Back
Top