RE: using the results of a function for Criteria

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Martin J (anonmous_at_nospam.com)
Date: 12/16/04 

Date: Thu, 16 Dec 2004 14:25:01 -0800

This question has been answered many times. In query builder when you put in
'red' and 'green' for criteria it writes in sql veiw as "WHERE
(([table].[color]='red') and ([table].[color]='green'))" however when you use
a function it will look like "WHERE (([table].[color]='red or green'))" That
is why it doesn't work. You need to use where clause of the docmd.open report
or build the where clause in code.

HTH
Martin J

"John Soarz" wrote:

> Hello all,
> First off this is a great newsgroup, so keep up the good work.
>
> I am trying to have the criteria from multiple queries run a function that
> will limit the amount of information each current user has access to. I am
> able to do this if the user only have permission to one particular area, but
> when 1 user has access to muliple areas I run into problems.
>
> For example:
> The areas would be RED, GREEN, BLUE, ORANGE, YELLOW
>
> USER1 has been assigned to RED
> USER2 has been assigned to BLUE and GREEN
> USER3 has been assigned to RED, ORANGE and Yellow
>
> So what I get the function to do is group all areas together for the current
> user.
>
> For instance say it was USER2 that was logged in the "FINDAREA" function
> would retrieve the following "'BLUE' or 'GREEN'" as a string.
> Within the query I would put the following "Like FindArea()".
> This works for any user that is only assigned to 1 Area, but for users that
> more then 1 it does not work.
>
> Any help with this problem would be greatly appreciated.
>
> Thanks

Relevant Pages

  • Re: Not Exists joining 2 tables
    ... "'code' is a bad name for a key column" is a valid complaint. ... EXISTS clause with a correlated subquery properly, ... SQL+ syntax and start using the ANSI SQL syntax that seems ... Then the only criteria in the where clause ...
    (comp.databases.ms-sqlserver)
  • Re: Criteria linked to form combo Yes/No or All expression help
    ... The way I'd debug it would be to start w/ one OR clause in the criteria. ... Run the query & see if it works for that criteria. ... I followed your suggestion of pasting the sql, ...
    (microsoft.public.access.queries)
  • RE: build the where clause...
    ... Looks like you are adding a 'where' clause for every item instead of just the ... It might help to display the SQL for your query just before you execute it. ... (even though I know there are records with both search criteria used). ... With one criteria debug.print strWhere yields: ...
    (microsoft.public.access.formscoding)
  • Re: Access Group by and Count problem
    ... This criterion should be put in the WHERE clause, since it has nothing to do ... Criteria in the WHERE clause are enforced BEFORE ... GROUP BY PageVisited, StartTime ... in the grid, instead of one of the aggregation functions. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Select query populating list box
    ... Additional assumption is that txtInstitution is never null. ... Every time you add one more criteria to the where clause, ... However if you look at my original code the text control needs to search ...
    (microsoft.public.access.queries)