month format

[momoko]

Pull query by month

Hi,
I currently have a date field and I want to pull out base by month, every month I need to report data as of the ending month. For example today is 09-Apr-03 and I want all records that is ending month of 31-Mar-03. How can I do this automatically? When I use the DateAdd function DateAdd("m",-1,Date()) it will give me those that are from the beginning till the 09-Mar-03 and this is not what I want. Please help!

 

[yippie_ky_yay]

Hi,

if you subtract today's day by itself, it will go back to the end of the previous month.

I don't know if you can do this directly on the report or not. Here's how to do it in VBA. Create an unbound textbox (here it's called Text0). When you go into the VBA part, select Detail from the left drop down and then Format from the right. VBA will automatically create the first and last lines for you (I don't know your level of experience - sorry if that was boring).

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim EndOfMonth As Variant
Dim DayOfMonth As Variant

'here we isolate the day
DayOfMonth = Format(Date, "dd")
'then we subtract that number from today
EndOfMonth = DateAdd("d", -DayOfMonth, Date)
'this will put the value in your text field
Text0 = EndOfMonth
End Sub

Hope that helps!

-Sean

 

[Rich]

Something like this help?
SELECT DISTINCTROW MaterialsList.ItemCost, Format$([MaterialsList].[DatePurch],"mmmm yyyy") AS [Date By Month]
FROM Suppliers INNER JOIN MaterialsList ON Suppliers.Supplier = MaterialsList.Supplier
WHERE (((Year([MaterialsList].[DatePurch])*12+DatePart("m",[MaterialsList].[DatePurch])-1)=Year(Date())*12+DatePart("m",Date())-1));