Add one field to a script

[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

 

[Tom Wickerath]

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/
__________________________________________

 

[Bob Vance]

Tom, It is the same table that the other fields are in "tblInvoice_ItMdt"
Thanks Bob

 

[Bob Vance]

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"

 

[Tom Wickerath]

Ah, you're right. Somehow, I thought I had noticed a different table name.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________