How do you reference a list box's values in a query?

[shanl]

I have moved a list of employees into a List Box that I would like to create a report on. The list box contains three fields. zLName, zFName & zBadgeid. I don't know how to code my query so that it will reference the list box and be able to point it to the correct field. I've tried List2(Column(0) and List2.Column1 but these have not worked. Also do I need to develop a looping routine to go through all the people in my list box so that the query won't just select the first person it finds in the list?
Shan

 

[CautionMP]

shanl,
[tt]= List2[/tt] will return the value in the bound column of the List Box (if a selection has been made). If [tt]zLName[/tt] is the bound column you should get the text from that field.

If nothing is selected you can avoid the error with [tt]= Nz(List2, "Blank")[/tt]

If you want a compound value you could add a column with the source [tt]zLName & ", " & zFName & "(" & zBadgeid & ")"[/tt], make the width 0", and make it the bound column. [tt]= List2[/tt] should then return something like [tt]Guy, Some (1234)[/tt].

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT+08:00) Singapore

 

[shanl]

CMP,
Thanks for getting me on the right track. Your mention of the Bound column showed that I had been missing something and when I looked at the Properties on my ListBox I saw that I had not identified which of my 3 columns needed to be the Bound Column so that helped my query to work.

The other thing I learned from this is that I couldn't put my criteria in the grid box of my Query. Once I took it out from there and put in my VBA code and picked up the right bound column, it worked fine.

I wasn't sure how to get your reference of concatenating all three columns together in the BOUND column property but if I find using one bound column isn't enough I may come back. Here's the solution that I finally got to work. It is looping through the List box values and constructing the SQL query logic using Badge numbers as the critieria that I need for my report to use.

Private Sub Payroll_Report_Click()
On Error GoTo Err_Payroll_Report_Click

Dim stDocName As String
stDocName = "PayrollReport_rpt"

Dim n As Integer
n = 0

Dim ListCounter As Integer
ListCounter = Me![List2].ListCount

Dim strTestField As String
If ListCounter > 0 Then strTestField = "[zBadgeID] ="

Dim stLinkCriteria As String
stLinkCriteria = ""
If Len(stLinkCriteria) <> 0 Then
stLinkCriteria = stLinkCriteria & "And" & "("
Else
stLinkCriteria = "("
End If
Do Until n = ListCounter

If n + 1 = ListCounter Then
stLinkCriteria = stLinkCriteria & strTestField & "'" & Me![List2].ItemData & "'"
Else
stLinkCriteria = stLinkCriteria & strTestField & "'" & Me![List2].ItemData & "'" & "Or"
End If

n = n + 1
Loop

stLinkCriteria = stLinkCriteria & ")" & "AND" & "(" & "[Date_] >" & "#" & Me![StartDate1] & "#" & "AND" & "[Date_] <" & "#" & Me![Enddate1] & "#" & ")"

Debug.Print stLinkCriteria
Debug.Print ListCounter

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria


Exit_Payroll_Report_Click:
Exit Sub


Err_Payroll_Report_Click:
MsgBox Err.Description
Resume Exit_Payroll_Report_Click

End Sub