Macro's date criteria doesn't work for "last month"

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I have a form that uses a combo box for filtering dates... when clicked the
combo calls a macro and shows records that match e.g. "Today", "This Week",
"Last Week", etc.

This works great on a single form... however, I now integrated a subform
(datasheet view). Now when clicking on the date filter, I get an error
indicating "type mismatch".

I posted a sample file at URL:
http://www.savefile.com/files.php?fid=5841563

Also, the "LastMonth" criteria doesn't seem to work...

anything wrong with the criteria: (Year([Opened Date])=Year(Date()) And
Month([Opened Date])=(Month(Date())-1))

Again, I'd like to find out how I can use it via form/subform
(frmCompany/frmNotes).

Any ideas how to fix those issues?

Tom
 
Tom,

I haven't looked at your file. What are the actions you used in your
macro? You may need to make sure you are using the correct syntax to
reference the subform, if the combobox is on the main form. Something
like...
[Forms]![NameOfMainForm]![NameOfSubformControl].[Form]

Year([Opened Date])=Year(Date()) And Month([Opened
Date])=(Month(Date())-1) will not work for this month - it will be
looking for data where the month value is 0. Maybe like this will be
better...
Format([Opened Date],"yymm")=Format(DateAdd("m",-1,Date()),"yymm")
 
Steve:

thanks for the feedback... yes, the "LastMonth" criteria works great now.

In respect to the form/subform scenario:

Currently, I have a single form... the macro's Condition equals:
[Forms]![frmNotesWorksFine]![OpenedDateFilter]="Today"... that works great

now, I have the following:
Mainform: "frmCompany"
Subform: "frmNotes"

Are you suggesting the following???
[Forms]![frmCompany]![frmNotes].[Form]]![OpenedDateFilter]="Today"


Thanks,
Tom










Steve Schapel said:
Tom,

I haven't looked at your file. What are the actions you used in your
macro? You may need to make sure you are using the correct syntax to
reference the subform, if the combobox is on the main form. Something
like...
[Forms]![NameOfMainForm]![NameOfSubformControl].[Form]

Year([Opened Date])=Year(Date()) And Month([Opened
Date])=(Month(Date())-1) will not work for this month - it will be looking
for data where the month value is 0. Maybe like this will be better...
Format([Opened Date],"yymm")=Format(DateAdd("m",-1,Date()),"yymm")

--
Steve Schapel, Microsoft Access MVP
I have a form that uses a combo box for filtering dates... when clicked
the
combo calls a macro and shows records that match e.g. "Today", "This
Week",
"Last Week", etc.

This works great on a single form... however, I now integrated a subform
(datasheet view). Now when clicking on the date filter, I get an error
indicating "type mismatch".

I posted a sample file at URL:
http://www.savefile.com/files.php?fid=5841563

Also, the "LastMonth" criteria doesn't seem to work...

anything wrong with the criteria: (Year([Opened Date])=Year(Date()) And
Month([Opened Date])=(Month(Date())-1))

Again, I'd like to find out how I can use it via form/subform
(frmCompany/frmNotes).

Any ideas how to fix those issues?

Tom
Click to expand...
Click to expand...
 
Tom,

As I mentioned before, it is very difficult to help if you can't provide
details of what you are doing. I still have no idea what macro actions
you are using, where you are calling the macro from, what it is supposed
to do, and what the problem is. Now I know that you are trying to use
this expression in a macro Condition, so this helps somewhat.

If the OpenedDateFilter control is on the frmNotes subform, and the
macro is being triggered from an event on the frmCompany form, then the
correct syntax for the Condition would be like this...
[frmNotes].[Form]![OpenedDateFilter]="Today"
 
Steve,

actually the date filter control resides on the main form.

Tom




Steve Schapel said:
Tom,

As I mentioned before, it is very difficult to help if you can't provide
details of what you are doing. I still have no idea what macro actions
you are using, where you are calling the macro from, what it is supposed
to do, and what the problem is. Now I know that you are trying to use
this expression in a macro Condition, so this helps somewhat.

If the OpenedDateFilter control is on the frmNotes subform, and the macro
is being triggered from an event on the frmCompany form, then the correct
syntax for the Condition would be like this...
[frmNotes].[Form]![OpenedDateFilter]="Today"

--
Steve Schapel, Microsoft Access MVP

Steve:

thanks for the feedback... yes, the "LastMonth" criteria works great now.

In respect to the form/subform scenario:

Currently, I have a single form... the macro's Condition equals:
[Forms]![frmNotesWorksFine]![OpenedDateFilter]="Today"... that works
great

now, I have the following:
Mainform: "frmCompany"
Subform: "frmNotes"

Are you suggesting the following???
[Forms]![frmCompany]![frmNotes].[Form]]![OpenedDateFilter]="Today"
Click to expand...
Click to expand...
 
Back
Top