dynamic field name

[jasn_78]

ok i am trying to get a query in vba to change its header some of its fields depending on an integer i have set (p)

valp = "VALUE" & P
qtyp = "QTY" & P

sql = "SELECT (DateDiff('d', [Forms]![frmCOMPARISON]![txtDATEFROM1],(HTRXTBL.HTRX_TRX_DATE))) AS DAY, " & _
        "(Round(Sum(HTRXTBL.HTRX_VALUE),2)) AS valp, " & _
        "Sum(HTRXTBL.HTRX_QTY_1) AS qtyp " & _
        "FROM ITEMTBL INNER JOIN HTRXTBL " & _
        "ON ITEMTBL.ITEM_NUMBER = HTRXTBL.HTRX_ITEM_NUMBER " & _
        "WHERE (HTRXTBL.HTRX_REC_TYPE= 'ITMSALE') " & _
        "AND ((HTRXTBL.HTRX_TRX_DATE) >= [Forms]![frmCOMPARISON]![txtDATEFROM1]) " & _
        "AND ((HTRXTBL.HTRX_TRX_DATE) <= [Forms]![frmCOMPARISON]![txtDATETO1]) " & _
        "GROUP BY HTRXTBL.HTRX_TRX_DATE"

any ideas how i could do this?

Thanks
Jason

 

[pbaldy]

"(Round(Sum(HTRXTBL.HTRX_VALUE),2)) AS " & valp & ", " & _

 

[jasn_78]

thanks mate knew i must have been missing something silly