Access 2003. Hi I need to construct a query that selects multiple
criteria to return but do not want to hard wire the criteria into the
query. Can I use a reference to a table instead? The real world
purpose is to see who a job is assigned to and group the seperate
names under an umbrella name "Maintenance"

Thanks in advance

DonH

On Dec 28, 2:14*pm, donh <donhar... (AT) gmail (DOT) com> wrote:
Although Ive done an initial search for an answer to my question,
searching some more shows Ive got to give more detail.

I have a hidden form which records who has looged into database. This
form stays open but hidden so I can use it to set criteria (if log in
= Maintenance display all records for maintenance). Thats fine until
I get to Admin which wants to see everyones records. I have been
trying to use an IIF statement to check who logged in and again thats
fine for maintenance but have failed miserably in trying to return the
FALSE which I would like in this case to be return all records (any
jobs that are assigned to Maintenance, Admin, Manager, Retail or named
users).

Hopefully thats clearer. Any help would be appreciated
DonH

On 28/12/2011 14:45:18, donh wrote:
I prefere to use a login form that opens a treeview type Menu. The Menu table
is joined to the User table so that the menu only displays Sub Menus, forms &
reports that the logged on user is allowed to see. In addition, the forms can
be set to read only
Phil

If we knew what you have in detail, and something less vague than "multiple
criteria", perhaps someone could be of more help. If you have a table of
jobs and related "people assignments", with a more-or-less normalized
relational database design, it's possible that "regular, ordinary queries"
could give the results you desire.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"donh" <donhartuk (AT) gmail (DOT) com> wrote

On Dec 28, 2:14 pm, donh <donhar... (AT) gmail (DOT) com> wrote:
Although Ive done an initial search for an answer to my question,
searching some more shows Ive got to give more detail.

I have a hidden form which records who has looged into database. This
form stays open but hidden so I can use it to set criteria (if log in
= Maintenance display all records for maintenance). Thats fine until
I get to Admin which wants to see everyones records. I have been
trying to use an IIF statement to check who logged in and again thats
fine for maintenance but have failed miserably in trying to return the
FALSE which I would like in this case to be return all records (any
jobs that are assigned to Maintenance, Admin, Manager, Retail or named
users).

Hopefully thats clearer. Any help would be appreciated
DonH

On Dec 29, 12:01*am, "Access Developer" <accde... (AT) gmail (DOT) com> wrote:
Larry,

Thanks for comments but I had hoped I had given more detail in my
follow up post and my search of the Internet shows Im not the only
person to have come accross this and so far without resolution.

Although I wish to expand this to incude other departments I want to
create a select query that returns information based on who logged in.

At present there are two choices Im concerned with. "Maintenance" that
will just return jobs that are assigned to the Maintenance team and
Admin which will show all jobs. To achieve this I tried using an IIF
statement in the Criteria

IIF( Forms![frmUserLoggedIn]![UserLoggedIn] ="Maintenance
Team","Maintenance Team","*")

The Maintenance Team of course works but I have tried all suggestions
I can find for the FALSE outcome to return an unfiltered list.

Hope that provides the detail required to gain a solution.

Thanks in anticipation.

DonH

On Dec 28, 5:48*pm, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
Thanks for your input Phil

On 29/12/2011 08:17:13, donh wrote:
Try

IIF( ( Forms![frmUserLoggedIn]![UserLoggedIn] ="MaintenanceTeam","Maintenance
Team", UserLoggedIn)

Phil

Try this

WHERE (<yourcriteriafield> = Forms![frmUserLoggedIn]![UserLoggedIn]) OR
(Forms![frmUserLoggedIn]![UserLoggedIn] = "Admin")

It should filter by who is logged in, unless they are admin. Then all
records will be returned.

--

AG
Email: npATadhdataDOTcom


"donh" <donhartuk (AT) gmail (DOT) com> wrote

On Dec 29, 12:01 am, "Access Developer" <accde... (AT) gmail (DOT) com> wrote:
Larry,

Thanks for comments but I had hoped I had given more detail in my
follow up post and my search of the Internet shows Im not the only
person to have come accross this and so far without resolution.

Although I wish to expand this to incude other departments I want to
create a select query that returns information based on who logged in.

At present there are two choices Im concerned with. "Maintenance" that
will just return jobs that are assigned to the Maintenance team and
Admin which will show all jobs. To achieve this I tried using an IIF
statement in the Criteria

IIF( Forms![frmUserLoggedIn]![UserLoggedIn] ="Maintenance
Team","Maintenance Team","*")

The Maintenance Team of course works but I have tried all suggestions
I can find for the FALSE outcome to return an unfiltered list.

Hope that provides the detail required to gain a solution.

Thanks in anticipation.

DonH

On Dec 29, 1:09*pm, "agiamb" <NOSPAMagi... (AT) newsgroup (DOT) nospam> wrote:
Many thanks for this now does as I needed it to!

"donh" wrote in message
news:01a59d2d-f4c5-4e5d-9530-a83ec7ae7b4c (AT) n6g2000vbg (DOT) googlegroups.com...

That is a very good idea. You do not want ANY criteria in the query at all.

As you are finding out, putting forms! expression in queries can get really
messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignored.

I could probably write another 10 or pages as to why putting forms
expressions in queries is bad (besides...it makes the queries real ugly, and
hard to read. and, the sql then is not standard anymore (it will not work
with server based systems either).

So the solution is simply to take the values from the form, and build your
own where clause IN CODE.

That way, you simply design the reports (or forms), and attached them to the
query, BUT NO FORMS! conditions are placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

Take a look at the following screen shots to see what I mean:

http://www.kallal.ca/ridesrpt/ridesrpt.html

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = " & cboSalesRep & ""

end if

select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = " & cobCity & ""
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want.
Lets say we have a check box to only include Special Customers. We can add
to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

So in your case, remove all of the forms! refs in the query. Simply in your
code that launches the form create the desired where clause. You not only
have control of the conditions you supply, but you can thus choose the
desired filtering with ease, and not be beholden to one give expression in
the query that as noted messes up the query anyway.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com