LIST BOX HEADACHE FOR NEWBIE

[talus7]

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.

 

[Douglas J. Steele]

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.

 

[talus7]

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]

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 "

 

[talus7]

YES YES YES! Douglas that worked!!!!!! You're the man! Thank you so
much for your input, and time.

 

[talus7]

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!

 

[Douglas J. Steele]

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

 

[talus7]

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