HELP...Query runs different in Access 2000

[brett]

Here is my SQL statement:
SELECT IMAGING_BATCH_T.SCAN_OPER, IMAGING_BATCH_T.SCAN_DT,
IMAGING_BATCH_T.PROCESS_NM,
IMAGING_BATCH_T.PAGES_PER_BATCH_CNT,
[Contact/Division].Contract, [Contact/Division].Division
FROM IMAGING_BATCH_T INNER JOIN [Contact/Division] ON
IMAGING_BATCH_T.PROCESS_NM = [Contact/Division].Process_NM
WHERE (((IMAGING_BATCH_T.SCAN_OPER)=UserIDStyle()) AND
((IMAGING_BATCH_T.SCAN_DT)=[Enter the date (mm/dd/yy):]));

What I am having problems with is I have one of the query
fields calling a module that I wrote. The problem is that
the module is being called twice instead of once; what
bugs me is I made the same query in Access 97 and it only
runs once at the end of the query but in Access 2000, the
module is being run twice (once at the beginning and
another time at the end). I originally just converted the
Access 97 database to the Access 2000 database and I
noticed this started happening in my queries. I created
another query within Access 2000 doing the same thing and
it reacts the same way. How can I stop this module from
being run twice? Am I calling it wrong in the query?

 

[Gary Walter]

Hi Brett,

If you are talking about the routine
"UserIDStyle()" in your WHERE
clause, I believe it is actually running
*3* times (because of the parameter).

Place a Debug.Print in your routine, say

Debug.Print "Executing once."

Run your query, then check the
Immediate Window in Debug
(ALT + F11).

I bet you see 3 lines of your print stmt.

Remove the parameter and repeat.

Without the parameter it WILL only
run once.

One suggestion would be to move to
a query-by-form method. As you build
the query SQL, you can also provide the
result of your routine at the same time so
you are guaranteed it runs only once.

I believe you will find a good example(s)
on Roger Carlson's website:

http://www.rogersaccesslibrary.com


While it does not cover your specific situation,
it may be worth checking out:

ACC2000: Number of Times a Custom Function Runs in a Query
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q210554

(Maybe one of the MVP's could have this added
to the KB article?)

Happy Holidays,

Gary Walter