Welcome to SPN

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

Sign Up Now!

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.
    > >> > >> >
    > >> > >

    > >
     

Share This Page