Parameter that can also give all records

  • Thread starter Thread starter Heather
  • Start date Start date
H

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
 
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.
Click to expand...
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 <--
 
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


Stefan Hoffmann said:
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.
Click to expand...
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 <--
Click to expand...
 
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.
Click to expand...
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 <--
Click to expand...
Click to expand...
 
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
'====================================================
 
Thank you - this helped a lot!!!
--
Heather


John Spencer said:
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
Click to expand...
Click to expand...
 
Back
Top