Hi all. I've been working in Access for a few years and am somewhat competent in writing complicated queries and working with VBA. I currently have a table of Items and two date fields "CommittedDate" and "CompletedDate" (both fields are Short Date in the table that defines them).
Since the dates can be any day of the week, I need to "normalize" them to the next Friday, and i use the following syntax to do that: WeekEnding = CDate(DateAdd("d",7-DatePart("w",[CommittedDate],7),[CommittedDate])). This function finds the next friday for whichever date is CommittedDate, and I added the CDate to ensure that the format is a date data type.
When I use the CommittedDate dates, everything works and my subsequent query that uses DSum executes successfully: IIf([Query1].[weekending] Is Not Null,DSum("IDCount","Query1","WeekEnding <= #" & [WeekEnding] & "#")). This result gets me a running total of all of the individual week's data, for those weeks that actually have data.
Now when I do the *same* queries and code using the "CompletedDate" dates, the DSum spits back an error. I successfully have my Query1 to find the next Friday for the CompletedDate and show an individual week count that looks like this:
When I try to run the DSum on this data, I get a "data type mismatch error." I even tried running a "manual" DSum: dsum("countofid","Query2","weekending < #1/1/2011#") with my own manual date, but Access is still having a problem with the "weekending" date (the cdate(dateadd(datepart))) function).
I have tried adding NZ(), isnull(), etc to this dsum without any good results. Anything I do shows me this:
WeekEnding CountOfID Sum
3/26/2010 1 #Errror
9/24/2010 1 #Errror
10/22/2010 1 #Errror
11/5/2010 1 #Errror
12/3/2010 3 #Errror
I know this is complicated and i'm probably not explaining it perfectly, but is there anyone that thinks they can help me out here? Please let me know if you want more specific information or anything like that.