Parameter that can also give all records

[Heather]

Hello,

I want the user to select a department but also have the ability to be given
all departments in 1 Prompt.

[Enter A Department] ** If they want ALL then I just what to tell them hit
enter for all departments.

Thanks

 

[Stefan Hoffmann]

hi Heather,

I want the user to select a department but also have the ability to be given
all departments in 1 Prompt.

[Enter A Department] ** If they want ALL then I just what to tell them hit
enter for all departments.

This may work (untested):

Create your parameter using the menu. Add your condition to the
department column. Switch to SQL view, there you should find something like:

SELECT ..
FROM yourTable
HERE (departmentField = [parameterName])

Add an extra condition after the WHERE and before your parameter equals
field name like this:

WHERE ((Len(Trim([parameterName])) = 0) OR (departmentField =
[parameterName]))



mfG
--> stefan <--

 

[Heather]

I do not understand your solution.

I remember seeing it done with a wild Character within the critera of the qry.

[Enter Department A or B for ALL Department just hit enter] but I do not
remember the formatt.

Maybe that is what you are telling me but I do not understand it.

--
Heather

hi Heather,

I want the user to select a department but also have the ability to be given
all departments in 1 Prompt.

[Enter A Department] ** If they want ALL then I just what to tell them hit
enter for all departments.

This may work (untested):

Create your parameter using the menu. Add your condition to the
department column. Switch to SQL view, there you should find something like:

SELECT ..
FROM yourTable
HERE (departmentField = [parameterName])

Add an extra condition after the WHERE and before your parameter equals
field name like this:

WHERE ((Len(Trim([parameterName])) = 0) OR (departmentField =
[parameterName]))



mfG
--> stefan <--

 

[Bob Barrows]

When looking at a row of data from your table in order to decide whether or
not to include it in its results, it will first look at the parameter value.
If the parameter is empty, then this will be true:
((Len(Trim([parameterName])) = 0)
and this will be false:
(departmentField = [parameterName]))

Since only one side of a boolean OR expression needs to be true, the row
will be accepted.

On the other hand, if the parameter does contain a value, this will be
false:
((Len(Trim([parameterName])) = 0)

So if will look at this to decide whether or not to include the row:
(departmentField = [parameterName]))

If it is true, the row will be included. If not, the row will be rejected.

Clear?

I do not understand your solution.

I remember seeing it done with a wild Character within the critera of
the qry.

[Enter Department A or B for ALL Department just hit enter] but I do
not remember the formatt.

Maybe that is what you are telling me but I do not understand it.

hi Heather,

I want the user to select a department but also have the ability to
be given all departments in 1 Prompt.

[Enter A Department] ** If they want ALL then I just what to tell
them hit enter for all departments.

This may work (untested):

Create your parameter using the menu. Add your condition to the
department column. Switch to SQL view, there you should find
something like:

SELECT ..
FROM yourTable
HERE (departmentField = [parameterName])

Add an extra condition after the WHERE and before your parameter
equals
field name like this:

WHERE ((Len(Trim([parameterName])) = 0) OR (departmentField =
[parameterName]))



mfG
--> stefan <--

 

[John Spencer]

Assuming that Department is a TEXT field AND always has a value you can use.

Like Nz([Enter a department (blank for all)],"*")

Or you can use
Like [Enter a department (blank for all)] & "*"

The latter can give you unexpected results if you have departments like
AA1
AA12
AA13
AC

And the users enters AA1 they will also get AA12 and AA13. On the other
hand, if you wanted all the A departments, entering A will get them all.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Heather]

Thank you - this helped a lot!!!
--
Heather

Assuming that Department is a TEXT field AND always has a value you can use.

Like Nz([Enter a department (blank for all)],"*")

Or you can use
Like [Enter a department (blank for all)] & "*"

The latter can give you unexpected results if you have departments like
AA1
AA12
AA13
AC

And the users enters AA1 they will also get AA12 and AA13. On the other
hand, if you wanted all the A departments, entering A will get them all.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello,

I want the user to select a department but also have the ability to be given
all departments in 1 Prompt.

[Enter A Department] ** If they want ALL then I just what to tell them hit
enter for all departments.

Thanks