Results 1 to 6 of 6

Combining Two criteria to Form StrCrteria to be used as a criteria in query

  1. #1
    tirfan is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    14

    Combining Two criteria to Form StrCrteria to be used as a criteria in query

    Dear Experts, i want to join two criteria in vba to make one strcriteria which will be feeded to query to get the result my code is as below. if i test one by one criteria then its working how can i combile the both criterias

    my vba code is
    Private Sub cmdApplyFilter_Click()
    'Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim StrSite As String
    Dim StrSubconnature As String
    Dim strSQL As String
    ' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Query4")
    ' Loop through the selected items in the list box and build a text string
    If Me!lstSite.ItemsSelected.Count > 0 Then
    For Each varItem In Me!lstSite.ItemsSelected
    StrSite = StrSite & "Query2.Sitecode = " & Chr(34) _
    & Me!lstSite.ItemData(varItem) & Chr(34) & "OR "
    Next varItem
    StrSite = Left(StrSite, Len(StrSite) - 3)
    Else
    StrSite = "Query2.Sitecode Like '*'"
    End If
    If Me!lstSubconnature.ItemsSelected.Count > 0 Then
    For Each varItem In Me!lstSubconnature.ItemsSelected
    StrSubconnature = StrSubconnature & "Query2.nature = " & Chr(34) _
    & Me!lstSubconnature.ItemData(varItem) & Chr(34) & "OR "
    Next varItem
    StrSubconnature = Left(StrSubconnature, Len(StrSubconnature) - 3)
    Else
    StrSubconnature = "Query2.nature Like '*'"
    End If
    strCriteria = StrSite & Strsubconnature

    ' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM Query2 " & _
    "WHERE " & strCriteria & ";"
    ' Apply the new SQL statement to the query
    qdf.SQL = strSQL
    ' Open the query
    DoCmd.Close acForm, "frmReportFilter3"
    DoCmd.OpenReport "Rpt_SubconBreakdown", acViewPreview, "", "", acNormal


    ' Empty the memory
    Set db = Nothing
    Set qdf = Nothing

    End Sub

  2. #2
    tirfan is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    14
    https://www.accessforums.net/queries...ria-27020.html

    My Problem is near to this post. only he is using one multi-select-list box for query criteria of one field and i am using two or may be need threee multiselect list box to provide criteria for 3 diffrenet fields in query. waiting for your replies.
    Last edited by tirfan; 09-04-2012 at 03:30 AM.

  3. #3
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    Depending on your criteria, should the StrSite and Strsubconnature be combined with " AND " or " OR " ? Like the strCriteria = StrSite & " AND " & Strsubconnature

  4. #4
    tirfan is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    14
    Both StrSitecode is Criteria for Sitecode Field from Table and StrSubconnature is Criteria for nature field from the same table. these two criteria will be used to to two diffrent field to make query. if i test my Data base. with only one str at one time. both is working. but i d'nt know how to combine the two criteria for two diffrent fields.

  5. #5
    tirfan is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    14
    Dear Experts,

    Now File is also attached, and its working for one Multilist. but i tried my best to combine the code of second Str which is StrSubconnature. but its not working. i am trying from 2 days but i am not able to get any solution for this file. kindly required urgent hlep.


    Many Thanks in Advance.
    Attached Files Attached Files

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,578
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-02-2012, 09:44 AM
  2. Using form fields as query criteria
    By bener in forum Queries
    Replies: 3
    Last Post: 10-28-2011, 12:54 PM
  3. Query criteria from form (just the year)
    By user622 in forum Forms
    Replies: 2
    Last Post: 07-26-2011, 09:51 AM
  4. Replies: 1
    Last Post: 07-13-2011, 11:00 AM
  5. Query criteria in a form
    By sefiroths in forum Queries
    Replies: 1
    Last Post: 12-23-2009, 05:15 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums