DateAdd against textbox value

[Tim Long]

Hello, can you please tell me what I'm doing wrong here?

I have a textbox (txtMonthYear) on a form (frmReports). The textbox has no
format, but has an input mask 00/0000

I want to run a variety of queries/reports off the value the user enters in
txtMonthYear. So when the user enters 01/2008 I want to run a report, for
example, on the data from January 2008.

Testing how I could do this, I ran the following query. It works fine:

SELECT tblClients.Client_ID, tblClients.d_received, Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") = [Forms]![frmReports].[txtMonthYear]

This gives me the data for January, no problem. Trying to test for the data
for the month prior to that, I ran the following query:

SELECT tblClients.Client_ID, tblClients.d_received, Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") = Format(DateAdd("M",
-1,[Forms]![frmReports].[txtMonthYear]), "mmyyyy")

This returned nothing.. no error message, just no data (but there is plenty
of data for that month).

If I run the following, however, it works fine (returns last month's data):

SELECT tblClients.Client_ID, tblClients.d_received, Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") = Format(DateAdd("M", -1,Date(),"mmyyyy")

I've checked that txtMonthYear doesn't have the focus (tabbed out of it to
make sure) when I ran the above queries.

Eventually I want to run 12-month reports off the user-provided month/year,
so when the user enters 01/2008, a report can be generated for the twelve
months to that month (Feb 2007 tio Jan 2008 inclusive). I am planning on
using DateAdd and "M", -12 to give me the 12 months worth of data but can't
get it working. It's driving me up the wall!

Any help would be much appreciated.

Many thanks

Tim Long

 

[Allen Browne]

Tim, there's a problem here with the data type of the text box. I'm not sure
if Access is treating it as Text or a Number, but it certainly is not a
date.

Also, the Format() function in your query does convert the date field into a
string. This is going to be inefficient for selecting records (if you have
lots.)

The best solution would be to add a button to your form for opening the
report. You can leave the criteria out of the query, and the button will
build the WhereCondition string for OpenReport to filter it for the desired
month, or show all months if the user did not fill in any date.

The code for the Click event procedure of your command button would be
something like this:
Dim strWhere As String
Dim dtStart As Date
Const strcJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.txtMonthYear) Then
dtStart = DateSerial(Mid(Me.txtMonthYear, 3,4),
Left(Me.txtMonthYear,2),1)
strWhere = "([d_received] >= " & Format(dtStart, strcJetDate) & _
") AND ([d_received] < " & Format(DateAdd("m", 1, dtStart),
strcJetDate) & ")"
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

Advantages of this approach:
a) The WhereCondition is crafted so Access can use any index you have on the
date field.

b) Dates on the last day of the month work correctly, even if they have a
time component.

c) The code can be extended to include an end date (e.g. results for a
quarter.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello, can you please tell me what I'm doing wrong here?

I have a textbox (txtMonthYear) on a form (frmReports). The textbox has no
format, but has an input mask 00/0000

I want to run a variety of queries/reports off the value the user enters
in
txtMonthYear. So when the user enters 01/2008 I want to run a report, for
example, on the data from January 2008.

Testing how I could do this, I ran the following query. It works fine:

SELECT tblClients.Client_ID, tblClients.d_received, Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") = [Forms]![frmReports].[txtMonthYear]

This gives me the data for January, no problem. Trying to test for the
data
for the month prior to that, I ran the following query:

SELECT tblClients.Client_ID, tblClients.d_received, Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") = Format(DateAdd("M",
-1,[Forms]![frmReports].[txtMonthYear]), "mmyyyy")

This returned nothing.. no error message, just no data (but there is
plenty
of data for that month).

If I run the following, however, it works fine (returns last month's
data):

