Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Function as Criteria? (Access 2002)

    It seems to me I've seen a recent thread on this subject that indicated you can't put a user function in the criteria, as that has to be assembled as a part of the query string. Unfortunately I haven't been able to find the post. It is possible however to create and save a query using the querydef object. You would need to delete the query and then save the new one with the same name but different criteria. Would that solve the problem?
    Wendell

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function as Criteria? (Access 2002)

    Hi Wendell!

    I also thought that I had seen a similar thread (and also couldn't find it), just so you know you're not seeing things <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Creating and deleting querydefs is my backup solution. There are a few reasons why I don't like that solution. First, part of my design requirement is to be able to select a region and print out reports for all the territories in the region in succession, and I'm not quite sure what effect the querydef thing will have on that. The second one is if I go around creating and deleting about 20ish querydefs each time I want to print out a report, how fast will my database become corrupt? Since I'm doing this in the front end, I'm assuming it's not the end of the world, but I'd like to keep this as stable as possible....

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Function as Criteria? (Access 2002)

    Actually, querydefs are stored, deleted and so forth pretty quickly. Also, you can modify a QueryDef in VBA, so you could simply do that in code. As to bloat, queries don't usually take up a great deal of space in an Access database - they are typically a string, and a compiled version of the string. The other possibility would be to put the criteria on a form (presumably hidden) and reference that form with a parameter query. In that case, I believe the IN function will be executed correctly.
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Function as Criteria? (Access 2002)

    <P ID="edit" class=small>(Edited by Cecilia on 23-Mar-03 17:18. Added information)</P>Hi All!

    I'm building some summary reports that are pretty complex. Basically, they compare national data to regional data and national to territorial data (the idea is to pull comparative data as key indicators from about 10 different subsystems and display it in a single report format). As a result, in order to just build sections of these reports, I have queries that build on queries and then union all of this stuff together. You can only imagine how slow these reports already are....

    Anyway, one of the requirements of this design is that the user be able to choose which region(s) and/or territory(-ies) and run the report for the selections. Here is my problem: If my reports were based on a single query, I could write the query in code and then use functions to decide my Where statements (and thereby choose nation/region/territory on the fly). But they aren't: the query that selects the geographic area is at minimum one query down, and therefore only manageable through design grid. [The reason I have to do it this way is to union the national results with whatever geographic results to have them display correctly on the page.)

    What I'm trying to do, without success, is this: build a function, ie. getRegion() that returns a string that handles the Where statement: "in ('NY','SF')". That is easy enough to do, and I've verified that my function returns the proper string. But when I put this function in the design grid, I get no results.

    Is there a reason I can't do something like this? Has anyone else tried something similar?

    [PS. One of the earlier things that I had tried was to make my geographic query the basis for the subreport and then do DLookups for the national part. The reason that this didn't work is that there were so many Dlookups that the report opened way too slowly....And I mean HOURS (since the where part of the query is conditional on the results of the geographic query). If this is a better way to go, is there a way to speed up the Dlookups?]

    TIA for any attempt to answer this burning question,

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Function as Criteria? (Access 2002)

    I may be wrong, but I believe that the thread you were looking at was <post#=232416>post 232416</post#>.

    Changing the SQL of a query is done simply in code just as CreateQueryDef is. Because they are in the FE, and FE's are normally on your PC, even if you get corruption, you just copy the development FE to your PC.

    Pat

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Function as Criteria? (Access 2002)

    I think you are probably right, Pat! But going through that whole thread suggests I may be wrong - unfortunately I'm not going to have time to do any testing of things and find out the real scoop. Hopefully someone else will.
    Wendell

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Function as Criteria? (Access 2002)

    Hi Cecilia
    Post you SQL query here (the one with the IN in it), and we can all take a look to see what could be wrong.

    Woops, sorry Wendell, is this ok?

    Pat

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function as Criteria? (Access 2002)

    Absolutely OK, Pat. Posting SQL here in the lounge is the best way for everyone to take a look at it and see what might be going wrong.
    Charlotte

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Function as Criteria? (Access 2002)

    Not to worry, Pat. Hopefully she won't post all of her queries however - something in excess of 20 apparently.
    Wendell

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Function as Criteria? (Access 2002)

    Then it's a case of changing the SQL of the query "on the fly" or doing a CreateQuery as you mentioned earlier.
    Pat

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function as Criteria? (Access 2002)

    You can create a custom function that returns True if Region is 'NY' or 'SF' and False otherwise, and use this function in a calculated field.

    The function must be in a standard module and it can look like this:

    Function IsOK(fld) As Boolean
    Select Case fld
    Case "NY", "SF"
    IsOK = True
    End Select
    End Function

    This version uses literal values 'NY' and 'SF', but it could be made dynamic.
    In the query grid, create a calculated field

    IsOK([Region])

    (it will come out as Expr1:IsOK([Region]), but don't worry about that)

    Uncheck the display check box, and set the criteria to True. Close and save the query. Its SQL statement looks like this:

    SELECT ClientID, ClientName, Region
    FROM tblClients
    WHERE IsOK([Region]) = True;

    (replace tblClients by the name of your table)

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function as Criteria? (Access 2002)

    Hi Pat!

    That's exactly my point....There is no SQL, I was trying to do this through the design grid. As Wendell suggests, I think it just doesn't work.

    If I have a query that does Select ClientID, ClientName, Region from tblClients, in the grid I'm trying to put in the Region criteria myRegionFunction() which returns the equivalent of "in ('NY','SF')" If I make it return just "NY" or just "SF", it works fine, but when I try to return the In string, it doesn't work.

    PS. I also tried having the function return "'NY' or 'SF'" but that didn't work either.

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function as Criteria? (Access 2002)

    Hi Hans,

    So basically, I'm okay if I want to return only a value. There's no way I can return a string and pass it to the criteria. Bummer. <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    Thanks! [ <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function as Criteria? (Access 2002)

    Indeed, you can't use a function as a criterion (unless it returns a single value), because a criterion (in general) is an expression, not a string. But my example (rather, Microsoft's example, it is based on an MSKB article) shows that you *can* use a custom function to specify a WHERE condition in a sneaky way. The example only simulates IN ("NY","SF"), but since a user-defined function has access to global variables, controls on open forms etc., you can make it as complicated as you like. You have to translate the criterion to an expression that evaluates to True if the condition is met.

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function as Criteria? (Access 2002)

    Hi Pat, Charlotte, and Wendell!

    I hope that you're not miffed with me about not posting the exact queries. There's a number of reasons why I haven't. The first is that they probably wouldn't make sense unless you got to see our entire mainframe and knew our entire line of business, which is kind of messed up in itsself, since a lot of it is legacy stuff. You'd end up spending the rest of the day shaking your head muttering "why oh why" incessantly <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> The second is that most of it is confidential gov't stuff. <img src=/S/boxedin.gif border=0 alt=boxedin width=25 height=20> Finally, because I work for the gov't, even POSTING here (whether it means asking questions or trying to help out others) could potentially either get me reprimanded or fired, or at the very least, a nasty "Access Forbidden" message when I try to pull up the page. <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

    If you have any doubts, check out MarkD's post that Pat referenced earlier--It had me laughing out loud! I think the gov't has decided to use the XP environment to control us to the point where we can't even do our jobs, never mind have any creativity. <img src=/S/scold.gif border=0 alt=scold width=50 height=15>

    Anyway, I hope my examples are good enough, even if they won't show any syntax or other errors. And no, I will _not_ be driving you crazy by posting my 20-some queries here! Please be thankful!!!! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> (<---love this smilie!) And THANK YOU!!!! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> for all of your assistance, it's completely invaluable!

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •