Criteria...How to express the last day of the previous month? 2

[gusbrunston]

In a query for a monthly report similar to a checking account statement from a bank, I need to calculate last month's beginning balance from all records entered up to and including the last day of the previous month. For example, for the report to be prepared after the end of September, I must select all the records with a transaction date up to and including the last day of August.

I don't know what to enter in the "Criteria" for the field "TransactionDate" to select only records entered before the last month (records for all months in previous years must be included). For example, this month the report for September transactions will be prepared. The beginning balance must be calculated from all transactions entered no later than the last day of August.

How do I express the Criteria, "The last day of the month that preceded last month" ? My best try (with "Month&quot eliminated the last four months of all previous years!

Thanks for any help.

Gus Brunston
An old PICKer
padregus@home.com

 

[gusbrunston]

Me again...

I guess a better way to express what I need to say is:
"Less than last month, this year."

Thanks. Gus Brunston
An old PICKer
padregus@home.com

 

[Databaseguy]

Give this a try...

Private Sub LastMonth()
Dim dteLast
dteLast = YourDate
dteLast = DateSerial(Year(dteLast), Month(dteLast), 0)  ' Fake out DateSerial to return last day of month
MsgBox dteLast

End Sub

Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[tempclerk]

In a query criteria, <=(Date()-Day(Date())) will get you anything on or before the last day of last month, but you seem to be asking for a month before that?
Goofy, but make Expr1: (Date()-Day(Date())), and then in the criteria of your date field put <=([Expr1]-Day([Expr1]))

 

[gusbrunston]

Hey, thanks for your suggestions.

1. Tyrone, I haven't tried to do this with a control on the form, but thought I could do it with a query. Maybe not...

2. Tempclerk, the reason I need the month before last month is to calculate last month's beginning balance for the report. (People on Tek-tips won't let me store last month's beginning balance!) I tried exactly what you suggested: Expr1 gives me &quot;9/30/2001&quot;. But when I enter &quot;<=([Expr1]-Day([Expr1]))&quot; as the Criteria for &quot;TransactionDate&quot; I get a parameter request for Expr1. ??? In terms of your response, what I need is &quot;anything before the FIRST day of last month.&quot;

Any further suggestions will be very much appreciated!

Gus Gus Brunston
An old PICKer
padregus@home.com

 

[tempclerk]

How about:
<=(Date()-Day(Date()))-Day(Date()-Day(Date()))

 

[Databaseguy]

If you declare it as a public function, and modify to include an argument, you can call it from a query just like any intrinsic function (date, time, etc)

Example:

Public Function LastMonth(dteLast as date)
dteLast = DateSerial(Year(dteLast), Month(dteLast), 0) ' Fake out DateSerial to return last day of month
LastMonth = dteLast
End Function Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[gusbrunston]

Wow! Lot's of ideas, right away. Thanks, while I try these out. Gus Brunston
An old PICKer
padregus@home.com

 

[gusbrunston]

I ended up using tempclerk's expression in the query...works great. I still have a lot to learn about modules and public functions, and Tyrone's post will be helpful for me, too.
I really appreciate the help I had with this thread!

Gus Brunston
An old PICKer
padregus@home.com