SELECT tblClients.Client_ID, tblClients.d_received, Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") =
Format(DateAdd("M", -1,Date(),"mmyyyy")

I've checked that txtMonthYear doesn't have the focus (tabbed out of it
to
make sure) when I ran the above queries.

Eventually I want to run 12-month reports off the user-provided
month/year,
so when the user enters 01/2008, a report can be generated for the twelve
months to that month (Feb 2007 tio Jan 2008 inclusive). I am planning on
using DateAdd and "M", -12 to give me the 12 months worth of data but
can't
get it working. It's driving me up the wall!

Any help would be much appreciated.

Many thanks

Tim Long

 

[Tim Long]

Allen, many thanks, I will try that, but just at the moment I’m hoping to
find a solution that works with the queries I have. Maybe I’m wrong to want
to do that so I should explain where I am with this. At the moment I’ve got
the following tables, etc:

I’ve got a table called tblClients. Apart from the usual name, address etc,
there are date fields; (eg the date the customer referral was received, which
is the d_received field in my example query); the date the customer was first
contacted, etc.. these are all ‘activities’ that have to be reported. I’ve
got to generate a crosstab report detailing how much of each activity was
performed per month..

So I have something like the following designed already:

Month Jan Feb Mar
Activity

Received 15 11 29
Contacted 8 12 32
Doc issued 15 22 36
Doc signed 7 11 6
Completed 11 13 17

A button on a form launches the report. I wrote some basic code that runs a
number of queries, each one similar to the following:

INSERT INTO tblDataForPivots ( Client_ID, [Order], DataMonth, Activity )
SELECT tblClients.Client_ID, "10" AS [Order], Month([d_received]) AS
DataMonth, IIf([d_received] Is Not Null,"Client Referrals Received","") AS
Activity
FROM tblClients
WHERE (((tblClients.d_received) Is Not Null));

‘Order’ allows me to determine the order in which the activities are listed
(so ‘10’ for Received, ‘20’ for Contacted etc). ‘DataMonth’ allows me to pool
the activities under the correct month in the crosstab report.
‘Activity’ provides a user-friendly label (so ‘Referrals Received’ rather
than ‘d_received’ etc).

Each query appends the data to a table (tblDataForPivots) and when the
queries have run and the table is populated, the next piece of code launches
the crosstab report. On this report is a subreport for which the record
source is the following query (‘qryCrossTabActivityByMonth’):

TRANSFORM Count(tblDataforPivots.Client_ID) AS CountOfClient_ID
SELECT tblDataforPivots.Activity, tblDataforPivots.Order
FROM tblDataforPivots
GROUP BY tblDataforPivots.Activity, tblDataforPivots.Order
ORDER BY tblDataforPivots.Order, MonthName([DataMonth])
PIVOT MonthName([DataMonth]) In
("January","February","March","April","May","June","July","August","September","October","November","December");

The subreport comprises a label ‘Activity’ and a text box for each of the
twelve months.

I thought I was getting somewhere with this, but there are major flaws.
Firstly, because there are no ‘parameters’ to the dates, everything is
captured (January 2006, January 2007, January 2008 under ‘January’) when what
is actually required is just 12 months’ data.

Also, I don’t know how to get the month headings to display in the correct
order (so if I ran it from February 2007 to January 2008, I’d want to see
‘February’ first and ‘January’ last, instead of January, February)

I wanted to try to get the dates sorted first, so that my report was pulling
in only the data for a twelve month period, not everything. I’d be loathe to
abandon everything I’ve done so far, but if it would be best to do that, I
would be happy to adopt a more efficient method. It’s just that looking at
the solution you gave, I couldn’t see how to make it work, given that I’m
reporting on eleven date fields (activities). The example I gave was just a
simplified example unfortunately!

I’ve tried adjusting the text box (changing it to a date format) in view of
what you were saying.. I was hoping that there might be an easy solution to
the date problem – I’d like to make the process of generating the report as
straightforward as possible for the user but I know that there are not many
‘easy’ solutions out there!

Hoping you can shed some further light.. Many thanks again Allen, much
appreciated.

Tim, there's a problem here with the data type of the text box. I'm not sure
if Access is treating it as Text or a Number, but it certainly is not a
date.

Also, the Format() function in your query does convert the date field into a
string. This is going to be inefficient for selecting records (if you have
lots.)

The best solution would be to add a button to your form for opening the
report. You can leave the criteria out of the query, and the button will
build the WhereCondition string for OpenReport to filter it for the desired
month, or show all months if the user did not fill in any date.

The code for the Click event procedure of your command button would be
something like this:
Dim strWhere As String
Dim dtStart As Date
Const strcJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.txtMonthYear) Then
dtStart = DateSerial(Mid(Me.txtMonthYear, 3,4),
Left(Me.txtMonthYear,2),1)
strWhere = "([d_received] >= " & Format(dtStart, strcJetDate) & _
") AND ([d_received] < " & Format(DateAdd("m", 1, dtStart),
strcJetDate) & ")"
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

