Results 1 to 6 of 6
  1. #1
    quidlat
    Guest

    Creating an updateable criteria in a query

    Hi guys! Can anyone help me out with this?

    I have a table whose records each contain staff member id, course number, hours, and month id. Each staff member teaches a course every month. The hours indicate the total number of hours he/she has taught that course in the given month.

    I need to have query that can somehow sumarize the total number of hours taugh for a given range of course numbers. For example, if i run the query, it must have a pop up box that will ask for an open range of course numbers (for example course numbers 100-106). Then upon pressing enter it must display the total number of hours of the set of indicated course numbers.

    Normally, I can get the query to do this by entering [Enter course number] under the criteria row of the Course Number field. However, I dont know what to do when the criteria has to be able to accept an open range of course numbers.

    I really need any ideas. Thanks so much, I love you all! *sniff, sniff

    chris

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an updateable criteria in a query

    The really simple solution is to enter <font color=008080>Between 100 And 106 </font color=008080> in the Criteria row. That's assuming it will always be a range and not a selection of numbers.

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

    Re: Creating an updateable criteria in a query

    Now, now, don;t get mushy on us! You could use two parameters and put an expression something like this in the criteria line of the query grid:

    Between [Enter first course number of range] And [Enter last course number of range]

    That should evaluate properly and give your your range input.
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an updateable criteria in a query

    Yes maam, that's true. What I meant was, there'd be forms and code and stuff required if say you wanted 100 and 103 and 108 ...

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an updateable criteria in a query

    If a maximum of 5 courses would be used, you could enter the parameter five times one time under the other in the query for the criteria to prompt for the course number 5 times. When you run the query, if you only had 4 course numbers to enter you would simply have to press enter in lieu of entering the 5th number.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Creating an updateable criteria in a query

    Hi,
    I take it you want people to be able to input say 103, 106, 108-109, 123 etc. all at once? Then yes, I think you need code and a form to get the course numbers and run the code from. To do it, I'd define the delimiter (e.g. a comma), use InStr in a loop to parse the input and create the WHERE clause of your SQL then use a db.execute to run it. You might be able to use the IN operator in your clause rather than multiple OR statements, though I'm not sure that would work with a range.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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