Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

LIST BOX HEADACHE FOR NEWBIE

Discussion in 'Information Technology' started by talus7, Jul 28, 2006.

  1. talus7

    talus7
    Expand Collapse
    Guest

    I have a form that I created which contains a list box and a list of
    records that exist in a table. I would like for an individual to select
    an item in the list box and click a command button to open up a query
    that will open the items that are queried in a date range.
    This is sample code that I found to run on the command button and list
    box, but I do not know how to reference or change it for the date
    types.

    ' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    ' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryMultiSelect")
    ' Loop through the selected items in the list box and build a text
    string
    If Me!lstRegions.ItemsSelected.Count > 0 Then
    For Each varItem In Me!lstRegions.ItemsSelected
    strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
    & Me!lstRegions.ItemData(varItem) & Chr(34) &
    "OR "
    Next varItem
    strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    Else
    strCriteria = "tblData.Region Like '*'"
    End If
    ' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM tblData " & _
    "WHERE " & strCriteria & ";"
    ' Apply the new SQL statement to the query
    qdf.SQL = strSQL
    ' Open the query
    DoCmd.OpenQuery "qryMultiSelect"
    ' Empty the memory
    Set db = Nothing
    Set qdf = Nothing

    Any help will be greatly appreciated.
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News How The Left & Indian Nationalists Distorted The History Of Sikh Gadhar Movement - Breaking News Sep 21, 2016
    Interfaith Materialists And Its Discontents Interfaith Dialogues Jun 10, 2016
    Sikhi Quasi-Pragmatists And Quasi-Spiritualists, Care To Explain? Sikh Sikhi Sikhism Jun 6, 2016
    Cholesterol Is Finally Officially Removed From "naughty List" Health & Nutrition Feb 11, 2016
    Listening to Gurbani while driving?? Sikh Rehat Maryada Jan 13, 2016

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    If that's an actual copy-and-paste of your code, you're missing a space in
    front of the OR in "OR ": it should be

    strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
    & Me!lstRegions.ItemData(varItem) & Chr(34) & " OR "

    Also, since you're adding 4 extra characters at the end, technically it
    should be strCriteria = Left(strCriteria, Len(strCriteria) - 4), but that's
    not really important, since all it will do is add an extra blank.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "talus7" <talus-7@msn.com> wrote in message
    news:1151614647.220751.278380@j72g2000cwa.googlegroups.com...
    >I have a form that I created which contains a list box and a list of
    > records that exist in a table. I would like for an individual to select
    > an item in the list box and click a command button to open up a query
    > that will open the items that are queried in a date range.
    > This is sample code that I found to run on the command button and list
    > box, but I do not know how to reference or change it for the date
    > types.
    >
    > ' Declare variables
    > Dim db As DAO.Database
    > Dim qdf As DAO.QueryDef
    > Dim varItem As Variant
    > Dim strCriteria As String
    > Dim strSQL As String
    > ' Get the database and stored query
    > Set db = CurrentDb()
    > Set qdf = db.QueryDefs("qryMultiSelect")
    > ' Loop through the selected items in the list box and build a text
    > string
    > If Me!lstRegions.ItemsSelected.Count > 0 Then
    > For Each varItem In Me!lstRegions.ItemsSelected
    > strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
    > & Me!lstRegions.ItemData(varItem) & Chr(34) &
    > "OR "
    > Next varItem
    > strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    > Else
    > strCriteria = "tblData.Region Like '*'"
    > End If
    > ' Build the new SQL statement incorporating the string
    > strSQL = "SELECT * FROM tblData " & _
    > "WHERE " & strCriteria & ";"
    > ' Apply the new SQL statement to the query
    > qdf.SQL = strSQL
    > ' Open the query
    > DoCmd.OpenQuery "qryMultiSelect"
    > ' Empty the memory
    > Set db = Nothing
    > Set qdf = Nothing
    >
    > Any help will be greatly appreciated.
    >
     
  4. talus7

    talus7
    Expand Collapse
    Guest

    Thanks Douglas for your prompt reply actually that is the code line
    that i am having trouble with, and I would actually like to change it
    from a text string to a date string. I actually copied that from a
    sample database, but since I am relatively new to VBA I am having
    trouble making the conversion.

    Douglas J. Steele wrote:
    > If that's an actual copy-and-paste of your code, you're missing a space in
    > front of the OR in "OR ": it should be
    >
    > strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
    > & Me!lstRegions.ItemData(varItem) & Chr(34) & " OR "
    >
    > Also, since you're adding 4 extra characters at the end, technically it
    > should be strCriteria = Left(strCriteria, Len(strCriteria) - 4), but that's
    > not really important, since all it will do is add an extra blank.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "talus7" <talus-7@msn.com> wrote in message
    > news:1151614647.220751.278380@j72g2000cwa.googlegroups.com...
    > >I have a form that I created which contains a list box and a list of
    > > records that exist in a table. I would like for an individual to select
    > > an item in the list box and click a command button to open up a query
    > > that will open the items that are queried in a date range.
    > > This is sample code that I found to run on the command button and list
    > > box, but I do not know how to reference or change it for the date
    > > types.
    > >
    > > ' Declare variables
    > > Dim db As DAO.Database
    > > Dim qdf As DAO.QueryDef
    > > Dim varItem As Variant
    > > Dim strCriteria As String
    > > Dim strSQL As String
    > > ' Get the database and stored query
    > > Set db = CurrentDb()
    > > Set qdf = db.QueryDefs("qryMultiSelect")
    > > ' Loop through the selected items in the list box and build a text
    > > string
    > > If Me!lstRegions.ItemsSelected.Count > 0 Then
    > > For Each varItem In Me!lstRegions.ItemsSelected
    > > strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
    > > & Me!lstRegions.ItemData(varItem) & Chr(34) &
    > > "OR "
    > > Next varItem
    > > strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    > > Else
    > > strCriteria = "tblData.Region Like '*'"
    > > End If
    > > ' Build the new SQL statement incorporating the string
    > > strSQL = "SELECT * FROM tblData " & _
    > > "WHERE " & strCriteria & ";"
    > > ' Apply the new SQL statement to the query
    > > qdf.SQL = strSQL
    > > ' Open the query
    > > DoCmd.OpenQuery "qryMultiSelect"
    > > ' Empty the memory
    > > Set db = Nothing
    > > Set qdf = Nothing
    > >
    > > Any help will be greatly appreciated.
    > >
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    You mean that rather than Region = 'E" OR Region = 'W", you want
    TransactionDate = #06/10/2006# OR TransactionDate = #06/11/2006#?

    strCriteria = strCriteria & "tblData.TransactionDate = " & _
    Format(Me!lstDates.ItemData(varItem), "\#mm\/dd\/yyyy\#") & _
    " OR "


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "talus7" <talus-7@msn.com> wrote in message
    news:1151621222.127685.241330@75g2000cwc.googlegroups.com...
    > Thanks Douglas for your prompt reply actually that is the code line
    > that i am having trouble with, and I would actually like to change it
    > from a text string to a date string. I actually copied that from a
    > sample database, but since I am relatively new to VBA I am having
    > trouble making the conversion.
    >
    > Douglas J. Steele wrote:
    >> If that's an actual copy-and-paste of your code, you're missing a space
    >> in
    >> front of the OR in "OR ": it should be
    >>
    >> strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
    >> & Me!lstRegions.ItemData(varItem) & Chr(34) & " OR "
    >>
    >> Also, since you're adding 4 extra characters at the end, technically it
    >> should be strCriteria = Left(strCriteria, Len(strCriteria) - 4), but
    >> that's
    >> not really important, since all it will do is add an extra blank.
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no private e-mails, please)
    >>
    >>
    >> "talus7" <talus-7@msn.com> wrote in message
    >> news:1151614647.220751.278380@j72g2000cwa.googlegroups.com...
    >> >I have a form that I created which contains a list box and a list of
    >> > records that exist in a table. I would like for an individual to select
    >> > an item in the list box and click a command button to open up a query
    >> > that will open the items that are queried in a date range.
    >> > This is sample code that I found to run on the command button and list
    >> > box, but I do not know how to reference or change it for the date
    >> > types.
    >> >
    >> > ' Declare variables
    >> > Dim db As DAO.Database
    >> > Dim qdf As DAO.QueryDef
    >> > Dim varItem As Variant
    >> > Dim strCriteria As String
    >> > Dim strSQL As String
    >> > ' Get the database and stored query
    >> > Set db = CurrentDb()
    >> > Set qdf = db.QueryDefs("qryMultiSelect")
    >> > ' Loop through the selected items in the list box and build a text
    >> > string
    >> > If Me!lstRegions.ItemsSelected.Count > 0 Then
    >> > For Each varItem In Me!lstRegions.ItemsSelected
    >> > strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
    >> > & Me!lstRegions.ItemData(varItem) & Chr(34) &
    >> > "OR "
    >> > Next varItem
    >> > strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    >> > Else
    >> > strCriteria = "tblData.Region Like '*'"
    >> > End If
    >> > ' Build the new SQL statement incorporating the string
    >> > strSQL = "SELECT * FROM tblData " & _
    >> > "WHERE " & strCriteria & ";"
    >> > ' Apply the new SQL statement to the query
    >> > qdf.SQL = strSQL
    >> > ' Open the query
    >> > DoCmd.OpenQuery "qryMultiSelect"
    >> > ' Empty the memory
    >> > Set db = Nothing
    >> > Set qdf = Nothing
    >> >
    >> > Any help will be greatly appreciated.
    >> >

    >
     
  6. talus7

    talus7
    Expand Collapse
    Guest

    YES YES YES! Douglas that worked!!!!!! You're the man! Thank you so
    much for your input, and time.
    Douglas J. Steele wrote:
    > You mean that rather than Region = 'E" OR Region = 'W", you want
    > TransactionDate = #06/10/2006# OR TransactionDate = #06/11/2006#?
    >
    > strCriteria = strCriteria & "tblData.TransactionDate = " & _
    > Format(Me!lstDates.ItemData(varItem), "\#mm\/dd\/yyyy\#") & _
    > " OR "
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "talus7" <talus-7@msn.com> wrote in message
    > news:1151621222.127685.241330@75g2000cwc.googlegroups.com...
    > > Thanks Douglas for your prompt reply actually that is the code line
    > > that i am having trouble with, and I would actually like to change it
    > > from a text string to a date string. I actually copied that from a
    > > sample database, but since I am relatively new to VBA I am having
    > > trouble making the conversion.
    > >
    > > Douglas J. Steele wrote:
    > >> If that's an actual copy-and-paste of your code, you're missing a space
    > >> in
    > >> front of the OR in "OR ": it should be
    > >>
    > >> strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
    > >> & Me!lstRegions.ItemData(varItem) & Chr(34) & " OR "
    > >>
    > >> Also, since you're adding 4 extra characters at the end, technically it
    > >> should be strCriteria = Left(strCriteria, Len(strCriteria) - 4), but
    > >> that's
    > >> not really important, since all it will do is add an extra blank.
    > >>
    > >> --
    > >> Doug Steele, Microsoft Access MVP
    > >> http://I.Am/DougSteele
    > >> (no private e-mails, please)
    > >>
    > >>
    > >> "talus7" <talus-7@msn.com> wrote in message
    > >> news:1151614647.220751.278380@j72g2000cwa.googlegroups.com...
    > >> >I have a form that I created which contains a list box and a list of
    > >> > records that exist in a table. I would like for an individual to select
    > >> > an item in the list box and click a command button to open up a query
    > >> > that will open the items that are queried in a date range.
    > >> > This is sample code that I found to run on the command button and list
    > >> > box, but I do not know how to reference or change it for the date
    > >> > types.
    > >> >
    > >> > ' Declare variables
    > >> > Dim db As DAO.Database
    > >> > Dim qdf As DAO.QueryDef
    > >> > Dim varItem As Variant
    > >> > Dim strCriteria As String
    > >> > Dim strSQL As String
    > >> > ' Get the database and stored query
    > >> > Set db = CurrentDb()
    > >> > Set qdf = db.QueryDefs("qryMultiSelect")
    > >> > ' Loop through the selected items in the list box and build a text
    > >> > string
    > >> > If Me!lstRegions.ItemsSelected.Count > 0 Then
    > >> > For Each varItem In Me!lstRegions.ItemsSelected
    > >> > strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
    > >> > & Me!lstRegions.ItemData(varItem) & Chr(34) &
    > >> > "OR "
    > >> > Next varItem
    > >> > strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    > >> > Else
    > >> > strCriteria = "tblData.Region Like '*'"
    > >> > End If
    > >> > ' Build the new SQL statement incorporating the string
    > >> > strSQL = "SELECT * FROM tblData " & _
    > >> > "WHERE " & strCriteria & ";"
    > >> > ' Apply the new SQL statement to the query
    > >> > qdf.SQL = strSQL
    > >> > ' Open the query
    > >> > DoCmd.OpenQuery "qryMultiSelect"
    > >> > ' Empty the memory
    > >> > Set db = Nothing
    > >> > Set qdf = Nothing
    > >> >
    > >> > Any help will be greatly appreciated.
    > >> >

    > >
     
  7. talus7

    talus7
    Expand Collapse
    Guest

    OK I got this to work, and then I realised after running the code that
    I actually need to use multiple criteria to run the query right. I
    would need to separate everything with a text and date criteria. Not
    only do I need dates Between ## And ##, but also need to separate based
    on the text field I have referenced. Any suggestions?
    For example I have a country field that I need to separate items from
    date and country.

    Thanks!
    talus7 wrote:
    > YES YES YES! Douglas that worked!!!!!! You're the man! Thank you so
    > much for your input, and time.
    > Douglas J. Steele wrote:
    > > You mean that rather than Region = 'E" OR Region = 'W", you want
    > > TransactionDate = #06/10/2006# OR TransactionDate = #06/11/2006#?
    > >
    > > strCriteria = strCriteria & "tblData.TransactionDate = " & _
    > > Format(Me!lstDates.ItemData(varItem), "\#mm\/dd\/yyyy\#") & _
    > > " OR "
    > >
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no private e-mails, please)
    > >
    > >
    > > "talus7" <talus-7@msn.com> wrote in message
    > > news:1151621222.127685.241330@75g2000cwc.googlegroups.com...
    > > > Thanks Douglas for your prompt reply actually that is the code line
    > > > that i am having trouble with, and I would actually like to change it
    > > > from a text string to a date string. I actually copied that from a
    > > > sample database, but since I am relatively new to VBA I am having
    > > > trouble making the conversion.
    > > >
    > > > Douglas J. Steele wrote:
    > > >> If that's an actual copy-and-paste of your code, you're missing a space
    > > >> in
    > > >> front of the OR in "OR ": it should be
    > > >>
    > > >> strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
    > > >> & Me!lstRegions.ItemData(varItem) & Chr(34) & " OR "
    > > >>
    > > >> Also, since you're adding 4 extra characters at the end, technically it
    > > >> should be strCriteria = Left(strCriteria, Len(strCriteria) - 4), but
    > > >> that's
    > > >> not really important, since all it will do is add an extra blank.
    > > >>
    > > >> --
    > > >> Doug Steele, Microsoft Access MVP
    > > >> http://I.Am/DougSteele
    > > >> (no private e-mails, please)
    > > >>
    > > >>
    > > >> "talus7" <talus-7@msn.com> wrote in message
    > > >> news:1151614647.220751.278380@j72g2000cwa.googlegroups.com...
    > > >> >I have a form that I created which contains a list box and a list of
    > > >> > records that exist in a table. I would like for an individual to select
    > > >> > an item in the list box and click a command button to open up a query
    > > >> > that will open the items that are queried in a date range.
    > > >> > This is sample code that I found to run on the command button and list
    > > >> > box, but I do not know how to reference or change it for the date
    > > >> > types.
    > > >> >
    > > >> > ' Declare variables
    > > >> > Dim db As DAO.Database
    > > >> > Dim qdf As DAO.QueryDef
    > > >> > Dim varItem As Variant
    > > >> > Dim strCriteria As String
    > > >> > Dim strSQL As String
    > > >> > ' Get the database and stored query
    > > >> > Set db = CurrentDb()
    > > >> > Set qdf = db.QueryDefs("qryMultiSelect")
    > > >> > ' Loop through the selected items in the list box and build a text
    > > >> > string
    > > >> > If Me!lstRegions.ItemsSelected.Count > 0 Then
    > > >> > For Each varItem In Me!lstRegions.ItemsSelected
    > > >> > strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
    > > >> > & Me!lstRegions.ItemData(varItem) & Chr(34) &
    > > >> > "OR "
    > > >> > Next varItem
    > > >> > strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    > > >> > Else
    > > >> > strCriteria = "tblData.Region Like '*'"
    > > >> > End If
    > > >> > ' Build the new SQL statement incorporating the string
    > > >> > strSQL = "SELECT * FROM tblData " & _
    > > >> > "WHERE " & strCriteria & ";"
    > > >> > ' Apply the new SQL statement to the query
    > > >> > qdf.SQL = strSQL
    > > >> > ' Open the query
    > > >> > DoCmd.OpenQuery "qryMultiSelect"
    > > >> > ' Empty the memory
    > > >> > Set db = Nothing
    > > >> > Set qdf = Nothing
    > > >> >
    > > >> > Any help will be greatly appreciated.
    > > >> >
    > > >
     
  8. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    I'm not really following. Can you give a concrete example?

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "talus7" <talus-7@msn.com> wrote in message
    news:1152224731.783116.194910@p79g2000cwp.googlegroups.com...
    > OK I got this to work, and then I realised after running the code that
    > I actually need to use multiple criteria to run the query right. I
    > would need to separate everything with a text and date criteria. Not
    > only do I need dates Between ## And ##, but also need to separate based
    > on the text field I have referenced. Any suggestions?
    > For example I have a country field that I need to separate items from
    > date and country.
    >
    > Thanks!
    > talus7 wrote:
    >> YES YES YES! Douglas that worked!!!!!! You're the man! Thank you so
    >> much for your input, and time.
    >> Douglas J. Steele wrote:
    >> > You mean that rather than Region = 'E" OR Region = 'W", you want
    >> > TransactionDate = #06/10/2006# OR TransactionDate = #06/11/2006#?
    >> >
    >> > strCriteria = strCriteria & "tblData.TransactionDate = " & _
    >> > Format(Me!lstDates.ItemData(varItem), "\#mm\/dd\/yyyy\#") & _
    >> > " OR "
    >> >
    >> >
    >> > --
    >> > Doug Steele, Microsoft Access MVP
    >> > http://I.Am/DougSteele
    >> > (no private e-mails, please)
    >> >
    >> >
    >> > "talus7" <talus-7@msn.com> wrote in message
    >> > news:1151621222.127685.241330@75g2000cwc.googlegroups.com...
    >> > > Thanks Douglas for your prompt reply actually that is the code line
    >> > > that i am having trouble with, and I would actually like to change it
    >> > > from a text string to a date string. I actually copied that from a
    >> > > sample database, but since I am relatively new to VBA I am having
    >> > > trouble making the conversion.
    >> > >
    >> > > Douglas J. Steele wrote:
    >> > >> If that's an actual copy-and-paste of your code, you're missing a
    >> > >> space
    >> > >> in
    >> > >> front of the OR in "OR ": it should be
    >> > >>
    >> > >> strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
    >> > >> & Me!lstRegions.ItemData(varItem) & Chr(34) & " OR "
    >> > >>
    >> > >> Also, since you're adding 4 extra characters at the end, technically
    >> > >> it
    >> > >> should be strCriteria = Left(strCriteria, Len(strCriteria) - 4), but
    >> > >> that's
    >> > >> not really important, since all it will do is add an extra blank.
    >> > >>
    >> > >> --
    >> > >> Doug Steele, Microsoft Access MVP
    >> > >> http://I.Am/DougSteele
    >> > >> (no private e-mails, please)
    >> > >>
    >> > >>
    >> > >> "talus7" <talus-7@msn.com> wrote in message
    >> > >> news:1151614647.220751.278380@j72g2000cwa.googlegroups.com...
    >> > >> >I have a form that I created which contains a list box and a list
    >> > >> >of
    >> > >> > records that exist in a table. I would like for an individual to
    >> > >> > select
    >> > >> > an item in the list box and click a command button to open up a
    >> > >> > query
    >> > >> > that will open the items that are queried in a date range.
    >> > >> > This is sample code that I found to run on the command button and
    >> > >> > list
    >> > >> > box, but I do not know how to reference or change it for the date
    >> > >> > types.
    >> > >> >
    >> > >> > ' Declare variables
    >> > >> > Dim db As DAO.Database
    >> > >> > Dim qdf As DAO.QueryDef
    >> > >> > Dim varItem As Variant
    >> > >> > Dim strCriteria As String
    >> > >> > Dim strSQL As String
    >> > >> > ' Get the database and stored query
    >> > >> > Set db = CurrentDb()
    >> > >> > Set qdf = db.QueryDefs("qryMultiSelect")
    >> > >> > ' Loop through the selected items in the list box and build a text
    >> > >> > string
    >> > >> > If Me!lstRegions.ItemsSelected.Count > 0 Then
    >> > >> > For Each varItem In Me!lstRegions.ItemsSelected
    >> > >> > strCriteria = strCriteria & "tblData.Region = " &
    >> > >> > Chr(34) _
    >> > >> > & Me!lstRegions.ItemData(varItem) &
    >> > >> > Chr(34) &
    >> > >> > "OR "
    >> > >> > Next varItem
    >> > >> > strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    >> > >> > Else
    >> > >> > strCriteria = "tblData.Region Like '*'"
    >> > >> > End If
    >> > >> > ' Build the new SQL statement incorporating the string
    >> > >> > strSQL = "SELECT * FROM tblData " & _
    >> > >> > "WHERE " & strCriteria & ";"
    >> > >> > ' Apply the new SQL statement to the query
    >> > >> > qdf.SQL = strSQL
    >> > >> > ' Open the query
    >> > >> > DoCmd.OpenQuery "qryMultiSelect"
    >> > >> > ' Empty the memory
    >> > >> > Set db = Nothing
    >> > >> > Set qdf = Nothing
    >> > >> >
    >> > >> > Any help will be greatly appreciated.
    >> > >> >
    >> > >

    >
     
  9. talus7

    talus7
    Expand Collapse
    Guest

    OK I have two fields that I would like to query to show information
    from my table. The first field you helped me right the code out to
    query based on the dates. Well after running it I realized that it will
    show me all of the records saved for those dates. The second field I
    would like to query as a text string. So I would like to add to the
    present code a reference to that other field to be queried as a text
    string. Does this explanation help?

    RP

    Douglas J. Steele wrote:
    > I'm not really following. Can you give a concrete example?
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "talus7" <talus-7@msn.com> wrote in message
    > news:1152224731.783116.194910@p79g2000cwp.googlegroups.com...
    > > OK I got this to work, and then I realised after running the code that
    > > I actually need to use multiple criteria to run the query right. I
    > > would need to separate everything with a text and date criteria. Not
    > > only do I need dates Between ## And ##, but also need to separate based
    > > on the text field I have referenced. Any suggestions?
    > > For example I have a country field that I need to separate items from
    > > date and country.
    > >
    > > Thanks!
    > > talus7 wrote:
    > >> YES YES YES! Douglas that worked!!!!!! You're the man! Thank you so
    > >> much for your input, and time.
    > >> Douglas J. Steele wrote:
    > >> > You mean that rather than Region = 'E" OR Region = 'W", you want
    > >> > TransactionDate = #06/10/2006# OR TransactionDate = #06/11/2006#?
    > >> >
    > >> > strCriteria = strCriteria & "tblData.TransactionDate = " & _
    > >> > Format(Me!lstDates.ItemData(varItem), "\#mm\/dd\/yyyy\#") & _
    > >> > " OR "
    > >> >
    > >> >
    > >> > --
    > >> > Doug Steele, Microsoft Access MVP
    > >> > http://I.Am/DougSteele
    > >> > (no private e-mails, please)
    > >> >
    > >> >
    > >> > "talus7" <talus-7@msn.com> wrote in message
    > >> > news:1151621222.127685.241330@75g2000cwc.googlegroups.com...
    > >> > > Thanks Douglas for your prompt reply actually that is the code line
    > >> > > that i am having trouble with, and I would actually like to change it
    > >> > > from a text string to a date string. I actually copied that from a
    > >> > > sample database, but since I am relatively new to VBA I am having
    > >> > > trouble making the conversion.
    > >> > >
    > >> > > Douglas J. Steele wrote:
    > >> > >> If that's an actual copy-and-paste of your code, you're missing a
    > >> > >> space
    > >> > >> in
    > >> > >> front of the OR in "OR ": it should be
    > >> > >>
    > >> > >> strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
    > >> > >> & Me!lstRegions.ItemData(varItem) & Chr(34) & " OR "
    > >> > >>
    > >> > >> Also, since you're adding 4 extra characters at the end, technically
    > >> > >> it
    > >> > >> should be strCriteria = Left(strCriteria, Len(strCriteria) - 4), but
    > >> > >> that's
    > >> > >> not really important, since all it will do is add an extra blank.
    > >> > >>
    > >> > >> --
    > >> > >> Doug Steele, Microsoft Access MVP
    > >> > >> http://I.Am/DougSteele
    > >> > >> (no private e-mails, please)
    > >> > >>
    > >> > >>
    > >> > >> "talus7" <talus-7@msn.com> wrote in message
    > >> > >> news:1151614647.220751.278380@j72g2000cwa.googlegroups.com...
    > >> > >> >I have a form that I created which contains a list box and a list
    > >> > >> >of
    > >> > >> > records that exist in a table. I would like for an individual to
    > >> > >> > select
    > >> > >> > an item in the list box and click a command button to open up a
    > >> > >> > query
    > >> > >> > that will open the items that are queried in a date range.
    > >> > >> > This is sample code that I found to run on the command button and
    > >> > >> > list
    > >> > >> > box, but I do not know how to reference or change it for the date
    > >> > >> > types.
    > >> > >> >
    > >> > >> > ' Declare variables
    > >> > >> > Dim db As DAO.Database
    > >> > >> > Dim qdf As DAO.QueryDef
    > >> > >> > Dim varItem As Variant
    > >> > >> > Dim strCriteria As String
    > >> > >> > Dim strSQL As String
    > >> > >> > ' Get the database and stored query
    > >> > >> > Set db = CurrentDb()
    > >> > >> > Set qdf = db.QueryDefs("qryMultiSelect")
    > >> > >> > ' Loop through the selected items in the list box and build a text
    > >> > >> > string
    > >> > >> > If Me!lstRegions.ItemsSelected.Count > 0 Then
    > >> > >> > For Each varItem In Me!lstRegions.ItemsSelected
    > >> > >> > strCriteria = strCriteria & "tblData.Region = " &
    > >> > >> > Chr(34) _
    > >> > >> > & Me!lstRegions.ItemData(varItem) &
    > >> > >> > Chr(34) &
    > >> > >> > "OR "
    > >> > >> > Next varItem
    > >> > >> > strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    > >> > >> > Else
    > >> > >> > strCriteria = "tblData.Region Like '*'"
    > >> > >> > End If
    > >> > >> > ' Build the new SQL statement incorporating the string
    > >> > >> > strSQL = "SELECT * FROM tblData " & _
    > >> > >> > "WHERE " & strCriteria & ";"
    > >> > >> > ' Apply the new SQL statement to the query
    > >> > >> > qdf.SQL = strSQL
    > >> > >> > ' Open the query
    > >> > >> > DoCmd.OpenQuery "qryMultiSelect"
    > >> > >> > ' Empty the memory
    > >> > >> > Set db = Nothing
    > >> > >> > Set qdf = Nothing
    > >> > >> >
    > >> > >> > Any help will be greatly appreciated.
    > >> > >> >
    > >> > >

    > >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page