Rabu, 22 Juli 2015

Re: [MS_AccessPros] Manipulating a integer in a string statement

 

 That seems to have solve my issue. Thank you.

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."  






From: "'Embrey, Darrell' DEmbrey@bcbsm.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, July 22, 2015 3:45 PM
Subject: RE: [MS_AccessPros] Manipulating a integer in a string statement

 
 
If it were me, I would change it to look similar to the following:
 
Dim strBedroomCount As String
 
'Bedroom size
   
    If Me.chkOne = True Then
        strBedroomCount = strBedroomCount & "1,"
    End If
   
    If Me.chkTWO = True Then
        strBedroomCount = strBedroomCount & "2,"
    End If
   
    If Me.chkThree = True Then
        strBedroomCount = strBedroomCount & "3,"
    End If
   
    If Me.chkFour = True Then
        strBedroomCount = strBedroomCount & "4,"
    End If
   
    If Me.chkFive = True Then
        strBedroomCount = strBedroomCount & "5"
    End If

    If Right(strBedroomCount,1) = ","  Then
        strBedroomCount = Left(strBedroomCount, Len(strBedroomCount) - 1)
    End If

        ' Add the beginning part of the Where Statement
    If InStr(strBedroomCount,",") > 0 Then

        strBedroomCount = " AND BedroomCount IN (" & strBedroomCount & ")"
 
    Else

        strBedroomCount = " AND BedroomCount = " & strBedroomCount & " "
    End If

Darrell
 


From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, July 22, 2015 4:19 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Manipulating a integer in a string statement
 
 
But the actual type is an integer, would I need to  convert to string?
 
   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
    
Or maybe this code is all wrong for it.

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070
 
"Anyone who claimed that old age had brought them patience was either lying or senile."  



 

From: "'Embrey, Darrell' DEmbrey@bcbsm.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, July 22, 2015 3:13 PM
Subject: RE: [MS_AccessPros] Manipulating a integer in a string statement
 
 
Hi Art,
 
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.
 

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: Art Lorenzini <dbalorenzini@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)

.

__,_._,___

Tidak ada komentar:

Posting Komentar