Month End Report

[Guest]

Hi,

I am trying to automate some of my reports. I have a report which will send
via email to a distribution list on the 1st Day of every month. What I need
to do is tell my query that the report is based on to only take data from the
last full month. Here is the criteria I am trying to manipulate to work.

DatePart("m",Now())=DatePart("m",[Approval Timestamp])

Problem is if I try it like this:

DatePart("m",-1,Now())=DatePart("m",-1,[Approval Timestamp])

I get a data type error.

Can anyone help?

Enigo

 

[Guest]

Is Approval Timestamp a date/time field? Is it possible the field might
contain nulls?
Looking at just the month might pull records from other years.
I would try a criteria under the Approval Timestamp column something like:

Between DateSerial(Year(Date()), Month(Date())-1,1) AND
DateAdd("d",-Day(Date()), Date())

 

[Guest]

Hi Duane,

I managed to fix the problem. I created a month formula and a year formula.
I then dropped them in a query and changed my criteria Month -1 and Year =
Year. THis worked perfectly.

Thanks again for all your help. I used your Concatenate function earlier on
in the build of my DB. Works like a charm.

Enigo

Is Approval Timestamp a date/time field? Is it possible the field might
contain nulls?
Looking at just the month might pull records from other years.
I would try a criteria under the Approval Timestamp column something like:

Between DateSerial(Year(Date()), Month(Date())-1,1) AND
DateAdd("d",-Day(Date()), Date())

--
Duane Hookom
Microsoft Access MVP

Hi,

I am trying to automate some of my reports. I have a report which will send
via email to a distribution list on the 1st Day of every month. What I need
to do is tell my query that the report is based on to only take data from the
last full month. Here is the criteria I am trying to manipulate to work.

DatePart("m",Now())=DatePart("m",[Approval Timestamp])

Problem is if I try it like this:

DatePart("m",-1,Now())=DatePart("m",-1,[Approval Timestamp])

I get a data type error.

Can anyone help?

Enigo

 

[Guest]

Will you current solution handle January dates?

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

I managed to fix the problem. I created a month formula and a year formula.
I then dropped them in a query and changed my criteria Month -1 and Year =
Year. THis worked perfectly.

Thanks again for all your help. I used your Concatenate function earlier on
in the build of my DB. Works like a charm.

Enigo

Is Approval Timestamp a date/time field? Is it possible the field might
contain nulls?
Looking at just the month might pull records from other years.
I would try a criteria under the Approval Timestamp column something like:

Between DateSerial(Year(Date()), Month(Date())-1,1) AND
DateAdd("d",-Day(Date()), Date())

--
Duane Hookom
Microsoft Access MVP

Hi,

I am trying to automate some of my reports. I have a report which will send
via email to a distribution list on the 1st Day of every month. What I need
to do is tell my query that the report is based on to only take data from the
last full month. Here is the criteria I am trying to manipulate to work.

DatePart("m",Now())=DatePart("m",[Approval Timestamp])

Problem is if I try it like this:

DatePart("m",-1,Now())=DatePart("m",-1,[Approval Timestamp])

I get a data type error.

Can anyone help?

Enigo

 

[Guest]

Hi Duane,

sorry about the late reply. On further reflection. This will impact January
dates. I will have to rethink my methodology. to fix this.

Any suggestions are welcome.

Will you current solution handle January dates?

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

I managed to fix the problem. I created a month formula and a year formula.
I then dropped them in a query and changed my criteria Month -1 and Year =
Year. THis worked perfectly.

Thanks again for all your help. I used your Concatenate function earlier on
in the build of my DB. Works like a charm.

Enigo

Is Approval Timestamp a date/time field? Is it possible the field might
contain nulls?
Looking at just the month might pull records from other years.
I would try a criteria under the Approval Timestamp column something like:

Between DateSerial(Year(Date()), Month(Date())-1,1) AND
DateAdd("d",-Day(Date()), Date())

--
Duane Hookom
Microsoft Access MVP


:

Hi,

I am trying to automate some of my reports. I have a report which will send
via email to a distribution list on the 1st Day of every month. What I need
to do is tell my query that the report is based on to only take data from the
last full month. Here is the criteria I am trying to manipulate to work.

DatePart("m",Now())=DatePart("m",[Approval Timestamp])

Problem is if I try it like this:

DatePart("m",-1,Now())=DatePart("m",-1,[Approval Timestamp])

I get a data type error.

Can anyone help?

Enigo

 

[Guest]

Did you even try my suggestion?
--
Duane Hookom
Microsoft Access MVP

Hi Duane,

sorry about the late reply. On further reflection. This will impact January
dates. I will have to rethink my methodology. to fix this.

Any suggestions are welcome.

Will you current solution handle January dates?

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

I managed to fix the problem. I created a month formula and a year formula.
I then dropped them in a query and changed my criteria Month -1 and Year =
Year. THis worked perfectly.

Thanks again for all your help. I used your Concatenate function earlier on
in the build of my DB. Works like a charm.

Enigo

:

Is Approval Timestamp a date/time field? Is it possible the field might
contain nulls?
Looking at just the month might pull records from other years.
I would try a criteria under the Approval Timestamp column something like:

Between DateSerial(Year(Date()), Month(Date())-1,1) AND
DateAdd("d",-Day(Date()), Date())

--
Duane Hookom
Microsoft Access MVP


:

Hi,

I am trying to automate some of my reports. I have a report which will send
via email to a distribution list on the 1st Day of every month. What I need
to do is tell my query that the report is based on to only take data from the
last full month. Here is the criteria I am trying to manipulate to work.

