Report on this and previous month only ?

[pookie62]

Hi,
I'v been reading a lot of posts, but can't find the solution for my question:
How do I create a report that covers this month and the previous month ?
I have a date field [Ontv_datum] in the table from where I extract the data.
I don't want input boxes "from date" - "to date"

Thanks for your help !

 

[MicroE]

Example Application:
http://www.myaccesstips.com/UI/Reports.html

FIxed

 

[RuralGuy]

MicroE,
That URL should *not* work on anyone's machine. At least those with a Hosts file.

 

[pookie62]

RuralGuy is correct.. it doesn't work..

 

[MicroE]

Sorry - I fixed it.

 

[pookie62]

Sorry MicroE but this link doesn't help much.. no code visible or sample for download..
Anyone else can help me ?
I found these functions:

Function StartOfMonth(D As Date)As Variant 
StartOfMonth = DateSerial(Year(D), Month(D) - 1, 1)
End Function

Function EndOfMonth(D As Date) As Variant
EndOfMonth = DateSerial(Year(D), Month(D) + 0, 0)
End Function

Can't get them implemented..
Query for the report is:

SELECT Polissen.AdvNaam, 
Verzekering.maatschappij, 
BLADIEBLA====
WHERE (((Polissen.[Provisie-ontv])=Yes) 
AND 
[B]((Polissen.Ontv_datum) Between StartOfMonth(Date()) And EndOfMonth(Date())));[/B]

Suggestions are welcome..
Thanks

 

[MicroE]

Take the criteria out of the SQL. In your code create the WHERE condition and pass it as an argument when opening the report.

Dim Criteria As String
Criteria = "[Provisie-ontv]=TRUE and Ontv_datum Between #" & StartOfMonth(Date) & "# And #" & EndOfMonth(Date) & "#"
DoCmd.OpenReport "Your Report", acViewPreview, , Criteria

 

[RuralGuy]

How about:

((Polissen.Ontv_datum) Between StartOfMonth(DateAdd("m",-1,Date())) And EndOfMonth(Date())));

 

[RuralGuy]

MicroE is correct and I'm a big fan of just applying a filter to a report with the WhereCondition argument as MicroE has done. That way the report is always working of of a generic RecordSource from which many reports can be generated.

 

[pookie62]

Tried both solutions :
RuralGuy's only returns from previous month and the month before that (?!), not the current..
MicroE's opens the report but doens't filter anything while I set one record to a date that it has to show.

Created a module with the functions, removed the criteria's from the query and put the code behind the button that opens the report.

Hope you're not out of ideas ?

 

[MicroE]

To get the end of the current month the EndOfMonth needs to be:

Function EndOfMonth(D As Date) As Variant
EndOfMonth = DateSerial(Year(D), Month(D) + 1, 0)
End Function

 

[RuralGuy]

I just realized that the StartOfMonth() function you posted is really the StartOfPreviousMonth(), so you don't need the DateAdd() code I added. I don't see why you are not getting the End of the current month.

 

[RuralGuy]

MicroE is correct again! I missed that.

 

[pookie62]

I was plying with the query and got this one working the way I wanted:

WHERE (((Polissen.[Provisie-ontv])=Yes) AND ((Polissen.Ontv_datum) Between StartOfMonth(DateAdd("m",0,Date())) And EndOfMonth(Date()))) OR (((Polissen.[Provisie-ontv])=Yes) AND ((Polissen.Ontv_datum) Between StartOfMonth(DateAdd("m",1,Date())) And EndOfMonth(Date())));

I suppose changing the function is better ?
Will test that one as well, thanks a lot for your help guys !! Appreciate !!!!!