Problem with user prompt

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bruce Lawrence - 29 Nov 2006 18:27 GMT

I want to prompt the user for a specific ID number or use a * for all
records at the time of running the report

So in my criteria for the employee ID field I put "= @empid"

In the report the input box is there. If I put in an ID number it
works great. However if I put in an asterisk "*" nothing shows up.

Any ideas?

Reply to this Message

Andrei - 29 Nov 2006 19:09 GMT

Your where clause should be written like this:

where
(isnull(@EmpID, '*') = '*')
or ((isnull(@EmpID, 0) <> 0) and (EmpID = @EmpID))

this lets your users enter a value for EmpID (then the second part of the
where clause will deal with it)
or leave the parameter field empty - then all their records will be selected

You could also replace the '*' with a '' (empty string) - this way, if the
users leave the parameter field blank, it will behave like a '*'.

Andrei.

>I want to prompt the user for a specific ID number or use a * for all
> records at the time of running the report
[quoted text clipped - 5 lines]
>
> Any ideas?

Reply to this Message

Bruce Lawrence - 29 Nov 2006 19:28 GMT

Ok you lost me a little bit.

Here is my current where clause.

WHERE (pshstj.workday BETWEEN @StartDate AND @EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX')) AND (pshstj.empid =
@ClockNo)

The "@clockno" section is where I prompt them for a clock number.
If I put a * it doesn't work. If I put a good number in it works fine.

Where does your 'where' clause fit into this?

> Your where clause should be written like this:
>
[quoted text clipped - 20 lines]
> >
> > Any ideas?

Reply to this Message

Topher - 29 Nov 2006 20:43 GMT

I'd write an IIF statement (either in a stored procedure, or in the
Report Query):

IF @clockno = '*'
BEGIN

select ....
from ...
where (pshstj.workday BETWEEN @StartDate AND @EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX'))

END

ELSE
BEGIN

select ..
from ..
where (pshstj.workday BETWEEN @StartDate AND @EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX')) AND (pshstj.empid =
@ClockNo)

END

> Ok you lost me a little bit.
>
[quoted text clipped - 35 lines]
> > >
> > > Any ideas?

Reply to this Message

Andrei - 29 Nov 2006 22:30 GMT

WHERE (pshstj.workday BETWEEN @StartDate AND @EndDate)
AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX'))
AND
(
(isnull(@ClockNo, '*') = '*')
or
( (isnull(@ClockNo, '*') <> '*') and (pshstj.empid =
lockNo) )
)

> Ok you lost me a little bit.
>
[quoted text clipped - 37 lines]
>> >
>> > Any ideas?

Reply to this Message

Bruce Lawrence - 30 Nov 2006 16:19 GMT

Andrei,

I'm not sure how... but it works.

I love you

Reply to this Message