multiple records listbox

[powerpro]

Can anyone help me to get my listbox to function properly. It's on a form to choose which records that I want a report on.

Basically, I know the problem is the code is looking for a number in the RecID field, and that's what's giving me the data mismatch error, but I need the code to look for RecID as a text field.

Any help is appreciated, here's my code:

Option Compare Database

Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In ListFilter.ItemsSelected
stDocCriteria = stDocCriteria & "[CarID] = " & ListFilter.Column(0, VarItm) & " OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function
Private Sub ButtonOpen_Click()
DoCmd.OpenReport "rpt_CarSales", acPreview, , GetCriteria()
End Sub

 

[powerpro]

Sorry, make that:

Basically, I know the problem is the code is looking for a number in the CarID field, and that's what's giving me the data mismatch error, but I need the code to look for CarID as a text field.

I'm using an example code to do what I want for my real database

 

[payback]

You need to put single quotes around a text field whereas numeric columns do not require any quotes.

 

[FancyPrairie]

If you're looking for text then your code should look like this:
stDocCriteria = stDocCriteria & "[CarID] = '" & ListFilter.Column(0, VarItm) & "' OR "

However, if you plan on building other databases or you plan on adding additional fields on your form from which the user can select critiera, I would suggest you begin building a library database (simply create a new database and reference it). Then take a look at the function in the FAQ faq181-5497 and add that to your library.

The funcion in the FAQ will do what you want, plus more. It will handle 0 to many list boxes, text boxes, and date ranges (and you can add to it as the need arises). You only have to do three things to make the function work:
1. Open a new module (in the library db or the current one, doesn't matter) and copy the function from the FAQ and paste it into your new module.
2. Setup the tag properties as indicated in the FAQ. In your case something like Where=tblCarSales.CarID,String;
3. Open your report like this:
DoCmd.OpenReport "rpt_CarSales", acPreview, , BuildWhereClause(Me)

That's all it takes to make it work.

 

[powerpro]

payback and FancyPrairie, thank you for your responses.

You have saved me from hours of frustration. It worked like a charm.

I've been viewing this forum for over a year and that was my first post. I've gotten a lot of info here.