Date Format Question 1

[Delindan]

I have a query that I got the syntax to work but the counts are not coming up as I want. I suspect it's because of the format of the user input [txtStart]. The field End Date is defined as a short date in my table. Here is the query:

SELECT [Job Activity].[Act Cost Center],
Count(([Job Activity]![End Date] Between [txtStart] And DateAdd("m",1,[txtStart]))) AS M1,
Count(([Job Activity]![End Date] Between DateAdd("m",1,[txtStart]) And DateAdd("m",2,[txtStart]))) AS M2,
Count(([Job Activity]![End Date] Between DateAdd("m",2,[txtStart]) And DateAdd("m",3,[txtStart]))) AS M3,
Count(([Job Activity]![End Date] Between DateAdd("m",3,[txtStart]) And DateAdd("m",4,[txtStart]))) AS M4,
Count(([Job Activity]![End Date] Between DateAdd("m",4,[txtStart]) And DateAdd("m",5,[txtStart]))) AS M5,
Count(([Job Activity]![End Date] Between DateAdd("m",5,[txtStart]) And DateAdd("m",6,[txtStart]))) AS M6,
Count(([Job Activity]![End Date] Between DateAdd("m",6,[txtStart]) And DateAdd("m",7,[txtStart]))) AS M7,
Count(([Job Activity]![End Date] Between DateAdd("m",7,[txtStart]) And DateAdd("m",8,[txtStart]))) AS M8,
Count(([Job Activity]![End Date] Between DateAdd("m",8,[txtStart]) And DateAdd("m",9,[txtStart]))) AS M9,
Count(([Job Activity]![End Date] Between DateAdd("m",9,[txtStart]) And DateAdd("m",10,[txtStart]))) AS M10,
Count(([Job Activity]![End Date] Between DateAdd("m",10,[txtStart]) And DateAdd("m",11,[txtStart]))) AS M11,
Count(([Job Activity]![End Date] Between DateAdd("m",11,[txtStart]) And DateAdd("m",12,[txtStart]))) AS M12
FROM [Job Activity]
GROUP BY [Job Activity].[Act Cost Center]
ORDER BY [Job Activity].[Act Cost Center];

Any suggestions would be greatly appreciated.

 

[PHV]

I got the syntax to work
Really ?
not coming up as I want
I'd use the IIf function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[genomon]

Datatype mismatch for the DateAdd function?
Have you tried CDate([txtStart]), or enclosing the dates with the # character?

Gluais faicilleach le cupan làn

 

[dhookom]

The issue is that you are counting which counts both true and false values.

I would either use a crosstab with "M" & DateDiff("m",txtStart, [End Date]) as the Column Heading or

SELECT [Job Activity].[Act Cost Center], 
Abs(Sum([Job Activity]![End Date] Between [txtStart] And DateAdd("m",1,[txtStart]))) AS M1, 
Abs(Sum([Job Activity]![End Date] Between DateAdd("m",1,[txtStart]) And DateAdd("m",2,[txtStart]))) AS M2,
--- etc ---
FROM [Job Activity]
GROUP BY [Job Activity].[Act Cost Center]
ORDER BY [Job Activity].[Act Cost Center];

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

thanks. this worked perfectly. I just figured out what you meant by counting both true and false occurances. I'm still trying to understand how what you have works. I read up on the abs() function and don't understand how that combined with the sum() works. If you have time...

 

[PHV]

([Job Activity]![End Date] Between [txtStart] And DateAdd("m",1,[txtStart]) returns a boolean value, ie 0 for false and -1 for true and thus the absolute value of the sum is the count of the true values.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Delindan]

Ohhhhhhhhh! Thanks!

 

[dhookom]

Thanks for the assist PH

Duane
Hook'D on Access
MS Access MVP