Viewing records for a certain date range.

[Guest]

I am trying to view records from 9/1/04 to 11/30/04 and for some reason when
I do this nothing comes up.

I currently have it set up to view the records from 7/1/04 to 9/30/04. The
SQL looks like this:

SELECT DISTINCTROW First(Format$([Vital Signs - Correspondence].[Date of
Activity],'m/yyyy')) AS [Month], Sum([Vital Signs - Correspondence].Quantity)
AS [Total Correspondence Processed]
FROM [Vital Signs - Correspondence]
GROUP BY Year([Vital Signs - Correspondence].[Date of
Activity])*12+DatePart('m',[Vital Signs - Correspondence].[Date of
Activity])-1
HAVING (((First(Format$([Vital Signs - Correspondence].[Date of
Activity],'m/yyyy')))>=#7/1/2004# And (First(Format$([Vital Signs -
Correspondence].[Date of Activity],'m/yyyy')))<=#9/30/2004#));

I think it has something to do with the fact that October and November have
two digits for the month (11/30/04)instead of just one (9/30/04).

If anyone has any suggestions I would greatly appreciate it.

Thanks.

 

[Edward G]

Mayra,
Sorry to answer a question with a question but what is the significance of
this expression? What was it intended to do?
..
GROUP BY Year([Vital Signs - Correspondence].[Date of
Activity])*12+DatePart('m',[Vital Signs - Correspondence].[Date of
Activity])-1

Ed

 

[John Vinson]

I am trying to view records from 9/1/04 to 11/30/04 and for some reason when
I do this nothing comes up.

I currently have it set up to view the records from 7/1/04 to 9/30/04. The
SQL looks like this:

SELECT DISTINCTROW First(Format$([Vital Signs - Correspondence].[Date of
Activity],'m/yyyy')) AS [Month], Sum([Vital Signs - Correspondence].Quantity)
AS [Total Correspondence Processed]
FROM [Vital Signs - Correspondence]
GROUP BY Year([Vital Signs - Correspondence].[Date of
Activity])*12+DatePart('m',[Vital Signs - Correspondence].[Date of
Activity])-1
HAVING (((First(Format$([Vital Signs - Correspondence].[Date of
Activity],'m/yyyy')))>=#7/1/2004# And (First(Format$([Vital Signs -
Correspondence].[Date of Activity],'m/yyyy')))<=#9/30/2004#));

I think it has something to do with the fact that October and November have
two digits for the month (11/30/04)instead of just one (9/30/04).

Several problems here! For one thing, the Format$() function returns a
Text String, and as a text string, "11/30/04" sorts before "9/30/04",
since 1 comes before 9. Secondly, the HAVING clause works *after* the
totalling has all been done; you want a WHERE clause instead. To get
it, put [Date Of Activity] into a vacant Field cell, change the Totals
operator to Where, and put

BETWEEN [Enter start date:] AND [Enter end date:]

on the Criteria line under the field.

Note that the First operator may not do what you expect; it's pretty
useless actually! It returns the first record in *disk storage order*
- an order which you cannot control, so it returns an arbitrary
record. I'm not sure what it is that you want to see here though.

John W. Vinson[MVP]