Can this Frame option group will work? as filter

[Frank Situmorang]

Hello,

If I tried my VBA to read from the form filter. Will it work?, below is my
VBA:
' Build criteria string from FraEvents option group
Select Case Me.FraEvents.Value
Case 1
strDateofBirth = "BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND [Forms]![frmReportDates_All
DateEvents]![txtEndDate]"
Case 2
strDateBaptized = "BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND [Forms]![frmReportDates_All
DateEvents]![txtEndDate]"
Case 3
strDatetrasnsfer = "BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND [Forms]![frmReportDates_All
DateEvents]![txtEndDate]"
End Select

Thanks for any hlep

 

[Allen Browne]

Yes: that approach could work.
Perhaps something like this:

Dim strWhere As String
Dim strField As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

Select Case Me.FraEvents.Value
Case 1
strField = "[Date Of Birth]"
Case 2
strField = "[Date Baptized]
Case 3
strField = "[Date Of Transfer]"
End Select
If strField <> vbNullString Then
With Forms!frmReportDates_AllDateEvents
If IsDate(!txtBegDate) Then
strWhere = "(" & strField & " >= " & Format(!txgtBegDate) & ")
AND "
End If
If IsDate(!txtEndDate) Then
strWhere = "(" & strField & " < " & Format(!txgtEndDate + 1) &
") AND "
End If
End With
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
'Apply the filter as needed, e.g.:
Me.Filter = strWher
Me.FilterOn = True
End If

 

[Frank Situmorang]

Thanks Allen...you are very good to me, I will give it a try.

--
H. Frank Situmorang

Yes: that approach could work.
Perhaps something like this:

Dim strWhere As String
Dim strField As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

Select Case Me.FraEvents.Value
Case 1
strField = "[Date Of Birth]"
Case 2
strField = "[Date Baptized]
Case 3
strField = "[Date Of Transfer]"
End Select
If strField <> vbNullString Then
With Forms!frmReportDates_AllDateEvents
If IsDate(!txtBegDate) Then
strWhere = "(" & strField & " >= " & Format(!txgtBegDate) & ")
AND "
End If
If IsDate(!txtEndDate) Then
strWhere = "(" & strField & " < " & Format(!txgtEndDate + 1) &
") AND "
End If
End With
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
'Apply the filter as needed, e.g.:
Me.Filter = strWher
Me.FilterOn = True
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

If I tried my VBA to read from the form filter. Will it work?, below is my
VBA:
' Build criteria string from FraEvents option group
Select Case Me.FraEvents.Value
Case 1
strDateofBirth = "BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND [Forms]![frmReportDates_All
DateEvents]![txtEndDate]"
Case 2
strDateBaptized = "BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND [Forms]![frmReportDates_All
DateEvents]![txtEndDate]"
Case 3
strDatetrasnsfer = "BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND [Forms]![frmReportDates_All
DateEvents]![txtEndDate]"
End Select

Thanks for any hlep

 

[Frank Situmorang]

Allen:

I have tried it but there is a syntax error here:
Red Color in this line: With Forms!frmReportFilter4_All DateEvents

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

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

End If
End With
End If

I appreciate your help Allen
--
H. Frank Situmorang

Yes: that approach could work.
Perhaps something like this:

Dim strWhere As String
Dim strField As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

Select Case Me.FraEvents.Value
Case 1
strField = "[Date Of Birth]"
Case 2
strField = "[Date Baptized]
Case 3
strField = "[Date Of Transfer]"
End Select
If strField <> vbNullString Then
With Forms!frmReportDates_AllDateEvents
If IsDate(!txtBegDate) Then
strWhere = "(" & strField & " >= " & Format(!txgtBegDate) & ")
AND "
End If
If IsDate(!txtEndDate) Then
strWhere = "(" & strField & " < " & Format(!txgtEndDate + 1) &
") AND "
End If
End With
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
'Apply the filter as needed, e.g.:
Me.Filter = strWher
Me.FilterOn = True
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

If I tried my VBA to read from the form filter. Will it work?, below is my
VBA:
' Build criteria string from FraEvents option group
Select Case Me.FraEvents.Value
Case 1
strDateofBirth = "BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND [Forms]![frmReportDates_All
DateEvents]![txtEndDate]"
Case 2
strDateBaptized = "BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND [Forms]![frmReportDates_All
DateEvents]![txtEndDate]"
Case 3
strDatetrasnsfer = "BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND [Forms]![frmReportDates_All
DateEvents]![txtEndDate]"
End Select

Thanks for any hlep

 

[Allen Browne]

If you have a space in your form name, enclose it in square brackets:
With Forms![frmReportFilter4_All Date Events]

(Unfortunately, there was a line-break in your original post, so I can not
tell what the form name actually is.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

I have tried it but there is a syntax error here:
Red Color in this line: With Forms!frmReportFilter4_All DateEvents

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

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

End If
End With
End If

I appreciate your help Allen
--
H. Frank Situmorang

Yes: that approach could work.
Perhaps something like this:

Dim strWhere As String
Dim strField As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

Select Case Me.FraEvents.Value
Case 1
strField = "[Date Of Birth]"
Case 2
strField = "[Date Baptized]
Case 3
strField = "[Date Of Transfer]"
End Select
If strField <> vbNullString Then
With Forms!frmReportDates_AllDateEvents
If IsDate(!txtBegDate) Then
strWhere = "(" & strField & " >= " & Format(!txgtBegDate) &
")
AND "
End If
If IsDate(!txtEndDate) Then
strWhere = "(" & strField & " < " & Format(!txgtEndDate + 1)
&
") AND "
End If
End With
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
'Apply the filter as needed, e.g.:
Me.Filter = strWher
Me.FilterOn = True
End If

Hello,

If I tried my VBA to read from the form filter. Will it work?, below is
my
VBA:
' Build criteria string from FraEvents option group
Select Case Me.FraEvents.Value
Case 1
strDateofBirth = "BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND [Forms]![frmReportDates_All
DateEvents]![txtEndDate]"
Case 2
strDateBaptized = "BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND [Forms]![frmReportDates_All
DateEvents]![txtEndDate]"
Case 3
strDatetrasnsfer = "BETWEEN [Forms]![frmReportDates_All
DateEvents]![txtBegDate] AND [Forms]![frmReportDates_All
DateEvents]![txtEndDate]"
End Select

 

[Frank Situmorang]

Thanks Allen for your help. Now when I tested there is an error message, I
do not know what is missing. Here is the error message and the whole VBA:
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

I appreciate your help

 

[Allen Browne]

Should there be an opening square bracket before the ChurchName_L field:
[ChurchName_L] IN (...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news[email protected]...

 

[Franka Situmorang]

Allen:

I can not see what is wrong, because below is the one which works perfectly.
The only thing is the membertype option group, I changed with the Date of
events, which are in the different fields. Yes membertype is the one field,
while the date events are from different field. Is it the one that makes not
working.

Below is the working one:
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

Thanks for your help Allen

Should there be an opening square bracket before the ChurchName_L field:
[ChurchName_L] IN (...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Thanks Allen for your help. Now when I tested there is an error message,
I
do not know what is missing. Here is the error message and the whole VBA:
Error Message:
Missing),], or item in query expression '(ChurchName_L] IN(GMAHK Jemaat
Kebayoran') AND[Stat_code] Like* AND

 

[Allen Browne]

message

I can not see what is wrong, because below is the one which works
perfectly.

Frank, I can't go through the whole thing for you (particularly as you say
it's working anyway.)

You need to pin down the exact error, in the exact line, and use the
Immediate Window to figure out what's wrong.

 

[Frank Situmorang]

Allen:

Here there is a syntax error, when I tested it in the intermediate window,
it says " compile error, Bock if without end if":
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

Is it because there is format?, because in my startdate and end date, I have
format it to be medium date


Thanks in advance,

Frank

 

[Allen Browne]

The message means that you have mismatching block operations.

For exmaple, you may have 5 If ... Then lines in your procedure, but only 4
matching End If lines.

Or, you might have a mismatched ending (such as a Loop statement closing a
Do) where an End If was expected.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...