I am posting again, no one seems to be able to help

[jfgambit]

I have posted this problem before and looked through the past posts, but I still cannot seem to solve this problem, so I have attached a copy of the db for review by the Access Gurus.



My problem is this:
There are several combo boxes on a form (non of them are required) as well as a combo box where a user can select the Buyer IDs they are interested in getting data for. As they select the Buyer IDs a listbox is populated with the IDs. Upon selection of a date range (the Start Date and End Data are required) an unbound textbox is populated with each selected Buyer ID from the listbox (example: B51790 or B16210). If only one Buyer ID is selected the query works fine, if more than one is selected no data is returned.

I need the data to be returned to both the query (so that it can be exported) and to the subform so that the user can review the data before export.

*Notice how I limited the criteria in the query for each combo box, again all of them work until multiple Buyer IDs are selected.

**FYI the date range is between July 1 2002 - August 9 2002

Please, can anyone help???

 

[Drevlin]

The problem is that you're using:
"B15790 or B16210"
as your filter criteria.

Which is looking for an instance that matches the whole string.

What your criteria needs to be is:

"B15790" or "B16210"

I'll see if I can figure out how to get it to work in your database.

 

[jfgambit]

Drevlin:

I figured that was the problem, but I am not sure how to break-out the individual Buyer numbers into a "XXXXXX" or "XXXXXX" format. If you can figure it out I will be eternally grateful!

 

[jfgambit]

OK....figured it out for anyone who is interested:

Using VB Code create a query with an IN statement containing that information form the list boxes. Attach that query to the main query and TA DA....

Here is the additional VB that needs to be added a command button:

Private Sub Command1_Click()

Dim MyDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim i As Integer, strSQL As String
Dim strWhere As String, strIN As String

Set MyDB = CurrentDb()

strSQL = "SELECT * from INFORMATIONTABLE"

If ListboxName.ListCount = 0 Then
strWhere = " WHERE [FIELD] Like '*'"
Else
For i = 0 To ListboxName.ListCount - 1
If ListboxName.Selected(i) Then
strIN = strIN & "'" & ListboxName.Column(0, i) & "',"
End If
Next i

strWhere = " WHERE [FIELD] IN (" & Left(strIN, Len(strIN) - 1) & ")"
End If

MyDB.QueryDefs.Delete "NameofQuery"
Set qdf = MyDB.CreateQueryDef("NameofQuery", strSQL & strWhere)

[SubformNAME].Requery

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click
End Sub