Advantages of this approach:
a) The WhereCondition is crafted so Access can use any index you have on the
date field.

b) Dates on the last day of the month work correctly, even if they have a
time component.

c) The code can be extended to include an end date (e.g. results for a
quarter.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello, can you please tell me what I'm doing wrong here?

I have a textbox (txtMonthYear) on a form (frmReports). The textbox has no
format, but has an input mask 00/0000

I want to run a variety of queries/reports off the value the user enters
in
txtMonthYear. So when the user enters 01/2008 I want to run a report, for
example, on the data from January 2008.

Testing how I could do this, I ran the following query. It works fine:

SELECT tblClients.Client_ID, tblClients.d_received, Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") = [Forms]![frmReports].[txtMonthYear]

This gives me the data for January, no problem. Trying to test for the
data
for the month prior to that, I ran the following query:

SELECT tblClients.Client_ID, tblClients.d_received, Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") = Format(DateAdd("M",
-1,[Forms]![frmReports].[txtMonthYear]), "mmyyyy")

This returned nothing.. no error message, just no data (but there is
plenty
of data for that month).

If I run the following, however, it works fine (returns last month's
data):

SELECT tblClients.Client_ID, tblClients.d_received, Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") =
Format(DateAdd("M", -1,Date(),"mmyyyy")

I've checked that txtMonthYear doesn't have the focus (tabbed out of it
to
make sure) when I ran the above queries.

Eventually I want to run 12-month reports off the user-provided
month/year,
so when the user enters 01/2008, a report can be generated for the twelve
months to that month (Feb 2007 tio Jan 2008 inclusive). I am planning on
using DateAdd and "M", -12 to give me the 12 months worth of data but
can't
get it working. It's driving me up the wall!

Any help would be much appreciated.

Many thanks

Tim Long

 

[Allen Browne]

Oh dear. You have a table with columns for each month?
That sounds like a reasonable way to design a spreadsheet, but it's not how
you design a database.

It may be possible to make that work, but my suggestion would be to use a
normalized design.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, many thanks, I will try that, but just at the moment I’m hoping to
find a solution that works with the queries I have. Maybe I’m wrong to
want
to do that so I should explain where I am with this. At the moment I’ve
got
the following tables, etc:

I’ve got a table called tblClients. Apart from the usual name, address
etc,
there are date fields; (eg the date the customer referral was received,
which
is the d_received field in my example query); the date the customer was
first
contacted, etc.. these are all ‘activities’ that have to be reported. I’ve
got to generate a crosstab report detailing how much of each activity was
performed per month..

So I have something like the following designed already:

Month Jan Feb Mar
Activity

Received 15 11 29
Contacted 8 12 32
Doc issued 15 22 36
Doc signed 7 11 6
Completed 11 13 17

A button on a form launches the report. I wrote some basic code that runs
a
number of queries, each one similar to the following:

INSERT INTO tblDataForPivots ( Client_ID, [Order], DataMonth, Activity )
SELECT tblClients.Client_ID, "10" AS [Order], Month([d_received]) AS
DataMonth, IIf([d_received] Is Not Null,"Client Referrals Received","") AS
Activity
FROM tblClients
WHERE (((tblClients.d_received) Is Not Null));

‘Order’ allows me to determine the order in which the activities are
listed
(so ‘10’ for Received, ‘20’ for Contacted etc). ‘DataMonth’ allows me to
pool
the activities under the correct month in the crosstab report.
‘Activity’ provides a user-friendly label (so ‘Referrals Received’ rather
than ‘d_received’ etc).

Each query appends the data to a table (tblDataForPivots) and when the
queries have run and the table is populated, the next piece of code
launches
the crosstab report. On this report is a subreport for which the record
source is the following query (‘qryCrossTabActivityByMonth’):

TRANSFORM Count(tblDataforPivots.Client_ID) AS CountOfClient_ID
SELECT tblDataforPivots.Activity, tblDataforPivots.Order
FROM tblDataforPivots
GROUP BY tblDataforPivots.Activity, tblDataforPivots.Order
ORDER BY tblDataforPivots.Order, MonthName([DataMonth])
PIVOT MonthName([DataMonth]) In
("January","February","March","April","May","June","July","August","September","October","November","December");

The subreport comprises a label ‘Activity’ and a text box for each of the
twelve months.

I thought I was getting somewhere with this, but there are major flaws.
Firstly, because there are no ‘parameters’ to the dates, everything is
captured (January 2006, January 2007, January 2008 under ‘January’) when
what
is actually required is just 12 months’ data.

Also, I don’t know how to get the month headings to display in the correct
order (so if I ran it from February 2007 to January 2008, I’d want to see
‘February’ first and ‘January’ last, instead of January, February)

I wanted to try to get the dates sorted first, so that my report was
pulling
in only the data for a twelve month period, not everything. I’d be loathe
to
abandon everything I’ve done so far, but if it would be best to do that, I
would be happy to adopt a more efficient method. It’s just that looking at
the solution you gave, I couldn’t see how to make it work, given that I’m
reporting on eleven date fields (activities). The example I gave was just
a
simplified example unfortunately!

I’ve tried adjusting the text box (changing it to a date format) in view
of
what you were saying.. I was hoping that there might be an easy solution
to
the date problem – I’d like to make the process of generating the report
as
straightforward as possible for the user but I know that there are not
many
‘easy’ solutions out there!

Hoping you can shed some further light.. Many thanks again Allen, much
appreciated.

Tim, there's a problem here with the data type of the text box. I'm not
sure
if Access is treating it as Text or a Number, but it certainly is not a
date.

Also, the Format() function in your query does convert the date field
into a
string. This is going to be inefficient for selecting records (if you
have
lots.)

The best solution would be to add a button to your form for opening the
report. You can leave the criteria out of the query, and the button will
build the WhereCondition string for OpenReport to filter it for the
desired
month, or show all months if the user did not fill in any date.

The code for the Click event procedure of your command button would be
something like this:
Dim strWhere As String
Dim dtStart As Date
Const strcJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.txtMonthYear) Then
dtStart = DateSerial(Mid(Me.txtMonthYear, 3,4),
Left(Me.txtMonthYear,2),1)
strWhere = "([d_received] >= " & Format(dtStart, strcJetDate) & _
") AND ([d_received] < " & Format(DateAdd("m", 1, dtStart),
strcJetDate) & ")"
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

