Running total multiple query

[eka24]

Please I am trying to add another criteria the Ship City to query below but have not been successful;
RunTot: DSum("ShippingFee","Orders","DatePart('m', [OrderDate])<=" & [AMonth] & " And DatePart('yyyy',[OrderDate])<=" & [AYear] & "")
This works perfectly until i added the ship city criteria;

RunTot: DSum("ShippingFee","Orders",“[ship city]=”’&[ship city]&”’ And "DatePart('m', [OrderDate])<=" & [AMonth] & " And DatePart('yyyy',[OrderDate])<=" & [AYear] & "")



I need help, thanks

 

[MarkK]

You could simplify things a litte if you use the VBA Month() and Year() functions, rather than DatePart(), ...

"Month(OrderDate) <= " & AMonth & " AND Year(OrderDate) = " & AYear

...and it looks like you might just have reversed a quote and double quote...

RunTot: DSum("ShippingFee","Orders",“[ship city]=[COLOR="Red"]”’[/COLOR]&[ship city]&”’ And "DatePart('m', [OrderDate])<=" & [AMonth] & " And DatePart('yyyy',[OrderDate])<=" & [AYear] & "")

hth
Mark

 

[eka24]

I get error when i apply your suggestion;
I have attached the file;

thank you

 

[MarkK]

What error do you get? Which suggestion did you apply?
Mark

 

[arnelgp]

try this:

RunTot: DSum("ShippingFee","Orders","[ship city]=" & Chr(34) & [ship city] & Chr(34) & " And Format(OrderDate, 'yyyymm') < " & Format([AYear],"0000") & Format([AMonth],"00"))

 

[eka24]

Anelgp, It working fine except that the first figure for the example Las Vagas is not shown. which also affect the next cummulative figure;
I expect Jan-2006 running total for las vagas to be 205 (instead of 0) and feb-2006 to be 212 (not 205).
SumOfShippingFee AYear AMonth RunTot Period Ship City £205.00 2006 1 Jan-2006 Las Vegas £5.00 2006 1 Jan-2006 New York £50.00 2006 1 Jan-2006 Portland £7.00 2006 2 205 Feb-2006 Las Vegas £7.00 2006 2 Feb-2006 Los Angelas £4.00 2006 2 5 Feb-2006 New York £9.00 2006 3 Mar-2006 Chicago £5.00 2006 3 212 Mar-2006 Las Vegas












Ship City




Las Vegas




New York




Portland




Las Vegas




Los Angelas




New York




Chicago




Las Vegas

 

[arnelgp]

Change the operator to <= instead of <

 

[eka24]

Thanks Arnelgp, exactly what am looking for.