Reporting last month's results

[Guest]

I have several reports that report data for last month or quarter. They all
function fine until you get to January and then they all ways come back with
no data for that time period, even though there is data. Currently i have a
query set up for each of the reports and i am using the following:

Year(Now()) Month (Now())-1

for my query

How do i get this to work so that it knows to pull data from December 2005
when it is currently January 2006?

 

[Rick Brandt]

I have several reports that report data for last month or quarter.
They all function fine until you get to January and then they all
ways come back with no data for that time period, even though there
is data. Currently i have a query set up for each of the reports and
i am using the following:

Year(Now()) Month (Now())-1

for my query

How do i get this to work so that it knows to pull data from December
2005 when it is currently January 2006?

Where DateFieldName >=DateSerial(Year(Date()), Month(Date())-1,1)
AND DateFieldName < DateSerial(Year(Date()), Month(Date()), 1)

 

[Guest]

Where DateFieldName >=DateSerial(Year(Date()), Month(Date())-1,1)
AND DateFieldName < DateSerial(Year(Date()), Month(Date()), 1)

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


I tried this but now it gives me miscellaneous data that makes no sense.

What i did was go into my query and added a total to the two columns that
with the criteria for where and the statements that you gave me, of course
replacing my field name in the appropriate place.

 

[Rick Brandt]

I tried this but now it gives me miscellaneous data that makes no sense.
What i did was go into my query and added a total to the two columns
that with the criteria for where and the statements that you gave me,
of course replacing my field name in the appropriate place.

Post your entire SQL.

 

[Guest]

Field: Lost | "Where [projDate]" | "And [projDate]"
Table: Current Project List
Total: Where |Where | Where
Sort:
Show:
Criteria: Yes | >=DateSerial(Year(Date()),Month(Date())-1,1) |
<DateSerial(Year(Date()), Month(Date()),1)

 

[Rick Brandt]

Field: Lost | "Where [projDate]" | "And [projDate]"
Table: Current Project List
Total: Where |Where | Where
Sort:
Show:
Criteria: Yes | >=DateSerial(Year(Date()),Month(Date())-1,1) |
<DateSerial(Year(Date()), Month(Date()),1)

Post your entire SQL.

Sorry, but I can't makes sense of this. You need to switch to SQL view of
the query and post that content here. From wjhat I can see though you have
the words "Where"and "And" in your field row? That is definitely wrong.
That row should have either field names only, field names with aliases in
front of them, or aliases followed by (valid) expressions.

The word "Where" will only be visible in SQL View of a query that has
criteria. It is implied but never seen in the designer grid.

 

[Guest]

Thank you that worked. I had never switched to SQL view before that makes it
much easier to write the code. The report is now working.

Field: Lost | "Where [projDate]" | "And [projDate]"
Table: Current Project List
Total: Where |Where | Where
Sort:
Show:
Criteria: Yes | >=DateSerial(Year(Date()),Month(Date())-1,1) |
<DateSerial(Year(Date()), Month(Date()),1)

Post your entire SQL.

Sorry, but I can't makes sense of this. You need to switch to SQL view of
the query and post that content here. From wjhat I can see though you have
the words "Where"and "And" in your field row? That is definitely wrong.
That row should have either field names only, field names with aliases in
front of them, or aliases followed by (valid) expressions.

The word "Where" will only be visible in SQL View of a query that has
criteria. It is implied but never seen in the designer grid.