dsum help - running sum N days before and after

[broecher]

My expression outputs nothing (blank), and it is very slow... Can someone tell me what I did wrong?

I am looking for the sum of the values in the column DayFixCount, but only if the short date in the column all_dates is within 3 days. The name of the query i am in is qry_9fixTotals.

expr1: DSum("DayFixCount","qry_9fixTotals","[all_dates] >=" & [all_dates]-3 & "And [all_dates] <=" & [all_dates]+3)

I can't seem to get any simpler DSum expressions to work either - if they contain the & symbol. Maybe this is not the correct method for my version of Access?

I'm using access 2002.

Thanks

[June7]

Need # delimiter for date criteria, text uses apostrophe, numbers nothing. Also needed a space in front of And so it doesn't run into date value. Use BETWEEN AND with inclusive range.

DSum("DayFixCount","qry_9fixTotals","[all_dates] BETWEEN #" & [all_dates]-3 & "# AND #" & [all_dates]+3 & "#")

Does all_dates include time?

[broecher]

Thanks June7! So I can use BETWEEN in the "design view" or does this have to be entered in SQL view?

Need # delimiter for date criteria, text uses apostrophe, numbers nothing.

I don't understand what you mean about apostrophes and numbers.

all_dates is just a short date like 09/20/2010.

[June7]

Either.

A date value is actually stored as a number but because of the datatype, Access displays the value in a date/time structure and will deal with the value like a date/time. Regardless of the display format setting, the value still has date and time components. If you don't save time (like 3:45:27 PM), Access just doesn't show time parts, which would be 00:00:00. It is also possible to save only the time part and the date won't show.

The WHERE CONDITION of domain aggregate function is like the WHERE clause of an sql statement. Criteria must be delimited. If the field is a date/time type, use # characters. They force Access to treat the value as date/time entity, and not just a string of characters. For text fields, apostrophe (or two quote marks) must be used and for number fields no delimiters.

[broecher]

it worked thanks so much!