Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2008
    Posts
    74

    Question Unanswered: Multiselect listbox & 12 Checkboxes to filter a form

    Hi, everyone!

    I need made a search form to filter my query (which is based on 5 tables). The search form contains:

    - Multiselect listbox Cost Centre
    - Multiselect listbox Cost Category
    - 12 Checkboxes each representing a month

    The user should be able to choose multiple cost centres, multiple cost categories and sum the costs for these criterias. However, costs for each single selected month should be listed separately. So, for example if the user chooses cost centres 1 and 2, cost categories a, b and c, and months october, november and december, the search output should look like this:

    Cost Category group October November December

    Salary 12'878 1'277 43'678



    I haven't figured out yet how to display all the requested months separately on one form...

    This is what I have so far, but there's an error in the code:

    Code:
    Dim Q As QueryDef, db As Database
        Dim ctl As Control
        Dim ctl1 As Control
        Dim Itm As Variant
        Dim strWhere As String
        Dim Criteria As String
        Dim Criteria1 As String
        Dim Monat As Date
        
        
        Set ctl = Me!lbKostenartengruppe
        Set ctl1 = Me!lbKostenstelle
        
        'filter by cost category group:
        
        For Each Itm In ctl.ItemsSelected
            If Len(Criteria) = 0 Then
                Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
            Else: Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) & Chr(34)
            End If
        Next Itm
        
        'filter by cost centre:
        
        For Each Itm In ctl1.ItemsSelected
            If Len(Criteria1) = 0 Then
                Criteria1 = Chr(34) & ctl1.ItemData(Itm) & Chr(34)
            Else: Criteria1 = Criteria1 & "," & Chr(34) & ctl1.ItemData(Itm) & Chr(34)
            End If
        Next Itm
        
        'if nothing is chose, show a MsgBox:
        
        If Len(Criteria) = 0 Or Len(Criteria1) = 0 Then
            Itm = MsgBox("Keine Auswahl vorhanden.")
            Exit Sub
        End If
        
        'if checkbox October is clicked, set the date category as 31.10.2007:
        
        If Me.ckOktober.Value Then
               Monat = #10/31/2007#
        End If
         
        'when the code finally works, add eleven more If-Then-Else loops for each month
        
        Set db = CurrentDb()
        Set Q = db.QueryDefs("qrySearch_MonatGetrennt")
        
                       
        'SQL String to display results:
        'qryBasicSearch pulls all the needed fields from 5 tables, qrySearch_MonatGetrennt is based on qryBasicSearch and is used to calculate the costs
        
        Q.SQL = "SELECT DISTINCT [Kostenart],[KoArt_Beschreibung],Sum([Kosten]) AS [Wert]" & _
                "FROM qryBasicSearch WHERE [KArtGr_Beschreibung] " & _
                "IN(" & Criteria & ") AND [Kostenstelle] IN(" & Criteria1 & ")" & _
                "AND [Bis] = #" & Monat & "#" & _
                "GROUP BY [Kostenart], [KoArt_Beschreibung];"
                
        Q.Close
        
                
        'show the results on a separate form, which is based on qrySearch_MonatGetrennt:
        
        DoCmd.OpenForm "FrmAusgabe_Monat"
    I get run-time error "Syntax error in Date/Time in SQL String".

    And when it works: HOW do I get to calculate and display each month separately on the output form - "FrmAusgabe_Monat"?????????

    Thanks so much in advance for your help!

    Best,
    OfficeDummy

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I presume the code stops at the Q.SQL line?

    Try msgbox'ing the right side of that Q.SQL line. It might show you a little more clearly what might be wrong.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2008
    Posts
    74
    Quote Originally Posted by StarTrekker
    I presume the code stops at the Q.SQL line?

    Try msgbox'ing the right side of that Q.SQL line. It might show you a little more clearly what might be wrong.
    I get # signs next to my date, but I guess it's usual. Otherwise, everything looks fine. *puzzled*

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I assume you mean one # sign either side of the date?

    After the second # sign, maybe there needs to be a space before the GROUP keyword... I can't be sure since you're not giving me much to work with here... I certainly can't run the code.

    I suggest taking the results of what you see into the Query Builder and pasting it into the SQL view... perhaps that will lead you to the cause.

    At the very least, paste the results of your msgbox here so I can see something!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jan 2008
    Posts
    74
    I added the space before GROUP BY, but it didn't change anything...

    Okay, here's a piece from the immediate window - I debug.prined the SQL string.

    Code:
    SELECT DISTINCT [Kostenart],[KoArt_Beschreibung],Sum([Kosten]) AS [Wert]FROM qryBasicSearch 
    WHERE [KArtGr_Beschreibung] IN("Unterhaltskosten","Mietkosten") 
    AND [Kostenstelle] IN("3100","3108","3125")AND [Bis] = #31.10.2007# 
    GROUP BY [Kostenart], [KoArt_Beschreibung];

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ah. You can't use dots to separate date parts. It needs to be slashes: #31/10/2007# not #31.10.2007#.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jan 2008
    Posts
    74
    Yeah, I know that. But the expression #31.10.2007# is stored in my variable 'Monat', which has been declared as #10/31/2007#.

    Somehow, Access automatically reformates the date into European format, and that's why my SQL String doesn't work. Can you somehow 'forbid' Access to reformat my date?

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok... so instead of Monat, use Format(Monat, "mm/dd/yyyy") to override the change.

    I think that change might be happening due to the settings in the International Options in Control Panel.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Jan 2008
    Posts
    74
    Ok, it works now! Thanks so much, StarTrekker!

    Now, Problem Nr.2: How can I use one query to display monthly calculations as separate columns?

    So the user chooses say, November, December and January on the form, and those three months are displayed separately?

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Happy to help

    As for problem 2, I think you might have to do a fair bit of coding to get that to work. The code would have to examine what is selected on the form and then set the Control Source of text boxes and the Caption of labels accordingly.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Jan 2008
    Posts
    74
    Can I do that with SQL String, or do I have to use something like 'DSum' for that? And does DSum support the complex criteria I have in my SQL code?

Posting Permissions

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