I am trying to set filters for printing a report. I found and example called ReportDialog which I am basing this one. It work great up to point I have to manipulate and integer in SQL String. My issue is when I try to filter for the Bedroom count all the string actions go out of the window because BedroomCount is an integer. When I run it now, I get a Type Mismatch error which is expected. How can I adjust the Bedroom Count section to work with an integer.
Here is the code so far:
Private Sub cmdSubmit_Click()
On Error GoTo Err_cmdSubmit_Click
Dim strReport As String
Dim strWhere As String
Dim strCommunity As String
Dim strClassType As String
Dim strNeighborhood As String
Dim intBedroomCount As Integer
Dim strWhereDate As String
' This is one way you can build a SQL "Where" Statement
If Me.chkBearCreek = True Then
strCommunity = strCommunity & ",'BC'"
End If
If Me.chkBlackFoot = True Then
strCommunity = strCommunity & ",'BF'"
End If
If Me.chkBridger = True Then
strCommunity = strCommunity & ",'BR'"
End If
If Me.chkCherryCreek = True Then
strCommunity = strCommunity & ",'CC'"
End If
If Me.chkDupree = True Then
strCommunity = strCommunity & ",'DU'"
End If
If Me.chkEagleButte = True Then
strCommunity = strCommunity & ",'EB'"
End If
If Me.chkGreenGrass = True Then
strCommunity = strCommunity & ",'GG'"
End If
If Me.chkIronLIghtning = True Then
strCommunity = strCommunity & ",'IL'"
End If
If Me.chkIsabel = True Then
strCommunity = strCommunity & ",'IS'"
End If
If Me.chkLaplant = True Then
strCommunity = strCommunity & ",'LP'"
End If
If Me.chkLantry = True Then
strCommunity = strCommunity & ",'LT'"
End If
If Me.chkParade = True Then
strCommunity = strCommunity & ",'PA'"
End If
If Me.chkRedScaffold = True Then
strCommunity = strCommunity & ",'RS'"
End If
If Me.chkRidgeView = True Then
strCommunity = strCommunity & ",'RV'"
End If
If Me.chkSwiftbird = True Then
strCommunity = strCommunity & ",'SB'"
End If
If Me.chkThunderButte = True Then
strCommunity = strCommunity & ",'TB'"
End If
If Me.chkTakini = True Then
strCommunity = strCommunity & ",'TK'"
End If
If Me.chkTimberLake = True Then
strCommunity = strCommunity & ",'TL'"
End If
If Me.chkWhiteHorse = True Then
strCommunity = strCommunity & ",'WH'"
End If
'Classification Type
If Me.cboClassification = "RENTAL" Then
strClassType = strClassType & ",'RENTAL'"
End If
If Me.cboClassification = "MUTUAL HELP" Then
strClassType = strClassType & ",'MUTUAL HELP'"
End If
If Me.cboClassification = "TAX CREDIT" Then
strClassType = strClassType & ",'TAX CREDIT'"
End If
' Neighborhoods
If Me.chkFoxRidgeI = True Then
strNeighborhood = strNeighborhood & ",'FOX1'"
End If
If Me.chkFOxRidgeII = True Then
strNeighborhood = strNeighborhood & ",'FOX2'"
End If
If Me.chkChinaTown = True Then
strNeighborhood = strNeighborhood & ",'CHINA'"
End If
If Me.chkDupreeStreet = True Then
strNeighborhood = strNeighborhood & ",'DUPREEST'"
End If
If Me.chkWashingtonStreet = True Then
strNeighborhood = strNeighborhood & ",'WASHST'"
End If
If Me.chkJeffersonStreet = True Then
strNeighborhood = strNeighborhood & ",'JEFFST'"
End If
If Me.chkKnotsLanding = True Then
strNeighborhood = strNeighborhood & ",'KNOTS'"
End If
If Me.chkSesameStreet = True Then
strNeighborhood = strNeighborhood & ",'SESAST'"
End If
If Me.chkNoHeart = True Then
strNeighborhood = strNeighborhood & ",'NOHEART'"
End If
'Bedroom size
If Me.chkOne = True Then
intBedroomCount = intBedroomCount & ",1"
End If
If Me.chkTWO = True Then
intBedroomCount = intBedroomCount & ",2"
End If
If Me.chkThree = True Then
intBedroomCount = intBedroomCount & ",3"
End If
If Me.chkFour = True Then
intBedroomCount = intBedroomCount & ",4"
End If
If Me.chkFive = True Then
intBedroomCount = intBedroomCount & ",5"
End If
' If the first part of strCommunity is ,
' We know that strCommunity has a value to use
If Left(strCommunity, 1) = "," Then
' Remove the ,
strCommunity = Right(strCommunity, Len(strCommunity) - 1)
' Add the beginning part of the Where Statement
strCommunity = " AND CommunityID IN (" & strCommunity
' Add the end part of the Where Statement
strCommunity = strCommunity & ")"
Else
' If the , was not added to strCommunity then
' No value was there--clear strCommunity
strCommunity = vbNullString
End If
' Debug.Print "strState = " & strState
'Build the neighborhood filter
' If the first part of strNeighborhood is ,
' We know that strNeighborhood has a value to use
If Left(strNeighborhood, 1) = "," Then
' Remove the ,
strNeighborhood = Right(strNeighborhood, Len(strNeighborhood) - 1)
' Add the beginning part of the Where Statement
strNeighborhood = " AND NeighborhoodID IN (" & strNeighborhood
' Add the end part of the Where Statement
strNeighborhood = strNeighborhood & ")"
Else
' If the , was not added to strNeighborhood then
' No value was there--clear strNeighborhood
strNeighborhood = vbNullString
End If
' Debug.Print "strNeighborhood = " & strNeighborhood
'Build the room count filter
' If the first part of intBedroomCount is ,
' We know that intBedroomCount has a value to use
If Left(intBedroomCount, 1) = "," Then
' Remove the ,
intBedroomCount = Right(intBedroomCount, Len(intBedroomCount) - 1)
' Add the beginning part of the Where Statement
intBedroomCount = " AND BedroomCount IN (" & intBedroomCount
' Add the end part of the Where Statement
intBedroomCount = intBedroomCount & ")"
Else
' If the , was not added to intBedroomCount then
' No value was there--clear intBedroomCount
intBedroomCount = vbNullString
End If
Debug.Print "intBedroomCount = " & intBedroomCount
' Build the Date Filter
'Select Case cboDateFilter
' Case Is = "Move-in Date"
' strWhereDate = "((tblPeople.MarriageDate) >= [Forms]![frmReportDialog]![txtStartDate])" & _
" AND ((tblPeople.MarriageDate)<=[Forms]![frmReportDialog]![txtEndDate])"
' Case Is = "DOB"
' strWhereDate = "((tblPeople.DOB) >= [Forms]![frmReportDialog]![txtStartDate])" & _
' " AND ((tblPeople.DOB)<=[Forms]![frmReportDialog]![txtEndDate])"
' Case Is = "None"
' strWhereDate = vbNullString
' Case Else
' strWhereDate = vbNullString
'End Select
' Debug.Print "strWhereDate = " & strWhereDate
' Put together the SQL string
strWhere = strWhereDate & strCommunity & strNeighborhood & intBedroomCount
Debug.Print "strWhere = " & strWhere
' Trim "AND " from the beginning if needed
If Left(strWhere, 4) = " AND" Then
' intWhereLen = Len(strWhere) - 4
strWhere = Right(strWhere, Len(strWhere) - 4)
End If
Debug.Print "strWhere = " & strWhere
strReport = Me.lstReports
' Debug.Print "strReport = " & strReport
DoCmd.OpenReport strReport, View:=acViewPreview, WhereCondition:=strWhere
' To use these 2 lines of code below you must add
' DoCmd.Restore to your Report's On Close Event,
DoCmd.RunCommand acCmdZoom75
DoCmd.Maximize
Exit_cmdSubmit_Click:
Exit Sub
Err_cmdSubmit_Click:
Select Case Err.Number
Case Is = 2501
'Ignore Useless Error Messsage
Resume Next
Case Is = 2046
'Restore to Normal Window size
DoCmd.Restore
Case Else
Call MsgBox(Err.Description & vbCrLf & "Error Number: " & Err.Number & vbCrLf & _
" In procedure cmdOpenReport_Click of VBA Document Form_frm_reports_manage_tenant_listing")
Resume Exit_cmdSubmit_Click
End Select
End Sub
THank you,
Art Lorenzini
Sioux Falls, SD
Posted by: dbalorenzini@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar