Month End Report

E

Enigo

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
 
D

Duane Hookom

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())
 
E

Enigo

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

Duane Hookom said:
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


Enigo said:
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
Click to expand...
Click to expand...
 
D

Duane Hookom

Will you current solution handle January dates?

--
Duane Hookom
Microsoft Access MVP


Enigo said:
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

Duane Hookom said:
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


Enigo said:
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
Click to expand...
Click to expand...
Click to expand...
 
E

Enigo

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.

Duane Hookom said:
Will you current solution handle January dates?

--
Duane Hookom
Microsoft Access MVP


Enigo said:
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

Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

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


Enigo said:
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.

Duane Hookom said:
Will you current solution handle January dates?

--
Duane Hookom
Microsoft Access MVP


Enigo said:
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
Click to expand...
Click to expand...
Click to expand...
 
E

Enigo

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

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


Enigo said:
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.

Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
E

Enigo

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]))
 
D

Duane Hookom

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


Enigo said:
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]))

Enigo said:
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
Click to expand...
Click to expand...
 
E

Enigo

Perfect. Works like a charm.

Thanks again

Duane Hookom said:
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


Enigo said:
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]))

Enigo said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

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
..

Enigo said:
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]))

Enigo said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Invalid use of null on report 1
Sorting/Grouping question on report 2
Display billing month based on start date and end date 5
Return all dates for 'next month' irrespective of year or day 4
Show week number in Project Report 0
Chart does not recognize my query criteria 4
Month problem 4
Calender report conversion. 9

Top