DatePart("m",Now())=DatePart("m",[Approval Timestamp])

Problem is if I try it like this:

DatePart("m",-1,Now())=DatePart("m",-1,[Approval Timestamp])

I get a data type error.

Can anyone help?

Enigo

 

[Guest]

Sorry Duane. I over looked that. Been so busy here. I'll give it a try now.

Did you even try my suggestion?
--
Duane Hookom
Microsoft Access MVP

Hi Duane,

sorry about the late reply. On further reflection. This will impact January
dates. I will have to rethink my methodology. to fix this.

Any suggestions are welcome.

Will you current solution handle January dates?

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,

I managed to fix the problem. I created a month formula and a year formula.
I then dropped them in a query and changed my criteria Month -1 and Year =
Year. THis worked perfectly.

Thanks again for all your help. I used your Concatenate function earlier on
in the build of my DB. Works like a charm.

Enigo

:

Is Approval Timestamp a date/time field? Is it possible the field might
contain nulls?
Looking at just the month might pull records from other years.
I would try a criteria under the Approval Timestamp column something like:

Between DateSerial(Year(Date()), Month(Date())-1,1) AND
DateAdd("d",-Day(Date()), Date())

--
Duane Hookom
Microsoft Access MVP


:

Hi,

I am trying to automate some of my reports. I have a report which will send
via email to a distribution list on the 1st Day of every month. What I need
to do is tell my query that the report is based on to only take data from the
last full month. Here is the criteria I am trying to manipulate to work.

DatePart("m",Now())=DatePart("m",[Approval Timestamp])

Problem is if I try it like this:

DatePart("m",-1,Now())=DatePart("m",-1,[Approval Timestamp])

I get a data type error.

Can anyone help?

Enigo

 

[Guest]

Duane,

I am getting an error. It says the expression I have entered contains too
many arguements.

Here is the formula as I have entered it.

Between DateSerial(Year(Date([Approval Timestamp])), Month(Date([Approval
Timestamp]))-1,1) AND DateAdd("d",-Day(Date([Approval Timestamp])),
Date([Approval Timestamp]))

 

[Guest]

You didn't understand my suggestion. Your criteria under teh Approval
Timestamp field/column should be:

Between DateSerial(Year(Date()), Month(Date())-1,1) AND
DateAdd("d",-Day(Date()), Date())

Don't change my expression.

--
Duane Hookom
Microsoft Access MVP

Duane,

I am getting an error. It says the expression I have entered contains too
many arguements.

Here is the formula as I have entered it.

Between DateSerial(Year(Date([Approval Timestamp])), Month(Date([Approval
Timestamp]))-1,1) AND DateAdd("d",-Day(Date([Approval Timestamp])),
Date([Approval Timestamp]))

Hi,

I am trying to automate some of my reports. I have a report which will send
via email to a distribution list on the 1st Day of every month. What I need
to do is tell my query that the report is based on to only take data from the
last full month. Here is the criteria I am trying to manipulate to work.

DatePart("m",Now())=DatePart("m",[Approval Timestamp])

Problem is if I try it like this:

DatePart("m",-1,Now())=DatePart("m",-1,[Approval Timestamp])

I get a data type error.

Can anyone help?

Enigo

 

[Guest]

Perfect. Works like a charm.

Thanks again

You didn't understand my suggestion. Your criteria under teh Approval
Timestamp field/column should be:

Between DateSerial(Year(Date()), Month(Date())-1,1) AND
DateAdd("d",-Day(Date()), Date())

Don't change my expression.

--
Duane Hookom
Microsoft Access MVP

Duane,

I am getting an error. It says the expression I have entered contains too
many arguements.

Here is the formula as I have entered it.

Between DateSerial(Year(Date([Approval Timestamp])), Month(Date([Approval
Timestamp]))-1,1) AND DateAdd("d",-Day(Date([Approval Timestamp])),
Date([Approval Timestamp]))

Hi,

I am trying to automate some of my reports. I have a report which will send
via email to a distribution list on the 1st Day of every month. What I need
to do is tell my query that the report is based on to only take data from the
last full month. Here is the criteria I am trying to manipulate to work.

DatePart("m",Now())=DatePart("m",[Approval Timestamp])

Problem is if I try it like this:

DatePart("m",-1,Now())=DatePart("m",-1,[Approval Timestamp])

I get a data type error.

Can anyone help?

Enigo

 

[John Spencer]

That is not what Duane recommended. I would recommend the following slight
modification to get all records where the Time Stamp was in the previous
month.

Field: Approval TimeStamp
Criteria: >=DateSerial(Year(Date()), Month(Date())-1,1) and <
DateSerial(Year(Date()), Month(Date()),1)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Duane,

I am getting an error. It says the expression I have entered contains too
many arguements.

Here is the formula as I have entered it.

Between DateSerial(Year(Date([Approval Timestamp])), Month(Date([Approval
Timestamp]))-1,1) AND DateAdd("d",-Day(Date([Approval Timestamp])),
Date([Approval Timestamp]))

Hi,

I am trying to automate some of my reports. I have a report which will
send
via email to a distribution list on the 1st Day of every month. What I
need
to do is tell my query that the report is based on to only take data from
the
last full month. Here is the criteria I am trying to manipulate to work.

DatePart("m",Now())=DatePart("m",[Approval Timestamp])

Problem is if I try it like this:

DatePart("m",-1,Now())=DatePart("m",-1,[Approval Timestamp])

I get a data type error.

Can anyone help?

Enigo