You need a string variable for the bedroom criteria:
Dim strBedWhere as String
Then assign the variable similar to the following:
' Add the beginning part of the Where Statement
strBedWhere = " AND BedroomCount IN (" & intBedroomCount
' Add the end part of the Where Statement
strBedWhere = strBedWhere & ")"
Darrell
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, July 22, 2015 3:53 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Manipulating a integer in a string statement
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 = strCommunit y & ",'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
& nbsp; 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 &a mp; ",'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
&nbs p; ' 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 = Righ t(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
&n bsp; ' 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"
' &nb sp; 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
&n bsp; '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
The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.
Posted by: "Embrey, Darrell" <DEmbrey@bcbsm.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar