Parameter Query - Wildcard?

Question

I have a parameter query which, simply put, asks for a salesman code.  So say there are 3 salesmen, I have to produce a report for each salesman - i.e. a total of 3 reports.  What happens if I want the same report for all salesmen grouped together - is there a wildcard character I could use when prompted for a salesman code that would bring all salesmen data up?

Answer 1

You could change the Criteria for the salesman field to

Like [Enter Salesman Code]

(substituting your own text for the parameter prompt) and enter * if you want to include all salesmen.

Or change the Criteria to

[Enter Salesman Code] Or [Enter Salesman Code] Is Null

(again substituting your own text), and leave the prompt empty if you want to include all salesmen.

Answer 2

Fixed it, there were errors in my data.  The like clause in the criteria box works well and it enables you to enter a salesman to get just their records or all salesman using % at the parameter prompt.  Thank-you

Answer 3

yes kcode is the product group field.  The report is not grouped on kcode, I guess I shall try that.

However, I have been testing the differences between using like and = and am getting a difference for salesman code 02 and its a really odd one as within the query there are results missing which look identical to those that are included and I cannot work out why.

sql for stq1 is:-

PARAMETERS [Enter Year] Long, [Enter Month] Long, [Enter Rep Code] Text ( 10 );

SELECT

"InvoicedThisYear" AS WhichYear,

IOT.pgrp, IIF(IOT.type="bulk" Or IOT.type="bag",IOT.qtydel,0) AS InvoicedTonnesThisYear,

IIF(IOT.type="each",IOT.qtydel,0) AS InvoicedUnitsThisYear,

IOT.qtydel*IOT.sprice AS InvoicedValueThisYear,

0 AS InvoicedTonnesLastYear,

0 AS InvoicedUnitsLastYear,

0 AS InvoicedValueLastYear,

0 AS CreditedTonnesThisYear,

0 AS CreditedUnitsThisYear,

0 AS CreditedValueThisYear,

0 AS CreditedTonnesLastYear,

0 AS CreditedUnitsLastYear,

0 AS CreditedValueLastYear,

IOT.name

FROM IOT

WHERE IOT.idate BETWEEN DateSerial([Enter Year],[Enter Month],1)

AND DateSerial([Enter Year],([Enter Month]+1),0)

AND IOT.kcode LIKE [Enter Rep Code];

UNION ALL

SELECT

"InvoicedLastYear",

IOT.pgrp,0,0,0,

IIF(IOT.type="bulk" Or IOT.type="bag",IOT.qtydel,0),

IIF(IOT.type="each",IOT.qtydel,0),

IOT.qtydel*IOT.sprice,

0,0,0,0,0,0,

IOT.name

FROM IOT

WHERE IOT.idate BETWEEN DateSerial(([Enter Year]-1),[Enter Month],1)

AND DateSerial(([Enter Year]-1),([Enter Month]+1),0)

AND IOT.kcode LIKE [Enter Rep Code]

UNION ALL

SELECT

"CreditedThisYear",

COT.pgrp,0,0,0,0,0,0,

IIF(COT.type="bulk" Or COT.type="bag",COT.qtyadj,0),

IIF(COT.type="each",COT.qtyadj,0),

COT.qtydel*COT.sprice,

0,0,0,

COT.name

FROM COT

WHERE COT.ddate BETWEEN DateSerial([Enter Year],[Enter Month],1)

AND DateSerial([Enter Year],([Enter Month]+1),0)

AND COT.kcode LIKE [Enter Rep Code]

UNION ALL SELECT

"CreditedLastYear",

COT.pgrp,0,0,0,0,0,0,0,0,0,

IIF(COT.type="bulk" Or COT.type="bag",COT.qtyadj,0),

IIF(COT.type="each",COT.qtyadj,0),

COT.qtydel*COT.sprice,

COT.name

FROM COT

WHERE COT.ddate BETWEEN DateSerial(([Enter Year]-1),[Enter Month],1)

AND DateSerial(([Enter Year]-1),([Enter Month]+1),0)

AND COT.kcode LIKE [Enter Rep Code];

My parameters were 2013 and 07 for rep 02.

I think it is including all the lines from table IOT as the differences are found when you look at the COT results.  The lines from table COT it should have included are below, but it is only including the top two where I have the like clause.  Where I have the = clause it is including all the lines.  I don't understand!

ddate	qtydel	qtyadj	sprice	type	kcode	pgrp
02/07/2012	0.225	0	240	BAG	02	310
05/07/2012	5.78	0	14	BULK	02	280
11/07/2012	3.12	0	94	BULK	02	SOT
23/07/2012	2	2	49.5	BULK	02	SBG