Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Public Function in a query???? (Access 97)

    Hi,

    I have a table that has a field called "Prob of Closing", what I need to do is create a second field (I think via a query) that will base its value on the "prob of closing" field. Example

    10-30% (prob of closing)=intro (in the new field called prob code)
    31-50%=general
    51-75%=High
    76%-99%=Projected

    I know how to do this in a form (using a select case stmt), but understand that you cannot do this (after update) in a query. Someone told me that I needed to use a public function, and then link the query. Easier said than done for me, I am completely lost? Has anyone ever done this before? and if so where would I put the code to point to my query.

    I hope this does not sound as confused as I am!!

    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Function in a query???? (Access 97)

    I am not sure that I would add another field to the table. If all you want to do is show the text equivalent of the percent ranges then I will do that in the form, as you have already done, or use the same approach in a Report.

    Jack

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Function in a query???? (Access 97)

    To make a long story short, all of this would be generated via a report. I am more comforatable w/ forms and queries than reports, as I am new to them.

    Can I create a text box on the report, similar to the way I would do it on a form?

    What I would do on the form is a "Select Case" in the "After Update" section of Prob of Closing Field, or is there another way I would have to do this in a report?

    Thanks Jack

  4. #4
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Function in a query???? (Access 97)

    There are a couple of ways that come to mind that you can do this . One is to use a Select Case statement in the On Print event of your report. Another way would be to create a 'lookup table' with two fields. The percent field and the equivalent text field. In a query you can add your table and your lookup table, joined on the percent field, and get your Text equivalent that way.

    HTH,
    Jack

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Function in a query???? (Access 97)

    Perhaps you can make use of the relatively unknown Partition( ) function? I don't believe you can find it in the Access Help menu, but you should be able to in the VBA Help (w/ database open, hit Alt-F11 to open code window, then choose Help).
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Function in a query???? (Access 97)

    Yes you can make a new field in a query that uses a case statement. I have a query that uses the following case statement. You should be able to adapt it to your needs.

    Put the following code in a module; name it something like Maths.

    Open up the query design grid and select the tables, queries you want to work on.

    Right click in a new grid on the line marked Field. Select the item

  7. #7
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Function in a query???? (Access 97)

    Shane,

    The Partition function sounds interesting, I have no idea what it is, therefore, I am dying of curiousity to learn it. I will look for it under "help" to see how it can work in my situation.

    Rupert, your suggestion is exactly what I want to do. I thought about it more last night, and I will be grouping my reports based on this new field that I create using the "Select Case" stmt. If I create the query before making up a report, I am sure the grouping will be much easier for me.

    Of course, I am not getting this data until the end of December. It is actually going to be a database based on our intranet, and we all know how messy reports that are printed off of the web can be. I am just trying to do some of the grunt work now, because I have a feeling that once this database is up and running my phone will be ringing off the wall with confused new users!!!

    Thanks to all who replied, you helped to save me from my New Years Hangover, and I'm not talking of the one you get from alcohol <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I will let you know how it goes.

Posting Permissions

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