Find first day of last month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I can use the following to calculate the date one month ago. I want to take
this a step further and set it to the first day of last month.

DateAdd("m",-1,Now())

So if today is 4/01/05 then the fucntion would return 1/12/04.

Any ideas?

Bruce
 
Bruce said:
I can use the following to calculate the date one month ago. I want
to take this a step further and set it to the first day of last month.

DateAdd("m",-1,Now())

So if today is 4/01/05 then the fucntion would return 1/12/04.

Any ideas?

Bruce
Click to expand...

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

I am trying to use the DateSerial () function you list below as the
criteria in a query I am constructing. The field that I am setting the
dateserial() criteria for is a date/time field but I am getting a mismatched
data type error when I try to run the query. Do I have to do anything
special to have the query recognize the dateserial function as a date/time
field in the query's criteria grid? Thanks. Function posted below:

FieldName: WrittenDate
Table: Case
DataType: Date/Time
Criteria: >=(DateSerial(Year("Date"),Month("Date"),1))

Joe
 
FieldName: WrittenDate
Table: Case
DataType: Date/Time
Criteria: >=(DateSerial(Year("Date"),Month("Date"),1))
Click to expand...

The text string "Date" is a four-letter word. It's not a date, and
you'll get a type mismatch error trying to calculate the Year value of
a non-date text string value.

Try

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

to make it clear to Access that you want a reference to the Date()
function rather than a text string value.

John W. Vinson[MVP]
 
Joe said:
Rick,

I am trying to use the DateSerial () function you list below as the
criteria in a query I am constructing. The field that I am setting the
dateserial() criteria for is a date/time field but I am getting a
mismatched data type error when I try to run the query. Do I have to
do anything special to have the query recognize the dateserial
function as a date/time field in the query's criteria grid? Thanks.
Function posted below:

FieldName: WrittenDate
Table: Case
DataType: Date/Time
Criteria: >=(DateSerial(Year("Date"),Month("Date"),1))
Click to expand...

My mistake. In VBA code Access will normally strip off the parethesis from
the Date() function but those parenthesis are required if used in a query.
When they weren't there Access "volunteered" to place quotes aroung the
words "Date" which broke the expression.
 
Back
Top