Add one field to a script

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

I am trying to add this field to this script, it is a text field

tblInvoice_ItMdt.GSTOptionsText,
In any order preferably last
Thanks In advance.........Bob

lstModify.RowSource = "SELECT Format(MIN(dtDate),'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.2 in;0;0;5 in;1.5 in"
lstModify.BoundColumn = 2
 
Hi Bob,

What is your question? I see a statement of what you are trying to do....
Do you get an error? If so, what is the error message?

It looks like the new field you are trying to add comes from a different
table, which means that you're going to have to include a JOIN clause as
well. Try creating the same query in normal design view for a new query, and
then flip to SQL View to look at the resulting SQL statement. Then it is just
a matter of creating the same SQL statement in code.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Tom, It is the same table that the other fields are in "tblInvoice_ItMdt"
Thanks Bob
 
Thanks Arvin worked it out :)
lstModify.RowSource = "SELECT Format(MIN(dtDate),'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)," _
& "Last(tblInvoice_ItMdt.GSTOptionsText)" _
& " FROM tblInvoice_Itmdt" _
& " GROUP BY tblInvoice_Itmdt.HorseID" _
& " order by funGetHorse(0,tblInvoice_ItMdt.HorseID,true) " _
& ",funGetHorse(0,tblInvoice_ItMdt.HorseID,false);"



lstModify.ColumnCount = 6
lstModify.ColumnWidths = "1.2 in;0;0;5 in;1.5 in;1 in"
 
Back
Top