how to populate a combo box with canned queries and display results

[AccessIdiot]

I have one big fat table with goose nesting data. Things like nest location (name of island in a river), nest type (ground/platform), number of eggs, number of eggs hatched, type of predation, etc.

I have some canned queries that I need to report: total number of eggs, number of eggs by island, number of eggs by type, number of nests, number of nests by island, number of nests by type, etc. So there are a few basic questions (total # nests, total # eggs, total nest success, total nest failure) and then further filters (all of the above filtered by type, location, possibly date range).

I would LOVE to build a dynamic search form but I keep failing (combo box #1 = total # of . . .; combo box #2 = filter by type/location; 3rd filter of date range).

So now I'm trying to just build one combo box that contains all these canned queries and then have a subform or report (whatever is easiest) show the results. The queries are not simple select statements, they are sums and counts. Any ideas? All I've got right now is an unbound form with an unbound combo box that contains a value list of two columns: name of query, description of query. I'm clueless about what to put in the after update part of my combo box.

By the way this is Access 2007. Cheers!

[AccessIdiot]

Broken links? Ahh, fixed, thanks, will check them out.

So here is what I have so far on an unbound form:

three combo boxes - two hidden and stacked that appear based on what the first one calls.

Two buttons: one for apply filter and one for remove filter. I found some great code for filtering a report based on a couple of combo boxes from a form.

The filter code seems to work for one combo box (cboType) but not for the other (cboIsland). I think because its trying to combine the where clause and really they are two separate where clauses but I don't know how to modify it to say "construct the where clause based on which cbo is visible".

The error I'm getting has to do with a data type mismatch but I'm not sure a) why when they are both constructed the same way and one works and the other doesn't and b) how to fix it. The tables that feed the combo boxes are two columns: auto id and text value. The combo boxes are bound to the first column but display the 2nd.

Here is the code:

 
 
Option Compare Database
Private Sub cboFilterBy_AfterUpdate()
If Me.cboFilterBy.Value = "Nest Type" Then
    Me.cboType.Visible = True
    Me.cboIsland.Visible = False
ElseIf Me.cboFilterBy.Value = "Island" Then
    Me.cboIsland.Visible = True
    Me.cboType.Visible = False
Else
    Me.cboType.Visible = False
    Me.cboIsland.Visible = False
End If
End Sub
 
Private Sub Form_Current()
 
If Me.cboFilterBy.Value = "Nest Type" Then
    Me.cboType.Visible = True
    Me.cboIsland.Visible = False
ElseIf Me.cboFilterBy.Value = "Island" Then
    Me.cboIsland.Visible = True
    Me.cboType.Visible = False
Else
    Me.cboType.Visible = False
    Me.cboIsland.Visible = False
End If
End Sub
 
Private Sub Form_Load()
     DoCmd.OpenReport "rpt_NestingMain", acViewPreview
End Sub
 
Private Sub cmdApplyFilter_Click()
    Dim strType As String
    Dim strIsland As String
    Dim strFilter As String
 
'If a combo box is empty its value is Null so I can use an If Statement to check whether or not the user made a choice
'and then construct the appropriate SQL:
 
    If IsNull(Me.cboType.Value) Then
        strType = "Like '*'"
    Else
        strType = "=" & Me.cboType.Value & ""
    End If
    If IsNull(Me.cboIsland.Value) Then
        strIsland = "Like '*'"
    Else
        strIsland = "='" & Me.cboIsland.Value & "'"
    End If
 
'Combine the criteria to form a WHERE clause for the filter:
    strFilter = "[Nest Location] " & strType & " AND [Island Name] " & strIsland
    With Reports![rpt_NestingMain]
         .Filter = strFilter
         .FilterOn = True
    End With
 
End Sub
 
 
Private Sub cmdRemoveFilter_Click()
     On Error Resume Next
     Reports![rpt_NestingMain].FilterOn = False
     Me.cboFilterBy.Value = ""
     Me.cboType.Visible = False
     Me.cboIsland.Visible = False
End Sub
 

[AccessIdiot]

Okay, I fixed it myself - nevermind! Thanks for the links, they helped me figure out how to correctly phrase for a numeric value in the cbo. Now to filter further on date range!

[jimatqsi]

I think you are missing 2 equal signs on line 55.

strFilter = "[Nest Location]= " & strType & " AND [Island Name]= " & strIsland

About the datatype mismatch, how is Nest Type defined in the table? Is it a string field? You are passing the nest type through strType, which is a string, but your filter does not enclose the type in quotes, so it looks like you are treating it as a numeric value. It has to be all one way or all the other.
Did you mean

strFilter = "[Nest Location]= '" & strType & "' AND [Island Name]= '" & strIsland & "'"

Jim

[AccessIdiot]

Yep you are right, its not working.

This is where I get confused. The table that the combo box is bound to has two columns: the primary key auto id and a text field that has the text I care about. So its that whole thing where you bind to one column but display two, the first with a width of zero. This was common practice in access '03 but I'm not sure if that's still how it's done with '07. It's how I have it set up.

So I'm assuming that that is why I was getting the data type error - because the code I was using was formatted for a text field when really the combo box is bound to the numeric field. This is what my code looks like now (I tried to fix it for the data type) but it is not giving me the results I want.

BTW this code is totally hacked from a couple of different examples so its probably really sloppy.


Oh and I've left in the commented out code for filtering by date because I'm going to tackle that next. :-)

