Create an unbound form with these controls:
1. Combo box named cboMeetingType, with RowSource set to the MeetingType
table.
2. Text box named txtYear, with Format property set to General Number (so
only numbers are accepted.)
3. Command button to open the report. It's event procedure will be like the
one below.
Private Sub cmdPrint_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
With Me.cboMeetingType
If Not IsNull(.Value) Then
strWhere = strWhere & "([MeetingType] = " & .Value & ") AND "
End If
End With
With Me.txtYear
If Not IsNull(.Value) Then
strWhere = strWhere & "([MeetingDate] Between " & _
Format(DateSerial(.Value, 1,1), conJetDate) & " And " & _
Format(DateSerial(.Value, 12,31), conJetDate) & ") AND "
End If
End With
lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub
Notes:
====
a) If MeetingType is a Text type field, you need extra quotes:
strWhere = strWhere & "([MeetingType] = """ & .Value & ") AND """
b) This is written so it is easy to add more search options if needed.
c) The code allows JET to use any index you have on the MeetingDate field,
for efficient results.
d) The formatting of the dates will ensure the dates are correctly
interpreted in countries like Australia and UK.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
message
news:
[email protected]...
