Filter by form with beginning date and enddate, where calender we

[Frank Situmorang]

Hello,

I do not know what is wrong on my form filter where I have the begginning
and end date.


This is the error message and my wholo VBA, I do not know what makes it
missing:
Error Message:
Missing),], or item in query expression '(ChurchName_L] IN(GMAHK Jemaat
Kebayoran') AND[Stat_code] Like* AND

[JenisKel '*'[tgl_Nikah]<26/02/2009

VBA:
Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
Dim strOrgBody As String
Dim strMembrStat As String
Dim strWhere As String '<<<<<<<<<start frm here will be event date
Dim strField As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" ' <<<<<<<<<<upto here will be
event date
Dim strGender As String
Dim strFilter As String
Dim strSortOrder As String
Dim strTitle As String '<<<<<<<<<<<<< New <<<<<<<<<<<<<<<<<
Dim strTitle2 As String '<<<<<<<<<<<<< New <<<<<<<<<<<<<<<<<
Dim strMsg As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "Laporan Buku Anggota Jemaat
Kebayoran_Kejadian2") <>

acObjStateOpen Then
' Note: This is message string #1.
strMsg = DLookup("MessageString", "[Lookup Message String_Qry]",
"[FormName] = '" & Me.Name & "' AND

[StringNumber] = 1")
MsgBox strMsg
Exit Sub
End If
' Build criteria string from Org_Bodies listbox
For Each varItem In Me.Org_Bodies.ItemsSelected
strOrgBody = strOrgBody & ",'" & Me.Org_Bodies.ItemData(varItem) _
& "'"
Next varItem
If Len(strOrgBody) = 0 Then
strOrgBody = "Like '*'"
strTitle = "All Churches" '<<<<<<<<<<< New <<<<<<<<<<<<<<<<<<<<
Else
strOrgBody = Right(strOrgBody, Len(strOrgBody) - 1)
strTitle = Replace(Replace(strOrgBody, "'", ""), ",", ", ")
'<<<<<<<< New Line above <<<<<<<<<
strOrgBody = "IN(" & strOrgBody & ")"
End If
' Build criteria string from MemberStatus listbox
For Each varItem In Me.MemberStatus.ItemsSelected
strMembrStat = strMembrStat & ",'" &
Me.MemberStatus.ItemData(varItem) _
& "'"
Next varItem
If Len(strMembrStat) = 0 Then
strMembrStat = "Like '*'"
strTitle = "All" '<<<<<<<<<<< New <<<<<<<<<<<<<<<<<<<<
Else
strMembrStat = Right(strMembrStat, Len(strMembrStat) - 1)
strTitle2 = Replace(Replace(strMembrStat, "'", ""), ",", ", ")
'<<<<<<<< New Line above <<<<<<<<<
strMembrStat = "IN(" & strMembrStat & ")"
End If
' Build criteria string from FraEvents option group
Select Case Me.FraEvents.Value
Case 1
strField = "[TGLLAHIR]"
Case 2
strField = "[Tgl_Nikah]"
Case 3
strField = "[TGLBPTIS_M]"
Case 4
strField = "[ATASSURT_M]"
Case 5
strField = "[TGL_pen]"
Case 6
strField = "[ATSPERCA_M]"
Case 7
strField = "[ATSSUR1_K]"
Case 8
strField = "[ATSSUR2_K]"
Case 9
strField = "[KMATIAN_K]"
Case 10
strField = "[KELMURT_K]"
Case 11
strField = "[KELHILA_K]"

End Select

If strField <> vbNullString Then
With Forms!frmReportFilter4_AllDateEvents
If IsDate(!txtBegDate) Then
strWhere = "(" & strField & " >= " & Format(!txtBegDate) & ")And "

End If
If IsDate(!txtEndDate) Then
strWhere = "(" & strField & " < " & Format(!txtEndDate + 1) &
")AND "

End If
End With
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

'Apply the filter as needed, e.g.:
' Build criteria string from fraGender option group

Select Case Me.fraGender.Value
Case 1
strGender = "='L'"
Case 2
strGender = "='P'"
Case 3
strGender = "Like '*'"
End Select
' Build filter string
strFilter = "[ChurchName_L] " & strOrgBody & _
" AND [STAT_CODE] " & strMembrStat & _
" AND [JenisKel] " & strGender & strWhere

' Build sort string
If Me.cboSortOrder1.Value <> "Not Sorted" Then
strSortOrder = "[" & Me.cboSortOrder1.Value & "]"
If Me.cmdSortDirection1.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
If Me.cboSortOrder2.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",[" & Me.cboSortOrder2.Value & "]"
If Me.cmdSortDirection2.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
If Me.cboSortOrder3.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",[" & Me.cboSortOrder3.Value
& "]"
If Me.cmdSortDirection3.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
End If
End If
End If
' Apply filter and sort to report
With Reports![Laporan Buku Anggota Jemaat Kebayoran_Kejadian2]
.Rptfilter_label.Caption = "Report for " & Nz(strTitle, "All Churches")
& _
" - with Member Status(" & Nz(strTitle2, "All") & ")"
'<<<<<<<<<<<<<<<<<<<<< New line above <<<<<<<<<<<<<<<<<<<<<
.Filter = strFilter
.FilterOn = True
.OrderBy = strSortOrder
.OrderByOn = True
End With
End Sub

 

[Franka Situmorang]

Sir.. i do not know what is wrong, below is the one that works perfectly, and
the only different the membertype I changed to the Date events. Yes I have to
admit that member type is one field, while the dates event option consits of
many fields. But I think when we click the option, it only chose one to be
included in in the Strwhere.

This is the working VBA:
Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
Dim strOrgBody As String
Dim strMembrStat As String
Dim strMembrType As String
Dim strGender As String
Dim strFilter As String
Dim strSortOrder As String
Dim strTitle As String '<<<<<<<<<<<<< New <<<<<<<<<<<<<<<<<
Dim strTitle2 As String '<<<<<<<<<<<<< New <<<<<<<<<<<<<<<<<
Dim strMsg As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "Laporan Buku Anggota Jemaat
Kebayoran_Consol") <> acObjStateOpen Then
' Note: This is message string #1.
strMsg = DLookup("MessageString", "[Lookup Message String_Qry]",
"[FormName] = '" & Me.Name & "' AND [StringNumber] = 1")
MsgBox strMsg
Exit Sub
End If
' Build criteria string from Org_Bodies listbox
For Each varItem In Me.Org_Bodies.ItemsSelected
strOrgBody = strOrgBody & ",'" & Me.Org_Bodies.ItemData(varItem) _
& "'"
Next varItem
If Len(strOrgBody) = 0 Then
strOrgBody = "Like '*'"
strTitle = "All Churches" '<<<<<<<<<<< New <<<<<<<<<<<<<<<<<<<<
Else
strOrgBody = Right(strOrgBody, Len(strOrgBody) - 1)
strTitle = Replace(Replace(strOrgBody, "'", ""), ",", ", ")
'<<<<<<<< New Line above <<<<<<<<<
strOrgBody = "IN(" & strOrgBody & ")"
End If
' Build criteria string from MemberStatus listbox
For Each varItem In Me.MemberStatus.ItemsSelected
strMembrStat = strMembrStat & ",'" &
Me.MemberStatus.ItemData(varItem) _
& "'"
Next varItem
If Len(strMembrStat) = 0 Then
strMembrStat = "Like '*'"
strTitle = "All" '<<<<<<<<<<< New <<<<<<<<<<<<<<<<<<<<
Else
strMembrStat = Right(strMembrStat, Len(strMembrStat) - 1)
strTitle2 = Replace(Replace(strMembrStat, "'", ""), ",", ", ")
'<<<<<<<< New Line above <<<<<<<<<
strMembrStat = "IN(" & strMembrStat & ")"
End If
' Build criteria string from FraMembership option group
Select Case Me.FraMembership.Value
Case 1
strMembrType = "='1'"
Case 2
strMembrType = "='2'"
Case 3
strMembrType = "Like '*'"
End Select
' Build criteria string from fraGender option group
Select Case Me.fraGender.Value
Case 1
strGender = "='L'"
Case 2
strGender = "='P'"
Case 3
strGender = "Like '*'"
End Select
' Build filter string
strFilter = "[ChurchName_L] " & strOrgBody & _
" AND [STAT_CODE] " & strMembrStat & _
" AND [JenisKel] " & strGender & _
" AND [JnsAngt] " & strMembrType

' Build sort string
If Me.cboSortOrder1.Value <> "Not Sorted" Then
strSortOrder = "[" & Me.cboSortOrder1.Value & "]"
If Me.cmdSortDirection1.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
If Me.cboSortOrder2.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",[" & Me.cboSortOrder2.Value & "]"
If Me.cmdSortDirection2.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
If Me.cboSortOrder3.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",[" & Me.cboSortOrder3.Value
& "]"
If Me.cmdSortDirection3.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
End If
End If
End If
' Apply filter and sort to report
With Reports![Laporan Buku Anggota Jemaat Kebayoran_Consol]
.Rptfilter_label.Caption = "Report for " & Nz(strTitle, "All Churches")
& _
" - with Member Status(" & Nz(strTitle2, "All") & ")"
'<<<<<<<<<<<<<<<<<<<<< New line above <<<<<<<<<<<<<<<<<<<<<
.Filter = strFilter
.FilterOn = True
.OrderBy = strSortOrder
.OrderByOn = True
End With
End Sub

...'(ChurchName_L] IN(GMAHK Jemaat ...

I think you are missing a bracket from the above line:
Try:

([ChurchName_L] IN(GMAHK Jemaat

Regards

--
<a href="
http://www.software-matters.co.uk/bespoke-database-design.html">Bespoke
Access Database Development</a>
<p>Software Matters</br>
Straightforward solutions that work</p>

Message posted via AccessMonster.com