Listview with Currency Column

[rodski]

After hours of searching on my own for a solution, I have given up & hope that someone can help. I'm just an average VBA person and new to generating the ActiveX Listview and Treeview, but have so far managed to create a form with a Treeview list and an Listview where I can drag items from the Listview object and drop them on an item in the Treeview object. Everything works well EXCEPT that I would like my 3rd column in the Listview to display currency rather than a string value which is left justified.

I have tried inserting several format lines but to no avail. It would be greatly appreciated if someone could guide me on this! Here is the code for the Listview:

==========
Private Sub tvTreeView_NodeClick(ByVal Node As Object)

Dim strSQL As String
Dim nodSelected As Node
Dim tv As TreeView
Dim lv As ListView
Dim rDate As Date
Dim lvItem As MSComctlLib.ListItem

'Ensure that the clicked node equals the selected node in the tree
Set tv = Me.tvTreeView.Object
Set lv = Me.lvOrders.Object
Set nodSelected = Node
Set tv.SelectedItem = nodSelected
nodSelected.Selected = True

'SetupListview
With Me.lvOrders
.GridlineStyleBottom = True
.FullRowSelect = True
.Font.Name = "Verdana"
.Font.Size = 8
End With

'Update the listview box to show the items
strSQL = "SELECT BuildKey(ID) AS ItemKey," & _
" Vendor," & _
" eDate," & _
" Amount" & _
" FROM tblScansR" & _
" WHERE BuildKey(Nz(ItemID, '<Null>')) = '" & tv.SelectedItem.Key & "'" & _
" ORDER BY eDate ASC"

'Build the list view
lv.ListItems.Clear
With CurrentProject.Connection
AddLVItems lv, .Execute(strSQL, , adCmdText), , , "2880,2160"
End With

End Sub

 

[jdraw]

What is the data type of Amount in tblScansR?
Can you change it to currency?
Another guess is to try CCur(Amount) in the SQL.

As I said guess and try.
Not something I'm familiar with.
Good luck.

 

[Cronk]

Try
format([Amount],'$#,##0.00')

 

[Galaxiom]

Try
format([Amount],'$#,##0.00')

Format always returns a string.

 

[Cronk]

Yes, it does, and for viewing purposes in a list box, it doesn't matter.

 

[Pat Hartman]

Decide how wide the string needs to be and then pad it with spaces. Let's use 10 for an example.

Select fld1, Space(10- Len(fld2)) & fld2
From yourtable
Order by fld1;

 

[rodski]

Sorry for the delay in responding! The data type of "Amount" in tblScansR is set to currency and I haven't been able to successfully place the "format([Amount],'$#,##0.00') " where it will work without an error. I think that it needs to somehow be placed in this part of the code:

With CurrentProject.Connection
AddLVItems lv, .Execute(strSQL, , adCmdText), , , "2880,2160"
End With

because this is where the strSQL is executed and is populating the list. On another form I have been able to successfully build a ListView object with the correct formatting (thanks to "tek-tips") but I need my ListView to be part of the "drag and drop" so that I can move items between the ListView and TreeView.

Thanks in advance for your suggestions!

 

[rodski]

Thanks Everyone! I did manage to figure it out with by adding a line of code to format the list item when populating the records.

lstItem.SubItems(2) = Nz(Format(rs!Amount, "$#,##,0.00#"), "")

Which is actually the 3rd column of the ListView.

 

[pbaldy]

Post 7 was moderated, I'm posting to trigger email notifications.

 

[Pat Hartman]

Unless you like writing code, there is no reason to populate the listbox using code. Simply bind it to a query that formats the currency as a string.