Advantages of this approach:
a) The WhereCondition is crafted so Access can use any index you have on
the
date field.

b) Dates on the last day of the month work correctly, even if they have a
time component.

c) The code can be extended to include an end date (e.g. results for a
quarter.)

Hello, can you please tell me what I'm doing wrong here?

I have a textbox (txtMonthYear) on a form (frmReports). The textbox has
no
format, but has an input mask 00/0000

I want to run a variety of queries/reports off the value the user
enters
in
txtMonthYear. So when the user enters 01/2008 I want to run a report,
for
example, on the data from January 2008.

Testing how I could do this, I ran the following query. It works fine:

SELECT tblClients.Client_ID, tblClients.d_received,
Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") =
[Forms]![frmReports].[txtMonthYear]

This gives me the data for January, no problem. Trying to test for the
data
for the month prior to that, I ran the following query:

SELECT tblClients.Client_ID, tblClients.d_received,
Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") = Format(DateAdd("M",
-1,[Forms]![frmReports].[txtMonthYear]), "mmyyyy")

This returned nothing.. no error message, just no data (but there is
plenty
of data for that month).

If I run the following, however, it works fine (returns last month's
data):

SELECT tblClients.Client_ID, tblClients.d_received,
Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") =
Format(DateAdd("M", -1,Date(),"mmyyyy")

I've checked that txtMonthYear doesn't have the focus (tabbed out of
it
to
make sure) when I ran the above queries.

Eventually I want to run 12-month reports off the user-provided
month/year,
so when the user enters 01/2008, a report can be generated for the
twelve
months to that month (Feb 2007 tio Jan 2008 inclusive). I am planning
on
using DateAdd and "M", -12 to give me the 12 months worth of data but
can't
get it working. It's driving me up the wall!

 

[Tim Long]

Hi Allen, I don't have a table with columns for each month. Reading back my
posting I realised that it was confusing: I wrote 'what I have at the moment'
referring to my first posting, meaning 'The crosstab report I have at the
moment' but on reading it back I do see how it confusing it was. It's the
crosstab 12-month report that has twelve month columns. And I have a dozen
rows in the report because there are a dozen dated activities to report on.
As I'd never done a crosstab report before I couldn't think of how to
populate the crosstab report using a single query (and add data to it at the
same time, such as a 'sort field' that allowed me to display the rows in an
order that I wanted to determine). Using the intermediate table and a bit of
code seemed to work but a couple of things were very obviously wrong with the
functioning.

There were a few things I had to change to get the 'rolling' order in the
months and for the query dates I've just decided to ask the user to input the
start date and end date into two textboxes on the form from where the report
is launched and that seems to avoid a lot of problems. The report's working
just as I need it to now.

Many thanks for the suggestions in your first posting, I'll definitely bear
it in mind the next time I'm tackling crosstab reports.

Tim Long

Oh dear. You have a table with columns for each month?
That sounds like a reasonable way to design a spreadsheet, but it's not how
you design a database.

It may be possible to make that work, but my suggestion would be to use a
normalized design.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, many thanks, I will try that, but just at the moment I’m hoping to
find a solution that works with the queries I have. Maybe I’m wrong to
want
to do that so I should explain where I am with this. At the moment I’ve
got
the following tables, etc:

I’ve got a table called tblClients. Apart from the usual name, address
etc,
there are date fields; (eg the date the customer referral was received,
which
is the d_received field in my example query); the date the customer was
first
contacted, etc.. these are all ‘activities’ that have to be reported. I’ve
got to generate a crosstab report detailing how much of each activity was
performed per month..

So I have something like the following designed already:

Month Jan Feb Mar
Activity

Received 15 11 29
Contacted 8 12 32
Doc issued 15 22 36
Doc signed 7 11 6
Completed 11 13 17

A button on a form launches the report. I wrote some basic code that runs
a
number of queries, each one similar to the following:

INSERT INTO tblDataForPivots ( Client_ID, [Order], DataMonth, Activity )
SELECT tblClients.Client_ID, "10" AS [Order], Month([d_received]) AS
DataMonth, IIf([d_received] Is Not Null,"Client Referrals Received","") AS
Activity
FROM tblClients
WHERE (((tblClients.d_received) Is Not Null));

‘Order’ allows me to determine the order in which the activities are
listed
(so ‘10’ for Received, ‘20’ for Contacted etc). ‘DataMonth’ allows me to
pool
the activities under the correct month in the crosstab report.
‘Activity’ provides a user-friendly label (so ‘Referrals Received’ rather
than ‘d_received’ etc).

Each query appends the data to a table (tblDataForPivots) and when the
queries have run and the table is populated, the next piece of code
launches
the crosstab report. On this report is a subreport for which the record
source is the following query (‘qryCrossTabActivityByMonth’):

TRANSFORM Count(tblDataforPivots.Client_ID) AS CountOfClient_ID
SELECT tblDataforPivots.Activity, tblDataforPivots.Order
FROM tblDataforPivots
GROUP BY tblDataforPivots.Activity, tblDataforPivots.Order
ORDER BY tblDataforPivots.Order, MonthName([DataMonth])
PIVOT MonthName([DataMonth]) In
("January","February","March","April","May","June","July","August","September","October","November","December");

The subreport comprises a label ‘Activity’ and a text box for each of the
twelve months.

I thought I was getting somewhere with this, but there are major flaws.
Firstly, because there are no ‘parameters’ to the dates, everything is
captured (January 2006, January 2007, January 2008 under ‘January’) when
what
is actually required is just 12 months’ data.

Also, I don’t know how to get the month headings to display in the correct
order (so if I ran it from February 2007 to January 2008, I’d want to see
‘February’ first and ‘January’ last, instead of January, February)

I wanted to try to get the dates sorted first, so that my report was
pulling
in only the data for a twelve month period, not everything. I’d be loathe
to
abandon everything I’ve done so far, but if it would be best to do that, I
would be happy to adopt a more efficient method. It’s just that looking at
the solution you gave, I couldn’t see how to make it work, given that I’m
reporting on eleven date fields (activities). The example I gave was just
a
simplified example unfortunately!

I’ve tried adjusting the text box (changing it to a date format) in view
of
what you were saying.. I was hoping that there might be an easy solution
to
the date problem – I’d like to make the process of generating the report
as
straightforward as possible for the user but I know that there are not
many
‘easy’ solutions out there!

Hoping you can shed some further light.. Many thanks again Allen, much
appreciated.

Tim, there's a problem here with the data type of the text box. I'm not
sure
if Access is treating it as Text or a Number, but it certainly is not a
date.

Also, the Format() function in your query does convert the date field
into a
string. This is going to be inefficient for selecting records (if you
have
lots.)

The best solution would be to add a button to your form for opening the
report. You can leave the criteria out of the query, and the button will
build the WhereCondition string for OpenReport to filter it for the
desired
month, or show all months if the user did not fill in any date.

The code for the Click event procedure of your command button would be
something like this:
Dim strWhere As String
Dim dtStart As Date
Const strcJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.txtMonthYear) Then
dtStart = DateSerial(Mid(Me.txtMonthYear, 3,4),
Left(Me.txtMonthYear,2),1)
strWhere = "([d_received] >= " & Format(dtStart, strcJetDate) & _
") AND ([d_received] < " & Format(DateAdd("m", 1, dtStart),
strcJetDate) & ")"
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

