Reporting last month's results

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
axelcore said:
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?
Click to expand...

Where DateFieldName >=DateSerial(Year(Date()), Month(Date())-1,1)
AND DateFieldName < DateSerial(Year(Date()), Month(Date()), 1)
 
Rick Brandt said:
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.
Click to expand...
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.
 
axelcore said:
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.
Click to expand...

Post your entire SQL.
 
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)
 
axelcore said:
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 said:
Post your entire SQL.
Click to expand...
Click to expand...

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.
 
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.

Rick Brandt said:
axelcore said:
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 said:
Post your entire SQL.
Click to expand...
Click to expand...

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.
Click to expand...
 
Back
Top