I have a query, the query runs fine and produces correct output. I then use the query to built a SQL string as record source for a report, but an error "2465, Desciption: DBtest Databse can't find the field'|1' referred to in your expression." occured.
How do I embed aDate Function into a SQL string use VBA?
Below is my query and the SQL string in the report module.
Thanks
Phucon
'*** The query
SELECT B.BkName AS Field1,
DateAdd("m",-1,[c].[Balance As Of Date]) AS Field2,
DateAdd("ww",-12,[c].[Balance As Of Date]) AS Field3,
DateAdd("yyyy",-1,[c].[Balance As Of Date]) AS Field4
FROM qryMostCurrentData AS C INNER JOIN attributes AS B ON C.[ID] = B.[ID]
WHERE (((B.dtend)=99991231))
ORDER BY B.BkName;
---------------------------------------------------------------------------------------------------------------------
'*** build SQL
Dim strRS As String
strRS = "SELECT B.BkName AS Field1, " & _
DateAdd("m", -1, [c].[Balance As Of Date]) & " AS Field2, " & _
DateAdd("ww", -12, [c].[Balance As Of Date]) & " AS Field3, " & _
DateAdd("yyyy", -1, [c].[Balance As Of Date]) & " AS Field4 " & _
"FROM qryMostCurrentData AS C INNER JOIN attributes AS B ON C.[ID] = B.[ID] " & _
"WHERE (((B.dtend) = 99991231)) ORDER BY B.B.BkName;"
Posted by: saigonf7q5@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar