Running Total Query SQL Syntax Error

[u473]

I am scratching my head with the required quotes and parentheses.
I started with an existing working Query with Running Total
by date.

Now I need to produce a running total by quarter day, so I converted my
dates in serial increments of 6 hours intervals.
I verified that these serial dates have the expected values.

I run into repeated syntax error in the adapted Query.

'Original working Query taken as sample, where dates are in Date type.

SELECT master2.WBS AS WBSAlias, master2.[Pricing Date] AS DateAlias,
Sum(master2.amo unt) AS [Weekly Amount],

Val(DSum("[amount]","master2" ,"[WBS]= '" & [WBSAlias] & "' And
[Pricing Date]<= #" & [dateAlias] & "#")) AS [Running Sum]

FROM master2
GROUP BY master2.WBS, master2.[Pricing Date]
ORDER BY master2.WBS, master2.[Pricing Date];


'New Query adapted from previous. Now dates are Serial values.
'Syntax error in string in Query expression. Probable cause : Quotes &
Parentheses around GDate ?

SELECT Sc8T.HTRPhase AS WBSAlias, Sc8T.GDate AS DateAlias,
Sum(Sc8T.QAC) AS Amount,


Val(DSum("[QAC]","Sc8T","[HTRPhase]= '" & [WBSAlias] & "' And [GDate]<=
[dateAlias])) AS [Running Sum]


FROM Sc8T
GROUP BY Sc8T.HTRPhase, Sc8T.GDate
ORDER BY Sc8T.HTRPhase, Sc8T.GDate;

Help would be very much appreciated,
Thank you.
J.P.

[Larry Linson]

Re: Running Total Query SQL Syntax Error

Are you surrounding the value of WSBAlias with single quotes because it is a
text Field? What type of field is DateAlias? Is it absolutely necessary to
keep the Running Sum in the Query? If not, it is dead easy to specify
Running Sum on a Control in a Report when you are presenting the data.

Larry Linson
Microsoft Access MVP


"u473" <u473@aol.com > wrote in message
news:1120310522 .277740.168460@ g44g2000cwa.goo glegroups.com.. .[color=blue]
>I am scratching my head with the required quotes and parentheses.
> I started with an existing working Query with Running Total
> by date.
>
> Now I need to produce a running total by quarter day, so I converted my
> dates in serial increments of 6 hours intervals.
> I verified that these serial dates have the expected values.
>
> I run into repeated syntax error in the adapted Query.
>
> 'Original working Query taken as sample, where dates are in Date type.
>
> SELECT master2.WBS AS WBSAlias, master2.[Pricing Date] AS DateAlias,
> Sum(master2.amo unt) AS [Weekly Amount],
>
> Val(DSum("[amount]","master2" ,"[WBS]= '" & [WBSAlias] & "' And
> [Pricing Date]<= #" & [dateAlias] & "#")) AS [Running Sum]
>
> FROM master2
> GROUP BY master2.WBS, master2.[Pricing Date]
> ORDER BY master2.WBS, master2.[Pricing Date];
>
>
> 'New Query adapted from previous. Now dates are Serial values.
> 'Syntax error in string in Query expression. Probable cause : Quotes &
> Parentheses around GDate ?
>
> SELECT Sc8T.HTRPhase AS WBSAlias, Sc8T.GDate AS DateAlias,
> Sum(Sc8T.QAC) AS Amount,
>
>
> Val(DSum("[QAC]","Sc8T","[HTRPhase]= '" & [WBSAlias] & "' And [GDate]<=
> [dateAlias])) AS [Running Sum]
>
>
> FROM Sc8T
> GROUP BY Sc8T.HTRPhase, Sc8T.GDate
> ORDER BY Sc8T.HTRPhase, Sc8T.GDate;
>
> Help would be very much appreciated,
> Thank you.
> J.P.
>[/color]