How to change a value returned in a listbox

[Alex.Spodkiewicz]

Hi all,

I have created a search screen which returns records with values that
I have specified. I am displaying the results in a listbox which is
fine. However I am wanting to change how the returned values are
displayed. For example I have a column with values of 1 and 5 which I
want to display as 1= Active and 5 = Closed. Is there a way to do this
in the code.

Thanks

Alex

 

[Scott McDaniel]

Hi all,

I have created a search screen which returns records with values that
I have specified. I am displaying the results in a listbox which is
fine. However I am wanting to change how the returned values are
displayed. For example I have a column with values of 1 and 5 which I
want to display as 1= Active and 5 = Closed. Is there a way to do this
in the code.

How are you filling the listbox? If it's through a query, you can use an IIF statement to do this. In the query design
view, build a new column with the Name like this:

Status: IIF(YourColumn=1, "Active", "Closed")

This would return "Active" if the value of YourColumn is 1, otherwise it would return Closed. If you need more choices
than this, you'd have to either used nested IIF statements, or build a table to hold your choices, then add that
table/query to the listbox's underlying recordsource.

If you're using a Value List for the .Rowsource, then just use an If statement when building it:

Thanks

Alex

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Alex.Spodkiewicz]

How are you filling the listbox? If it's through a query, you can use an IIF statement to do this. In the query design
view, build a new column with the Name like this:

Status: IIF(YourColumn=1, "Active", "Closed")

This would return "Active" if the value of YourColumn is 1, otherwise it would return Closed. If you need more choices
than this, you'd have to either used nested IIF statements, or build a table to hold your choices, then add that
table/query to the listbox's underlying recordsource.

If you're using a Value List for the .Rowsource, then just use an If statement when building it:





Scott McDaniel
scott@takemeout_infotrakker.comwww.infotrakker.com

The code that I am using looks like this so far:

Public Sub btnrefsearch_Click()
Dim strSQL As String
Dim strWhere As String
Dim FCount As Integer

strWhere = ""
FCount = 0

If Eval("[forms]![frm_clients]![fldclientno] Is Null") Or
Eval("[forms]![frm_clients]![fldclientno] = ''") Then
strWhere = strWhere + ""
Else: strWhere = "tbl_referrals.CLIENT_NO = " & fldclientno
FCount = 1
End If

If Left(strWhere, 4) = " AND" Then strWhere = Right(strWhere,
Len(strWhere) - 4)

If Len(strWhere) > 0 Then strWhere = " WHERE " & strWhere

strSQL = "SELECT tbl_referrals.[client_no], tbl_Referrals.[ref_no],
tbl_Referrals.[status] as Status, tbl_Referrals.[action_date],
tbl_Referrals.[allocated_role], tbl_Referrals.[allocated_s_p_no],
tbl_Referrals.[Start_Date], tbl_Referrals.[End_Date]" & _
"FROM tbl_Referrals" & strWhere

If [tbl_Referrals].[status] = 1 Then
lstreferrals.Column(2).Value = "Active"
End If

If [tbl_referrals].[status] = 5 Then
lstreferrals.column(2).Value = "Closed"
End If

lstreferrals.RowSource = strSQL
lstreferrals.Requery


End Sub

So would your idea work in this instance?

Thanks

Alex