Phucon,
Try:
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;"
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 22 Sep 2014 11:57:21 -0700
Subject: [MS_AccessPros] How do I embed a Date Functions into a SQL string in VBA?
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;"
Try:
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;"
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 22 Sep 2014 11:57:21 -0700
Subject: [MS_AccessPros] How do I embed a Date Functions into a SQL string in VBA?
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: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar