To show the other words on the report

  • Thread starter Thread starter Frank Situmorang
  • Start date Start date
F

Frank Situmorang

Hello,

In my Church membership database, in etering the data there is an option to
choose by combo A for Active member and I for Inactive Member, and D for
Dormant.

I already have a filter form to show all the active, how ever in the title
of the report when filter, it only shows A , I , D.

How can I make it in order to show on the report title" Active" to replace A
and "Inactive" to replace I and "Dormat" to replace D.

Thanks for any idea
 
Use an expression for the control source which cannot have the same name as a
field. One such expression might look like:

=IIF([Member status] = "A","Active", IIF([Member Status] = "I",
"Inactive",IIF([Member Status]="D","Dormant","No Status")))

or an alternative expression

=Choose(Instr(1,"AID",[Member Status])+1,"No Status", "Active", "Inactive",
"Dormant")

Those expressions should be all on one line, but the newreader will probably
wrap them onto multiple lines.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks John for your response. I am not too good in VBA. I can do this, just
by the help of the good people in this news group.

Could you tell me where shall I put it (the IIF) in htis VBA of Apply
filter. I want to show what is beeing filterred on the report. This is a kind
of dynamic header.

This is my VBA of button Apply filter:
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
Dim strTitle2 As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "Laporan Buku Anggota Jemaat
Kebayoran_Consol") <> acObjStateOpen Then
MsgBox "You must open the report first."
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"
Else
strOrgBody = Right(strOrgBody, Len(strOrgBody) - 1)
strTitle = Replace(Replace(strOrgBody, "'", ""), ",", ", ")

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"
Else
strMembrStat = Right(strMembrStat, Len(strMembrStat) - 1)
strTitle2 = Replace(Replace(strMembrStat, "'", ""), ",", ", ")

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")
& _
" - (" & Nz(strTitle2, "All") & ")"
.Filter = strFilter
.FilterOn = True
.OrderBy = strSortOrder
.OrderByOn = True
End With
End Sub


--
H. Frank Situmorang


John Spencer (MVP) said:
Use an expression for the control source which cannot have the same name as a
field. One such expression might look like:

=IIF([Member status] = "A","Active", IIF([Member Status] = "I",
"Inactive",IIF([Member Status]="D","Dormant","No Status")))

or an alternative expression

=Choose(Instr(1,"AID",[Member Status])+1,"No Status", "Active", "Inactive",
"Dormant")

Those expressions should be all on one line, but the newreader will probably
wrap them onto multiple lines.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Frank said:
Hello,

In my Church membership database, in etering the data there is an option to
choose by combo A for Active member and I for Inactive Member, and D for
Dormant.

I already have a filter form to show all the active, how ever in the title
of the report when filter, it only shows A , I , D.

How can I make it in order to show on the report title" Active" to replace A
and "Inactive" to replace I and "Dormat" to replace D.

Thanks for any idea
Click to expand...
Click to expand...
 
I think I misunderstood your original posting. I thought you wanted to
include the expansion of the abbreviation in each detail ( that is, next to
each member).

I am guessing but perhaps you need something like the following in the 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
Dim strTitle2 As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "Laporan Buku Anggota Jemaat
Kebayoran_Consol") <> acObjStateOpen Then
MsgBox "You must open the report first."
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"
Else
strOrgBody = Right(strOrgBody, Len(strOrgBody) - 1)
strTitle = Replace(Replace(strOrgBody, "'", ""), ",", ", ")

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"
Else
strMembrStat = Right(strMembrStat, Len(strMembrStat) - 1)
'=====================================================================
' NEW CODE
'=====================================================================
If strMembrStat Like "*A*" Then
strTitle2 = ", Active"
End IF

If strMembrStat Like "*I*" Then
strTitle2 = StrTitle2 & ", Inactive"
End IF

If strMembrStat Like "*D*" Then
strTitle2 = StrTitle2 & ", Dormant"
End IF

strTitle2 = Mid(StrTitle2,3)

' strTitle2 = Replace(Replace(strMembrStat, "'", ""), ",", ", ")
'=====================================================================
' END NEW CODE
'=====================================================================
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")
& _
" - (" & Nz(strTitle2, "All") & ")"
.Filter = strFilter
.FilterOn = True
.OrderBy = strSortOrder
.OrderByOn = True
End With
End Sub


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks very much John, I will try it. I am a slow learner in Access, but I
started to have this as my new hobby.

Frank
--
H. Frank Situmorang


John Spencer (MVP) said:
I think I misunderstood your original posting. I thought you wanted to
include the expansion of the abbreviation in each detail ( that is, next to
each member).

I am guessing but perhaps you need something like the following in the 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
Dim strTitle2 As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "Laporan Buku Anggota Jemaat
Kebayoran_Consol") <> acObjStateOpen Then
MsgBox "You must open the report first."
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"
Else
strOrgBody = Right(strOrgBody, Len(strOrgBody) - 1)
strTitle = Replace(Replace(strOrgBody, "'", ""), ",", ", ")

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"
Else
strMembrStat = Right(strMembrStat, Len(strMembrStat) - 1)
'=====================================================================
' NEW CODE
'=====================================================================
If strMembrStat Like "*A*" Then
strTitle2 = ", Active"
End IF

If strMembrStat Like "*I*" Then
strTitle2 = StrTitle2 & ", Inactive"
End IF

If strMembrStat Like "*D*" Then
strTitle2 = StrTitle2 & ", Dormant"
End IF

strTitle2 = Mid(StrTitle2,3)

' strTitle2 = Replace(Replace(strMembrStat, "'", ""), ",", ", ")
'=====================================================================
' END NEW CODE
'=====================================================================
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")
& _
" - (" & Nz(strTitle2, "All") & ")"
.Filter = strFilter
.FilterOn = True
.OrderBy = strSortOrder
.OrderByOn = True
End With
End Sub


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Frank said:
Thanks John for your response. I am not too good in VBA. I can do this, just
by the help of the good people in this news group.

Could you tell me where shall I put it (the IIF) in htis VBA of Apply
filter. I want to show what is beeing filterred on the report. This is a kind
of dynamic header.
Click to expand...
Click to expand...
 
John:

I have inserted the new code in the VBA, but it still does not work.

I appreciate your idea again, how can we make it works.

Thanks in advance
 
I'm stuck at this point. Sorry, but you will need to work it out yourself. I
cannot take the time to duplicate your data structure, report structure, etc.

By the way, "does not work" is an inadequate expression when you are trying to
trouble shoot. It can mean many things, such as
-- the wrong results are shown
-- no results are shown
-- an syntax error message appears
-- the computer seems to stop
-- the report does not open



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top