Advantages of this approach:
a) The WhereCondition is crafted so Access can use any index you have on
the
date field.

b) Dates on the last day of the month work correctly, even if they have a
time component.

c) The code can be extended to include an end date (e.g. results for a
quarter.)

Hello, can you please tell me what I'm doing wrong here?

I have a textbox (txtMonthYear) on a form (frmReports). The textbox has
no
format, but has an input mask 00/0000

I want to run a variety of queries/reports off the value the user
enters
in
txtMonthYear. So when the user enters 01/2008 I want to run a report,
for
example, on the data from January 2008.

Testing how I could do this, I ran the following query. It works fine:

SELECT tblClients.Client_ID, tblClients.d_received,
Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") =
[Forms]![frmReports].[txtMonthYear]

This gives me the data for January, no problem. Trying to test for the
data
for the month prior to that, I ran the following query:

SELECT tblClients.Client_ID, tblClients.d_received,
Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") = Format(DateAdd("M",
-1,[Forms]![frmReports].[txtMonthYear]), "mmyyyy")

This returned nothing.. no error message, just no data (but there is
plenty
of data for that month).

If I run the following, however, it works fine (returns last month's
data):

SELECT tblClients.Client_ID, tblClients.d_received,
Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") =
Format(DateAdd("M", -1,Date(),"mmyyyy")

I've checked that txtMonthYear doesn't have the focus (tabbed out of
it
to
make sure) when I ran the above queries.

Eventually I want to run 12-month reports off the user-provided
month/year,
so when the user enters 01/2008, a report can be generated for the
twelve
months to that month (Feb 2007 tio Jan 2008 inclusive). I am planning
on
using DateAdd and "M", -12 to give me the 12 months worth of data but
can't
get it working. It's driving me up the wall!