Modifying an Access Pass through Query using Querydefs

[NJC59]

Hi there!

I have looked high and low in order to try and resolve this, but am getting nowhere.

I have a pass through query which I want to add a parameter to the end.

The pass through query is very long and has to crate temporary tables on SQL Server in order to generate the required output.

I do not want to retype or try and paste all of this code into a vba module.

Can I use querydefs or something in the querydefs collection to extract the code into a new query and append my parameter along the way?

A simple way of looking at this could be

querytemplate:-

"set nocount off



Select UWREF
from policy
where UWREF like '"

parameter:- (picked up from a text box on a form)
"ABC123"

additional text to follow:-
"%' set nocount on"

In a nutshell that's all I want to do, but as stated beforehand the real world query has a shed load of other code.

This is driving me nuts as I can only seem to find examples where the code is manually entered!

Any help pointers would be greatly appreciated as part of the problem here maybe information overload and I can't believe this that difficult to achieve!

Cheers,

Niven

[June7]

If you use LIKE, need wildcard. This is a SELECT sql in VBA procedure? Policy is the pass-through query? Something like:

"Select UWREF
from policy
where UWREF like '*" & Me.textboxname & "*'"

If you use a combobox and user selects a specific item, can use:
Where UWREF = '" & Me.textboxname & "'"