Problem with user prompt

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?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.
"Bruce Lawrence" <BL32375@.gmail.com> wrote in message
news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
>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?
>|||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?
Andrei wrote:
> 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.
>
> "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
> news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
> >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?
> >|||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
Bruce Lawrence wrote:
> 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?
>
> Andrei wrote:
> > 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.
> >
> >
> > "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
> > news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
> > >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?
> > >|||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) )
)
"Bruce Lawrence" <BL32375@.gmail.com> wrote in message
news:1164828482.158109.84050@.j44g2000cwa.googlegroups.com...
> 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?
>
> Andrei wrote:
>> 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.
>>
>> "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
>> news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
>> >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?
>> >
>|||Andrei,
I'm not sure how... but it works.
I love you