Adding a new column to ListBox!

[Bob V]

I am trying to add an extra column to my "Distribute Invoice" List Box
I would like "Distribute Invoice" to show tblInvoice_ItMdt.TotalAmount" like
Holding Invoice does
Thanks for any help with this................Regards Bob

***Holding Invoices****
lstModify.RowSource = "SELECT tblInvoice_ItMdt.IntermediateID," _
& " tblInvoice_ItMdt.HorseID," _
& " funGetHorse(0,tblInvoice_ItMdt.HorseID,false) AS Name," _
& " tblInvoice_ItMdt.TotalAmount" _
& " FROM tblInvoice_ItMdt INNER JOIN tblHorseInfo" _
& " ON tblInvoice_ItMdt.HorseID=tblHorseInfo.HorseID" _
& " order by funGetHorse(0,tblInvoice_ItMdt.HorseID,true) " _
& ",funGetHorse(0,tblInvoice_ItMdt.HorseID,false);"

lstModify.ColumnCount = 4
lstModify.ColumnWidths = "0;0;6 in;1 in"
lstModify.BoundColumn = 1
-----------------------------------------------------------
***Distribute Invoices*****
lstModify.RowSource = "SELECT Format(MIN(dtDate),'dd-mmm-yyyy') AS
StartDate," _
& " Format(MAX(dtDate),'dd-mmm-yyyy') AS EndDate," _
& "
tblInvoice_Itmdt.HorseID,funGetHorse(0,tblInvoice_Itmdt.HorseID,false)" _
& " FROM tblInvoice_Itmdt" _
& " GROUP BY tblInvoice_Itmdt.HorseID" _
& " order by funGetHorse(0,tblInvoice_ItMdt.HorseID,true) " _
& ",funGetHorse(0,tblInvoice_ItMdt.HorseID,false);"



lstModify.ColumnCount = 4
lstModify.ColumnWidths = "1 in;1 in;0;1.5 in"
lstModify.BoundColumn = 2

 

[Douglas J. Steele]

So what's your question? Does the code you're showing not work?

 

[Bob V]

Thanks Douglas , both codes work but I am trying to get the bottom code
"Distribute Invoices" to show the "tblInvoice_ItMdt.TotalAmount"
Like the top code does........................Thanks Bob

 

[Douglas J. Steele]

Without knowing anything about the tables that are involved, wouldn't it
just be a case of adding tblInvoice_ltMdt.TotalAmount to the list of fields
in both the Select and Group By sections of the SQL, and changing the
ColumnCount and ColumnWidths fields?

 

[Bob V]

Thanks Douglas , have tried a few codes like you suggested but just keep
losing all my data when I change it, Any suggestion to a new code?
Thanks Bob

 

[Douglas J. Steele]

I would think

lstModify.RowSource = "SELECT Format(MIN(dtDate),'dd-mmm-yyyy') AS
StartDate," _
& " Format(MAX(dtDate),'dd-mmm-yyyy') AS EndDate," _
& "tblInvoice_Itmdt.HorseID,funGetHorse(0,tblInvoice_Itmdt.HorseID,false),
" _
& "tblInvoice_ItMdt.TotalAmount" _
& " FROM tblInvoice_Itmdt" _
& " GROUP BY tblInvoice_Itmdt.HorseID, tblInvoice_ItMdt.TotalAmount" _
& " order by funGetHorse(0,tblInvoice_ItMdt.HorseID,true) " _
& ",funGetHorse(0,tblInvoice_ItMdt.HorseID,false);"

or

lstModify.RowSource = "SELECT Format(MIN(dtDate),'dd-mmm-yyyy') AS
StartDate," _
& " Format(MAX(dtDate),'dd-mmm-yyyy') AS EndDate," _
& "tblInvoice_Itmdt.HorseID,funGetHorse(0,tblInvoice_Itmdt.HorseID,false),
" _
& "Sum(tblInvoice_ItMdt.TotalAmount)" _
& " FROM tblInvoice_Itmdt" _
& " GROUP BY tblInvoice_Itmdt.HorseID" _
& " order by funGetHorse(0,tblInvoice_ItMdt.HorseID,true) " _
& ",funGetHorse(0,tblInvoice_ItMdt.HorseID,false);"

Either way

lstModify.ColumnCount = 5
lstModify.ColumnWidths = "1 in;1 in;0;1.5 in;1 in"
lstModify.BoundColumn = 2

 

[Bob V]

Thanks Douglas, firstly was getting a error on AsStartDate,"_ so changed it
to AS StartDate , """ _ and debugged ok
But both codes are giving me an error when opening the form
Code 1:Syntax error in query expression tblInvoice_Mtdt.TotalAmount From
tblInvoice.ItMdt Group by tblInvoice.ItMdt,Invoice.ItMdt.TotalAmount order
by
funGetHorse(0,tblInvoice-ItMdt,true),funGetHorse(0,tblInvoice-ItMdt,false)

Code 2: Syntax Error (missing operator) in a query expression
Format(Max(dtDate),'dd-mmm-yy')As End date
tblInvoice_ItMdt.Horse.ID,funGetHorse(0,tblInvoice_ItMtd.HorseID,False),"Sum(tblInvoice_ItMtd.TotalAmount)'

Thanks for any help..........Bob

 

[Bob V]

Thanks Douglas BRILLIANT just had the wrong amount of Arguments at the end
of the line...Thanks for your time....Bob

lstModify.RowSource = "SELECT Format(MIN(dtDate),'dd-mmm-yyyy') AS StartDate
," _
& " Format(MAX(dtDate),'dd-mmm-yyyy') AS EndDate," _
&
"tblInvoice_Itmdt.HorseID,funGetHorse(0,tblInvoice_Itmdt.HorseID,false)," _
& "Sum(tblInvoice_ItMdt.TotalAmount)" _
& " FROM tblInvoice_Itmdt" _
& " GROUP BY tblInvoice_Itmdt.HorseID" _
& " order by funGetHorse(0,tblInvoice_ItMdt.HorseID,true) " _
& ",funGetHorse(0,tblInvoice_ItMdt.HorseID,false);"



lstModify.ColumnCount = 5
lstModify.ColumnWidths = "1 in;0;0;5 in;1.5 in"
lstModify.BoundColumn = 2

End Sub