Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Two optionboxes (Access 2000)

    What is the best method to handle 2 option boxes? The first box contains 12 options and is called cities.The second option box contains 4 options and is called grpSummary. I must first choose city and then the one of the summaries from the second option box

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

    Re: Two optionboxes (Access 2000)

    What do you want to do with the items selected in the option boxes?

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two optionboxes (Access 2000)

    I am defining the record source of the form.The first option group is called cities.The second is called grpSummary.The first row of the grpSummary is called decrease . When choosing option 1 from cities and option 1 from grpSummary I get:

    Dim StrSQL As String
    StrSQL = " SELECT *" & _
    " FROM qryQuarterSalesSo " & _
    " WHERE (((qryQuarterSalesSo.[4])<[3]))"

    When choosing option 2 from the first and option 1 from the second I get:
    StrSQL = " SELECT *" & _
    " FROM qryQuarterSalesVa " & _
    " WHERE (((qryQuarterSalesVa.[4])<[3]))"

    regards


    Freelance

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

    Re: Two optionboxes (Access 2000)

    So what exactly is your question?

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two optionboxes (Access 2000)

    How can i refer to both options? Is there a way to simplify the reference to the first option for example something like Me!cities and work with the second option ? Because the six rows in the seocnd option box must be repeated for each city in a roll. I must work with the second option box after i have chosen a city

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

    Re: Two optionboxes (Access 2000)

    Do you really need to use a different query for each city? It would be easier if you could specify the city in the WHERE part of the SQL.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two optionboxes (Access 2000)

    Yes, at first i didnt want to use a different query for each sql.The problem is that when i tried to use only one query i didnt get the expected results.At first i made a common query called qryQuarterSales. On clicking the first city , for example, i get the rught list of customers for that city. The problems are with the second option.The first option is to find to customers with a decreased liters for 4th quarter as compared with 3th quarter.But the results are not true.
    I am sending you a very short example containing only customerid = 906. In the 3th quarter he has 36 liters and for the 4th quarter he has 135 liters. Therefore this customer should not be shown in the list for customers with decreased quantities.However, if you click on the diamond, aimed to show this list for option 1 of the cities( afid) , then this customer appears. Therefore i have some error somewhere. Of course if you could help me find the error i wil be happy to work with only one query

    Kind regards

    Freelance
    Attached Files Attached Files

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

    Re: Two optionboxes (Access 2000)

    The problem is that the value in the crosstab query is a text value. Change the expression for the value field to
    <code>
    SumOfLiters: Val(Nz(Som([order details].liters),0))
    </code>
    (Use ; instead of , if your system uses the comma as decimal separator)

  9. #9
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two optionboxes (Access 2000)

    You are so great Hans ! Of course it works and i will not need having 12 queries, i will have one and i thank you very much for that !

Posting Permissions

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