Access 2003 - Setting default value of a text box

[Amanda]

I have 2 text boxes in a form that default to two set dates. I'd like to
update the form so that txtStartDate is the first date of last month and
txtEndDate is the last date of this month. For example, if I were to pull
the form up today, I'd like txtStateDate's default to be 9/1/09 and
txtEndDate's default to be 10/31/09. If I pull it up the form in November,
txtStartDate should be 10/1/09 and txtEndDate is 11/31/09.

Both text boxes are unbound. Any suggestions?

 

[Arvin Meyer [MVP]]

I have 2 text boxes in a form that default to two set dates. I'd like to
update the form so that txtStartDate is the first date of last month and
txtEndDate is the last date of this month. For example, if I were to pull
the form up today, I'd like txtStateDate's default to be 9/1/09 and
txtEndDate's default to be 10/31/09. If I pull it up the form in
November,
txtStartDate should be 10/1/09 and txtEndDate is 11/31/09.

Both text boxes are unbound. Any suggestions?

Add these 2 functions to a standard module and call them from the
DefaultValue property:

Function BeginLastMonth()
BeginLastMonth = DateAdd("m", -1, DateSerial(Year(Date), Month(Date), 1))
End Function

Function LastDOM (DateInAMonth)
LastDOM = Day(DateSerial(Year(DateInAMonth), Month(DateInAMonth) + 1, 0))
End Function

 

[Klatuu]

?dateserial(year(date),month(date)-1,1)
9/1/2009
?dateserial(year(date), month(date)+1,0)
10/31/2009

 

[Amanda]

Thank you for your response! However, if I input

?dateserial(year(date),month(date)-1,1)

Access says there's an invalid operator in my expression. If I take the
question mark away, Access automatically updates the line to:

DateSerial(Year("Date"),Month("Date")-1,1)

And my text box gives me an #Error value. I've tried taking the quotes
away, but Access keeps putting 'em back.

 

[Amanda]

Thank you Arvin!

The first function did what I wanted beautifully, but as I played with the
second, I realized that you didn't need the Day() function in there. If you
just use

Function LastDOM(DateInAMonth)
LastDOM = DateSerial(Year(DateInAMonth), Month(DateInAMonth) + 1, 0)
End Function

It returns the last day of the month. Thank you both though for the help!

 

[Arvin Meyer [MVP]]

That's because Dave was doing the expression in the Immediate Window.

To use it in a query, it would look like:

Expr1: DateSerial(Year(Date()),Month(Date())-1,1)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com