Using Function for field headings. 1

[errolf]

I am using a function Modified() that calculates when a file was last modified. I want to use this function to create a heading in a query eg. [Stock_On_hand] As SOH & Modified.
I am using the following code in a Command Button but cannot get it to work.
Private Sub CmdMthly_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim DebtSql As String
Dim intMonths As Integer, i As Integer
Dim datStart As Date, datEnd As Date
Dim strQSL As String, strDatesQSL As String, strPivotQSL As String
Dim NoDebtSql As String
Dim MySql As String
Dim strSQL As String
Dim DateMod As String
datStart = Me!BeginningDate
datEnd = Me!EndingDate
intMonths = DateDiff("m", datStart, datEnd)
While i <= intMonths
strDatesQSL = Format(DateAdd("m", i, datStart), "mmm-yy")
If i < intMonths Then
strPivotQSL = strPivotQSL & "'" & strDatesQSL & "',"
Else
strPivotQSL = strPivotQSL & "'" & strDatesQSL & "'"
End If
i = i + 1
Wend
DateMod = Format(Modified(), "dd-mm-yy")
DateMod = DateMod
NoDebtSql = "TRANSFORM Sum([SCEX].[QTY]) AS UnitSales " & _
" SELECT STEX.DESC,Sum(SCEX.QTY) AS [Tot_ Units],STEX.ON_HAND as ['SOH'& DateMod] " & _
"FROM (SCEX INNER JOIN DREX ON [SCEX].[AC_NO]=[DREX].[NUMBER])" & _
"INNER JOIN STEX ON [SCEX].[STOCK]=[STEX].

" & _
"WHERE (((RetGrp([SCEX].[CLASS])) Like [Forms]![StatsSboard]![CmbComRet])" & _
"And (([SCEX].[CLASS]) Like [Forms]![StatsSboard]![Classif]) And (([STEX].[SUPP_2])" & _
"Like [Forms]![StatsSboard]![Combo2]) And (([SCEX].[INV_DATE])" & _
"Between [Forms]![StatsSboard]![BeginningDate] And [Forms]![StatsSboard]![EndingDate])" & _
"And (([SCEX].[AC_NO]) Like [Forms]![StatsSboard]![CmbProd]))" & _
"GROUP BY [Forms]![StatsSboard]![Classif], [STEX].[DESC],[STEX].[ON_HAND]" & _
strSQL & _
"PIVOT Format(SCEX.INV_DATE,'mmm-yy') " & _
"In " & _
"(" & strPivotQSL & ");"
MySql = MySql
 curSql = NoDebtSql
db.QueryDefs("MthlyQtyCTabQry").Sql = curSql
DoCmd.Close acQuery, "MthlyQtyCTabQry", acSaveNo
DoCmd.OpenQuery "MthlyQtyCTabQry", acViewNormal
End Sub

Hoping for some guidance

Errol

 

[RoyVidar]

Do you need the single quotes? They are probably one part of the challenge (remove). Also the concatenation operator (&), can perhaps be a challenge, try with only one first, and if that doesn't work, try double them up:

[tt]...ON_HAND as [SOH & DateMod] " & _[/tt]
[tt]...ON_HAND as [SOH && DateMod] " & _[/tt]

Roy-Vidar

 

[RoyVidar]

Oh see now it seems you're going to add a variable to the string, but not the "SOH" text

[tt]...ON_HAND as [SOH & " & DateMod & "] " & _[/tt]

Roy-Vidar