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 !

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

FIxed

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

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

Sorry - I fixed it.

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
((Polissen.Ontv_datum) Between StartOfMonth(Date()) And EndOfMonth(Date())));


Suggestions are welcome..
Thanks

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

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

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.

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 ? ;)

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

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.

MicroE is correct again! I missed that.

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