I know part of the problem is the big where clause at the end where I'm including both cboType and cboIsland when really I need to filter on one or the other. I'm just not sure how to tackle that. When one is visible I need the invisible one to not be included in the where statement.

Option Compare Database
Private Sub cboFilterBy_AfterUpdate()
If Me.cboFilterBy.Value = "Nest Type" Then
    Me.cboType.Visible = True
    Me.cboIsland.Visible = False
ElseIf Me.cboFilterBy.Value = "Island" Then
    Me.cboIsland.Visible = True
    Me.cboType.Visible = False
Else
    Me.cboType.Visible = False
    Me.cboIsland.Visible = False
End If
End Sub
 
Private Sub Form_Current()
 
If Me.cboFilterBy.Value = "Nest Type" Then
    Me.cboType.Visible = True
    Me.cboIsland.Visible = False
ElseIf Me.cboFilterBy.Value = "Island" Then
    Me.cboIsland.Visible = True
    Me.cboType.Visible = False
Else
    Me.cboType.Visible = False
    Me.cboIsland.Visible = False
End If
End Sub
 
Private Sub Form_Load()
     DoCmd.OpenReport "rpt_NestingMain", acViewPreview
End Sub
 
Private Sub cmdApplyFilter_Click()
'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
 
    Dim strFilter As String                  'The criteria string.
    Dim strType As String
    Dim strIsland As String
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
'If a combo box is empty its value is Null so I can use an If Statement to check whether or not the user made a choice
'and then construct the appropriate SQL:
 
    If Me.cboType.Value > "" Then
        strType = strType & " AND ([Nest Location]=" & Me.cboType.Value & ")"
    End If
 
    If Me.cboIsland.Value > "" Then
        strIsland = strIsland & " AND ([Island Name]=" & Me.cboIsland.Value & ")"
    End If
 
    'Date field. Use the format string to add the # delimiters and get the right international format.
    'If Not IsNull(Me.txtStartDate) Then
        'strFilter = strFilter & "([EnteredOn] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    'End If
 
    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    'If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
        'strFilter = strFilter & "([EnteredOn] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
    'End If
 
 '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        'MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strFilter = Left$(strFilter, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
    End If
'Combine the criteria to form a WHERE clause for the filter:
    strFilter = "[Nest Location] " & strType & " AND [Island Name] " & strIsland
    With Reports![rpt_NestingMain]
         .Filter = strFilter
         .FilterOn = True
    End With
 
 
End Sub
 
 
Private Sub cmdRemoveFilter_Click()
     On Error Resume Next
     Reports![rpt_NestingMain].FilterOn = False
     Me.cboFilterBy.Value = ""
     Me.cboType.Visible = False
     Me.cboIsland.Visible = False
End Sub
 

[AccessIdiot]

Okay, got it working except for date range filter. If I leave that part blank I get an error. If I put something in it does nothing. I can't figure out the right syntax to include it as part of the final filter statement.

[jimatqsi]

Try wrapping your date criteria in "#", one before and one after the date, like this
'strFilter = strFilter & "([EnteredOn] <# " & Format(Me.txtEn dDate + 1, conJetDate) & "#) AND "

Jim

[AccessIdiot]

That didn't do it. I think the conJetDate already formats it with #'s.

I think it has something to do with how the final statement is put together.

Private Sub cmdApplyFilter_Click()
    Dim strFilter As String 'The criteria string.
    Dim strType As String
    Dim strIsland As String
    Dim strDate As String
    Dim lngLen As Long   'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"
 
If Me.cboType.Value > "" Then
  strType = strType & " AND ([Nest Location]=" & Me.cboType.Value & ")"
End If
 
If Me.cboIsland.Value > "" Then
  strIsland = strIsland & " AND ([Island Name]=" & Me.cboIsland.Value & ")"
End If
 
'Date field. Use the format string to add the # delimiters and get the right international format.
If Not IsNull (Me.txtStartDate) Then
  strDate = strDate & "([Field Obs Date] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
End If
 
If Not IsNull (Me.txtEndDate) Then   'Less than the next day.
  strDate = strDate & "([Field Obs Date] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If
 
lngLen = Len(strDate) - 5
strDate = Left$(strDate, lngLen)
 
'final string
strFilter = "[Nest Location] " & strType & " AND [Island Name] " & strIsland & " AND [Field Obs Date] " & strDate
 
With Reports![rpt_NestingMain]
         .Filter = strFilter
         .FilterOn = True
End With
End Sub
 

Basically I'd like to be able to enter a starting date only OR a range of dates and have it work. I believe the code came from that classic Allen Browne site.

[AccessIdiot]

Okay, got it. That NeoPa guy sure knows what he's talking about. ;-)

[jimatqsi]

:) Yea, a lot of us noticed that.

[NeoPa]

AccessIdiot: Okay, got it. That NeoPa guy sure knows what he's talking about. ;-)

Absolutely!! :D

M. (I won't use full name here & I can't refer to you as AccessIdiot as I know better than that),

Great to see you back again.

I just caught your latest thread and saw that you'd been back for a week or so. I'll pop over there now and see if I can't provide some sort of answer for you.

-NeoPa.

PS. How are the fish getting on up there? All mapped and documented yet?