How to replace EQUAL TO by LIKE in a Query involving variables

Dear All,

I am working on a Query which is in reality 3 Queries in 1. It works perfectly.

When I run the Query, it gives the following Query - Selection Criteria

Query - Selection Criteria
-------------------------------------------------------
Import Log                        ...................
Block Number                      ...................

Document Date                     ...................
Document Date                     ...................


[OK]   [Cancel]

To use this Query, we must choose only 1 criterion and leave other fields blank. To achieve this, I

use OR inside WHERE

1ST Query

If you type S713 in the first field, it returns all records with Import Log = S713

2 ND Query

If you type G1575 in the 2ND field, it returns all records with Block Number = G1575

3rd Query

If you type '01.07.11' and '05.07.11' in the 3RD and 4TH fieldS, it returns all records with

Dates between 01.07.00 and 05.07.11

The Query won't work if you fill in all fields.

WHAT I WANT?

I want to replace the EQUAL TO in the WHERE clause by LIKE to allow the Query to search for a pattern, instead

of rendering an exact match.

I know this can be done with = '[%1]' by changing it to LIKE '%[%1]%'

But how can you do the same with @shipnum and @reqnum?

I can change the variable declarations to

set @shipnum = /* */ '%[%1]%'

but it creates problems.

How can we use LIKE in the WHERE clause, without modifying the declarations.

Thanks

Leon Lai

.

.

.

Here's my Query

/*
TABLES:
T0 = OPCH A/P Invoice - Header
T1 = PCH1 A/P Invoice - Rows
T5 = OJDT Journal Entry
*/

declare @shipnum nvarchar (30)
set @shipnum =
/*select T1.[ImportLog] from [dbo].[PCH1] T1 where T1.[ImportLog]*/ '[%1]'

declare @reqnum nvarchar (30)
set @reqnum =
/*select T1.[BlockNum] from [dbo].[PCH1] T1 where T1.[BlockNum]*/ '[%2]'

declare @taxdt1 datetime
set @taxdt1=
/*select T0.[TaxDate] from [dbo].[OPCH] T0 where T0.[TaxDate]*/ '[%3]'

declare @taxdt2 datetime
set @taxdt2=
/*select T0.[TaxDate] from [dbo].[OPCH] T0 where T0.[TaxDate]*/ '[%4]'




SELECT
T0.[TaxDate] AS 'Doc Dt',
T1.[ImportLog] AS 'Ship #',
T1.[BlockNum] AS 'Reqn #',
T0.[CardName] AS 'Supplier Name',
T0.[DocTotal] AS 'Rs'

FROM [dbo].[OPCH] T0
INNER JOIN [dbo].[PCH1] T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN [dbo].[OJDT] T5 ON T0.[TransID] = T5.[TransID]

WHERE

(T1.[ImportLog]  = @shipnum  AND 
@reqnum = '  ' AND
@taxdt1  = '  ' AND
@taxdt2  = '  ' )

OR

(T1.[BlockNum] = @reqnum  AND 
@shipnum = '  ' AND
@taxdt1  = '  ' AND
@taxdt2  = '  ' )

OR

(T0.[TaxDate] >= @taxdt1 AND
T0.[TaxDate] <= @taxdt2 AND
@shipnum = ' ' AND
@reqnum = ' ' )