MS Query - Wildcard 'Like' with a parameter

[Smithsc]

Hi there,
This is my first post so appologies if it's in the wrong forum.
I have an MS Query that downloads data from a SQL server. One of the fields contains (amonst other things) an agreement number and I want to be able to select the agreement number at run time.

The criteria I'm using looks like this:
Like ('%' + [Enter Agreement No] + '%')
The query runs but produces zero records.

If I change the criteria to read:
Like('%' + '75196' + '%')
The query runs and produces over 750 records.

Can somebody please tell me what is wrong.

Many Thanks

 

[SkipVought]

Hi,

Cannot use a parameter with the LIKE operator.

However, it is possible with VBA code. Post your question in forum707, if you want to go this route.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Think this is a data type issue - you are having a number entered but need it to be a string to perform the wildcard search

Any chance you could convert the query to VBA rather than using MS Query...that way you can control the parameter data defintion better...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question