Query runs extremely slow because of simple user defined function

[Mrs Howl]

I have a query that just reads one table and appends to an output
table, one-for-one. No criteria. It's not a Total query (i.e. no
group by). It normally run run in minutes, but gets horribly slowed
down because five of my output fields are text fields and the
expression that gets appended is a user-defined function I wrote which
is very simple (just a few lines long). Here's the code of my
function:

Public Function tformat(num As Double, xdec As Byte) As String
If xdec = 0 Then
tformat = Right(Space(12) + Format(num), 12)
Else
tformat = Right(Space(12) + Format(num, "0." + String(xdec,
"0")), 12)
End If
End Function

In each of the 5 expressions in my query grid, the parameters I pass
are:
num: a numeric field from the query's input table
xdec: a numeric literal constant.
The above function looks fairly simple, but it causes my query which
otherwise would run in a few minutes to take many times that long to
finish (maybe an hour). Do any of you understand why, or maybe have a
helpful suggestion about a trick I could use to speed up my query?

[Ben Eaton]

I'd be interested to see the SQL behind your query, that may have something
to do with it.

Assuming however that this is a VBA problem - given what your function
actually does I think the best way to do this would be to do away with the
custom function altogether and do the whole thing in SQL using an IIf
statement.
IIf(xdec=0,Right(Space(12)+Format([num]),12),Right(Space(12)+Format([num],"0
.."+String(xdec,"0")),12))

(remembering of course to replace the xdec with your literal)

May work, may not work, suck it and see...

"Mrs Howl" <ti********@yahoo.com> wrote in message
news:5b*************************@posting.google.co m...

I have a query that just reads one table and appends to an output
table, one-for-one. No criteria. It's not a Total query (i.e. no
group by). It normally run run in minutes, but gets horribly slowed
down because five of my output fields are text fields and the
expression that gets appended is a user-defined function I wrote which
is very simple (just a few lines long). Here's the code of my
function:

Public Function tformat(num As Double, xdec As Byte) As String
If xdec = 0 Then
tformat = Right(Space(12) + Format(num), 12)
Else
tformat = Right(Space(12) + Format(num, "0." + String(xdec,
"0")), 12)
End If
End Function

In each of the 5 expressions in my query grid, the parameters I pass
are:
num: a numeric field from the query's input table
xdec: a numeric literal constant.
The above function looks fairly simple, but it causes my query which
otherwise would run in a few minutes to take many times that long to
finish (maybe an hour). Do any of you understand why, or maybe have a
helpful suggestion about a trick I could use to speed up my query?

[Mrs Howl]

False alarm... I've figured it out. If the visual basic window is
open while the query is running, it drastically slows down the query.
I aborted the query, close the visual basic window and reran. It ran
much faster. Why this should make a difference, I don't know.