Jamie,

Just seen this after replying to your other post. I think we are violently
agreeing here<vbg

Bob

"onedaywhen" wrote in message
om...
Frank, Funny, I though the OP *is* struggling with dynamic SQL ;-)

Actually, I was trying to simply things for the OP. Creating a stored
procedure can be as simple as executing a CREATE PROCEDURE statement,
and hopefully mine is a useful template. Using a stored procedure to
me seems analogous with using a VBA function with arguments i.e.
encapsulates functionality and hides complexity from the user; they
pass in the parameters and get back some values.

--

"Frank Kabel" wrote in message
...
O.K. I agree this is the way in respect to performance. But if the OP
does not want to struggle with Stored Procedures dynamic SQL would do
:-)

--
Regards
Frank Kabel
Frankfurt, Germany

onedaywhen wrote:
Rather than create dynamic SQL (which is a bit evil), create a
'stored
procedure' in your database and pass the values from your combo boxes
to the procedure as input parameters. Here's an example which takes
two dates as parameters:

CREATE PROCEDURE
MyStoredProc (
start_date DATETIME,
end_date DATETIME
)
AS
SELECT
RefID,
DateEffective,
Earnings
FROM
EarningsHistory
WHERE
DateEffective
BETWEEN start_date AND end_date;

If you are using ADO, use a command object to create the parameters
and invoke the stored proc. If you are using MS Query, here's what
should appear in the SQL window to run the above procedure with
parameters:

{Call MyStoredProc('01 JAN 2001', '01 JAN 2004')}


"Frank Kabel" wrote in message
...
Hi Charles
why don't you just build the SQL string (using text concatenation)
as
pass this string to the module / SQL Querey?

--
Regards
Frank Kabel
Frankfurt, Germany

Charles wrote:
Hi is it possible to use vba code to change text in a
module at run time I am using Excel XP, it will be in the
same file. if any body can guide in the right direction it
will much appreciated.

I am trying to write a sql statment to query a table in
MSAccess but the user can select from three Comboboxes, I
wanted to add the Where string at run time to the code.

TIA
Charles