Your email is all garbled.
Just place a breakpoint in your code and step through it using F8. Hover over any variable to see its current value or use the debug window.
Duane Hookom, MVP
MS Access
Just place a breakpoint in your code and step through it using F8. Hover over any variable to see its current value or use the debug window.
Duane Hookom, MVP
MS Access
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Wed, 22 Jul 2015 21:28:55 +0000
> Subject: Re: [MS_AccessPros] Manipulating a integer in a string statement
>
> This is where I am right now.
> 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
> Which works up to the point that they do not select a bedroom count and that is when it pops the error.
> With Warm Regards, Arthur D. LorenziniIT System ManagerCheyenne River Housing AuthorityWk.(605)964-4265 Ext. 130Fax (605)964-1070
> "Anyone who claimed that old age had brought them patience was either lying or senile."
>
>
>
>
> From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
> To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
> Sent: Wednesday, July 22, 2015 4:19 PM
> Subject: RE: [MS_AccessPros] Manipulating a integer in a string statement
>
> You should check for
> If Len(strBedroomCount) > [Some Number Here] Then
> 'add this to your where clause
> End If
>
> Duane Hookom, MVP
> MS Access
>
>
>
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Wed, 22 Jul 2015 21:09:03 +0000
> Subject: Re: [MS_AccessPros] Manipulating a integer in a string statement
>
>
>
> I guess it was not quite right. We do not account for if they do not choose a bedroom count. Its causing the following error:
> Syntax error(missing operator) in query Expression 'ClasssificationType IN ('RENTAL') And BedroomCount='.
> With Warm Regards, Arthur D. LorenziniIT System ManagerCheyenne River Housing AuthorityWk.(605)964-4265 Ext. 130Fax (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
>
> Hi Art, 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.
>
>
>
> #yiv9615357599 #yiv9615357599 -- #yiv9615357599ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9615357599 #yiv9615357599ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9615357599 #yiv9615357599ygrp-mkp #yiv9615357599hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9615357599 #yiv9615357599ygrp-mkp #yiv9615357599ads {margin-bottom:10px;}#yiv9615357599 #yiv9615357599ygrp-mkp .yiv9615357599ad {padding:0 0;}#yiv9615357599 #yiv9615357599ygrp-mkp .yiv9615357599ad p {margin:0;}#yiv9615357599 #yiv9615357599ygrp-mkp .yiv9615357599ad a {color:#0000ff;text-decoration:none;}#yiv9615357599 #yiv9615357599ygrp-sponsor #yiv9615357599ygrp-lc {font-family:Arial;}#yiv9615357599 #yiv9615357599ygrp-sponsor #yiv9615357599ygrp-lc #yiv9615357599hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9615357599 #yiv9615357599ygrp-sponsor #yiv9615357599ygrp-lc .yiv9615357599ad {margin-bottom:10px;padding:0 0;}#yiv9615357599 #yiv9615357599actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9615357599 #yiv9615357599activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9615357599 #yiv9615357599activity span {font-weight:700;}#yiv9615357599 #yiv9615357599activity span:first-child {text-transform:uppercase;}#yiv9615357599 #yiv9615357599activity span a {color:#5085b6;text-decoration:none;}#yiv9615357599 #yiv9615357599activity span span {color:#ff7900;}#yiv9615357599 #yiv9615357599activity span .yiv9615357599underline {text-decoration:underline;}#yiv9615357599 .yiv9615357599attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9615357599 .yiv9615357599attach div a {text-decoration:none;}#yiv9615357599 .yiv9615357599attach img {border:none;padding-right:5px;}#yiv9615357599 .yiv9615357599attach label {display:block;margin-bottom:5px;}#yiv9615357599 .yiv9615357599attach label a {text-decoration:none;}#yiv9615357599 blockquote {margin:0 0 0 4px;}#yiv9615357599 .yiv9615357599bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9615357599 .yiv9615357599bold a {text-decoration:none;}#yiv9615357599 dd.yiv9615357599last p a {font-family:Verdana;font-weight:700;}#yiv9615357599 dd.yiv9615357599last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9615357599 dd.yiv9615357599last p span.yiv9615357599yshortcuts {margin-right:0;}#yiv9615357599 div.yiv9615357599attach-table div div a {text-decoration:none;}#yiv9615357599 div.yiv9615357599attach-table {width:400px;}#yiv9615357599 div.yiv9615357599file-title a, #yiv9615357599 div.yiv9615357599file-title a:active, #yiv9615357599 div.yiv9615357599file-title a:hover, #yiv9615357599 div.yiv9615357599file-title a:visited {text-decoration:none;}#yiv9615357599 div.yiv9615357599photo-title a, #yiv9615357599 div.yiv9615357599photo-title a:active, #yiv9615357599 div.yiv9615357599photo-title a:hover, #yiv9615357599 div.yiv9615357599photo-title a:visited {text-decoration:none;}#yiv9615357599 div#yiv9615357599ygrp-mlmsg #yiv9615357599ygrp-msg p a span.yiv9615357599yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9615357599 .yiv9615357599green {color:#628c2a;}#yiv9615357599 .yiv9615357599MsoNormal {margin:0 0 0 0;}#yiv9615357599 o {font-size:0;}#yiv9615357599 #yiv9615357599photos div {float:left;width:72px;}#yiv9615357599 #yiv9615357599photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv9615357599 #yiv9615357599photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9615357599 #yiv9615357599reco-category {font-size:77%;}#yiv9615357599 #yiv9615357599reco-desc {font-size:77%;}#yiv9615357599 .yiv9615357599replbq {margin:4px;}#yiv9615357599 #yiv9615357599ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9615357599 #yiv9615357599ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9615357599 #yiv9615357599ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9615357599 #yiv9615357599ygrp-mlmsg select, #yiv9615357599 input, #yiv9615357599 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9615357599 #yiv9615357599ygrp-mlmsg pre, #yiv9615357599 code {font:115% monospace;}#yiv9615357599 #yiv9615357599ygrp-mlmsg * {line-height:1.22em;}#yiv9615357599 #yiv9615357599ygrp-mlmsg #yiv9615357599logo {padding-bottom:10px;}#yiv9615357599 #yiv9615357599ygrp-msg p a {font-family:Verdana;}#yiv9615357599 #yiv9615357599ygrp-msg p#yiv9615357599attach-count span {color:#1E66AE;font-weight:700;}#yiv9615357599 #yiv9615357599ygrp-reco #yiv9615357599reco-head {color:#ff7900;font-weight:700;}#yiv9615357599 #yiv9615357599ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9615357599 #yiv9615357599ygrp-sponsor #yiv9615357599ov li a {font-size:130%;text-decoration:none;}#yiv9615357599 #yiv9615357599ygrp-sponsor #yiv9615357599ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9615357599 #yiv9615357599ygrp-sponsor #yiv9615357599ov ul {margin:0;padding:0 0 0 8px;}#yiv9615357599 #yiv9615357599ygrp-text {font-family:Georgia;}#yiv9615357599 #yiv9615357599ygrp-text p {margin:0 0 1em 0;}#yiv9615357599 #yiv9615357599ygrp-text tt {font-size:120%;}#yiv9615357599 #yiv9615357599ygrp-vital ul li:last-child {border-right:none !important;}#yiv9615357599
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
> Posted by: Art Lorenzini <dbalorenzini@yahoo.com>
> ------------------------------------
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
> <*> To visit your group on the web, go to:
> http://groups.yahoo.com/group/MS_Access_Professionals/
>
> <*> Your email settings:
> Individual Email | Traditional
>
> <*> To change settings online go to:
> http://groups.yahoo.com/group/MS_Access_Professionals/join
> (Yahoo! ID required)
>
> <*> To change settings via email:
> MS_Access_Professionals-digest@yahoogroups.com
> MS_Access_Professionals-fullfeatured@yahoogroups.com
>
> <*> To unsubscribe from this group, send an email to:
> MS_Access_Professionals-unsubscribe@yahoogroups.com
>
> <*> Your use of Yahoo Groups is subject to:
> https://info.yahoo.com/legal/us/yahoo/utos/terms/
>
> From: MS_Access_Professionals@yahoogroups.com
> Date: Wed, 22 Jul 2015 21:28:55 +0000
> Subject: Re: [MS_AccessPros] Manipulating a integer in a string statement
>
> This is where I am right now.
> 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
> Which works up to the point that they do not select a bedroom count and that is when it pops the error.
> With Warm Regards, Arthur D. LorenziniIT System ManagerCheyenne River Housing AuthorityWk.(605)964-4265 Ext. 130Fax (605)964-1070
> "Anyone who claimed that old age had brought them patience was either lying or senile."
>
>
>
>
> From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
> To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
> Sent: Wednesday, July 22, 2015 4:19 PM
> Subject: RE: [MS_AccessPros] Manipulating a integer in a string statement
>
> You should check for
> If Len(strBedroomCount) > [Some Number Here] Then
> 'add this to your where clause
> End If
>
> Duane Hookom, MVP
> MS Access
>
>
>
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Wed, 22 Jul 2015 21:09:03 +0000
> Subject: Re: [MS_AccessPros] Manipulating a integer in a string statement
>
>
>
> I guess it was not quite right. We do not account for if they do not choose a bedroom count. Its causing the following error:
> Syntax error(missing operator) in query Expression 'ClasssificationType IN ('RENTAL') And BedroomCount='.
> With Warm Regards, Arthur D. LorenziniIT System ManagerCheyenne River Housing AuthorityWk.(605)964-4265 Ext. 130Fax (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
>
> Hi Art, 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.
>
>
>
> #yiv9615357599 #yiv9615357599 -- #yiv9615357599ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9615357599 #yiv9615357599ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9615357599 #yiv9615357599ygrp-mkp #yiv9615357599hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9615357599 #yiv9615357599ygrp-mkp #yiv9615357599ads {margin-bottom:10px;}#yiv9615357599 #yiv9615357599ygrp-mkp .yiv9615357599ad {padding:0 0;}#yiv9615357599 #yiv9615357599ygrp-mkp .yiv9615357599ad p {margin:0;}#yiv9615357599 #yiv9615357599ygrp-mkp .yiv9615357599ad a {color:#0000ff;text-decoration:none;}#yiv9615357599 #yiv9615357599ygrp-sponsor #yiv9615357599ygrp-lc {font-family:Arial;}#yiv9615357599 #yiv9615357599ygrp-sponsor #yiv9615357599ygrp-lc #yiv9615357599hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9615357599 #yiv9615357599ygrp-sponsor #yiv9615357599ygrp-lc .yiv9615357599ad {margin-bottom:10px;padding:0 0;}#yiv9615357599 #yiv9615357599actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9615357599 #yiv9615357599activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9615357599 #yiv9615357599activity span {font-weight:700;}#yiv9615357599 #yiv9615357599activity span:first-child {text-transform:uppercase;}#yiv9615357599 #yiv9615357599activity span a {color:#5085b6;text-decoration:none;}#yiv9615357599 #yiv9615357599activity span span {color:#ff7900;}#yiv9615357599 #yiv9615357599activity span .yiv9615357599underline {text-decoration:underline;}#yiv9615357599 .yiv9615357599attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9615357599 .yiv9615357599attach div a {text-decoration:none;}#yiv9615357599 .yiv9615357599attach img {border:none;padding-right:5px;}#yiv9615357599 .yiv9615357599attach label {display:block;margin-bottom:5px;}#yiv9615357599 .yiv9615357599attach label a {text-decoration:none;}#yiv9615357599 blockquote {margin:0 0 0 4px;}#yiv9615357599 .yiv9615357599bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9615357599 .yiv9615357599bold a {text-decoration:none;}#yiv9615357599 dd.yiv9615357599last p a {font-family:Verdana;font-weight:700;}#yiv9615357599 dd.yiv9615357599last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9615357599 dd.yiv9615357599last p span.yiv9615357599yshortcuts {margin-right:0;}#yiv9615357599 div.yiv9615357599attach-table div div a {text-decoration:none;}#yiv9615357599 div.yiv9615357599attach-table {width:400px;}#yiv9615357599 div.yiv9615357599file-title a, #yiv9615357599 div.yiv9615357599file-title a:active, #yiv9615357599 div.yiv9615357599file-title a:hover, #yiv9615357599 div.yiv9615357599file-title a:visited {text-decoration:none;}#yiv9615357599 div.yiv9615357599photo-title a, #yiv9615357599 div.yiv9615357599photo-title a:active, #yiv9615357599 div.yiv9615357599photo-title a:hover, #yiv9615357599 div.yiv9615357599photo-title a:visited {text-decoration:none;}#yiv9615357599 div#yiv9615357599ygrp-mlmsg #yiv9615357599ygrp-msg p a span.yiv9615357599yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9615357599 .yiv9615357599green {color:#628c2a;}#yiv9615357599 .yiv9615357599MsoNormal {margin:0 0 0 0;}#yiv9615357599 o {font-size:0;}#yiv9615357599 #yiv9615357599photos div {float:left;width:72px;}#yiv9615357599 #yiv9615357599photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv9615357599 #yiv9615357599photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9615357599 #yiv9615357599reco-category {font-size:77%;}#yiv9615357599 #yiv9615357599reco-desc {font-size:77%;}#yiv9615357599 .yiv9615357599replbq {margin:4px;}#yiv9615357599 #yiv9615357599ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9615357599 #yiv9615357599ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9615357599 #yiv9615357599ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9615357599 #yiv9615357599ygrp-mlmsg select, #yiv9615357599 input, #yiv9615357599 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9615357599 #yiv9615357599ygrp-mlmsg pre, #yiv9615357599 code {font:115% monospace;}#yiv9615357599 #yiv9615357599ygrp-mlmsg * {line-height:1.22em;}#yiv9615357599 #yiv9615357599ygrp-mlmsg #yiv9615357599logo {padding-bottom:10px;}#yiv9615357599 #yiv9615357599ygrp-msg p a {font-family:Verdana;}#yiv9615357599 #yiv9615357599ygrp-msg p#yiv9615357599attach-count span {color:#1E66AE;font-weight:700;}#yiv9615357599 #yiv9615357599ygrp-reco #yiv9615357599reco-head {color:#ff7900;font-weight:700;}#yiv9615357599 #yiv9615357599ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9615357599 #yiv9615357599ygrp-sponsor #yiv9615357599ov li a {font-size:130%;text-decoration:none;}#yiv9615357599 #yiv9615357599ygrp-sponsor #yiv9615357599ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9615357599 #yiv9615357599ygrp-sponsor #yiv9615357599ov ul {margin:0;padding:0 0 0 8px;}#yiv9615357599 #yiv9615357599ygrp-text {font-family:Georgia;}#yiv9615357599 #yiv9615357599ygrp-text p {margin:0 0 1em 0;}#yiv9615357599 #yiv9615357599ygrp-text tt {font-size:120%;}#yiv9615357599 #yiv9615357599ygrp-vital ul li:last-child {border-right:none !important;}#yiv9615357599
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
> Posted by: Art Lorenzini <dbalorenzini@yahoo.com>
> ------------------------------------
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
> <*> To visit your group on the web, go to:
> http://groups.yahoo.com/group/MS_Access_Professionals/
>
> <*> Your email settings:
> Individual Email | Traditional
>
> <*> To change settings online go to:
> http://groups.yahoo.com/group/MS_Access_Professionals/join
> (Yahoo! ID required)
>
> <*> To change settings via email:
> MS_Access_Professionals-digest@yahoogroups.com
> MS_Access_Professionals-fullfeatured@yahoogroups.com
>
> <*> To unsubscribe from this group, send an email to:
> MS_Access_Professionals-unsubscribe@yahoogroups.com
>
> <*> Your use of Yahoo Groups is subject to:
> https://info.yahoo.com/legal/us/yahoo/utos/terms/
>
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (11) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar