Multi-column List box (1 Viewer)

SolerPower

New member
Local time
Today, 10:48
Joined
Oct 29, 2013
Messages
6
Hello everyone
So I have this database that when you click a button it generates a report using the information entered by a user. On that report I have a multi-column list box that holds a currency value. The problem that I am having is that the list box is cutting off some of the currency value digits and I have no idea why. Attached is a screen shot of the list box and the code I’m using to populate the list box. the correct values are 5,000 1,500 and 18,000
:banghead:
 

Attachments

  • code.JPG
    code.JPG
    70.5 KB · Views: 78
  • listbox.JPG
    listbox.JPG
    11.8 KB · Views: 72

AlexHedley

Registered User.
Local time
Today, 15:48
Joined
Aug 28, 2012
Messages
171
The "Amount" Field, what Data type is it?
Number, Single, Double, ...?
 

SolerPower

New member
Local time
Today, 10:48
Joined
Oct 29, 2013
Messages
6
Hey Alex

I just found out the solution to my problem. The commas in the currency value creates another column when inserted into a list box, In order to prevent that from happening I had to add single quotes to change the currency value into a string. Updated code below.


sql = "insert into tblReportBuilder(ID, Type, Amount) Values(1,'12b-1 Adjustment','" & Forms![frmconsultingMain]![txt12bAdj] & "')"
db.Execute (sql)
sql = "insert into tblReportBuilder(ID, Type, Amount ) Values (2, 'Commission Adjustment', '" & Forms![frmconsultingMain]![txtCommAdj] & "')"
db.Execute (sql)
sql = "insert into tblReportBuilder(ID, Type, Amount) Values (3, 'Other Adjustment', '" & Forms![frmconsultingMain]![txtOtherAdj] & "')"
db.Execute (sql)
'sql = "insert into tblReportBuilder(ID, Type, Amount) Values (4, 'Total Fee', '" & Forms![frmconsultingMain]![txtFinInvoiceAmt] & "')"
'db.Execute (sql)
sql = "Select * From tblReportBuilder Where (tblReportBuilder.Amount is not null) Order by ID asc"
Set rs = db.OpenRecordset(sql)
While Not rs.EOF
Me.lstReportBuilder.AddItem rs.Fields("Type") & ";" & " " & ";'" & FormatCurrency(rs.Fields("Amount"), 2) & "';"
rs.MoveNext
Wend
End Sub
 

Users who are viewing this thread

Top Bottom