data type mismatch

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

I am working on a report that should return only current members.
In the CommitmentDate table, each time a member renews, their renewal date
is entered along with the enrollment period which is typically one year.
I created a query to give me the date to which the membership is valid. So
if a member renews 7/29/2009 for one year, the query returns 7/29/2010 as the
"valid to" date. Another column sorts them into "current" or "expired"
members based on the current date. The query is working and it follows:

SELECT MemberCommitmentDates.MemberLookup,
qryMemberNameLookup.NameLookupLNF, MemberCommitmentDates.CommitmentDate,
MemberCommitmentDates.CommitmentPeriodYears,
DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) AS MemberValidTo,
IIf(DateValue([MemberValidTo])>Date(),"Current","Expired") AS MemberStatus
FROM qryMemberNameLookup INNER JOIN MemberCommitmentDates ON
qryMemberNameLookup.ID = memberCommitmentDates.MemberLookup;

The problem I am having is with the report. I can get the report to give me
all members and their status, but when I try to filter by Member Status I get
a "Data Type Mismatch". I have tried various forms of filter statements
based on things I have read here, but I can only be sure of this attempt:
[MemberStatus] = "current"

Any suggestions are appreciated!
 
I expect the issue is with the MemberValidTo column. I never use a
derived/calculated column in another expression in the same query.
Try something like the following which expects a numeric
CommitmentPeriodYears and a date Commitmentdate:

SELECT MemberCommitmentDates.MemberLookup,
qryMemberNameLookup.NameLookupLNF, MemberCommitmentDates.CommitmentDate,
MemberCommitmentDates.CommitmentPeriodYears,
DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) AS MemberValidTo,
IIf(DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) >Date()
,"Current","Expired") AS MemberStatus
FROM qryMemberNameLookup INNER JOIN MemberCommitmentDates ON
qryMemberNameLookup.ID = memberCommitmentDates.MemberLookup;
 
Applying criteria to MemberStatus is also the same situation as it is
derived within the same query.
Try this --
SELECT MemberCommitmentDates.MemberLookup,
qryMemberNameLookup.NameLookupLNF, MemberCommitmentDates.CommitmentDate,
MemberCommitmentDates.CommitmentPeriodYears
FROM qryMemberNameLookup INNER JOIN MemberCommitmentDates ON
qryMemberNameLookup.ID = memberCommitmentDates.MemberLookup
WHERE DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) >Date();

--
Build a little, test a little.


Duane Hookom said:
I expect the issue is with the MemberValidTo column. I never use a
derived/calculated column in another expression in the same query.
Try something like the following which expects a numeric
CommitmentPeriodYears and a date Commitmentdate:

SELECT MemberCommitmentDates.MemberLookup,
qryMemberNameLookup.NameLookupLNF, MemberCommitmentDates.CommitmentDate,
MemberCommitmentDates.CommitmentPeriodYears,
DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) AS MemberValidTo,
IIf(DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) >Date()
,"Current","Expired") AS MemberStatus
FROM qryMemberNameLookup INNER JOIN MemberCommitmentDates ON
qryMemberNameLookup.ID = memberCommitmentDates.MemberLookup;

--
Duane Hookom
Microsoft Access MVP


Mary said:
I am working on a report that should return only current members.
In the CommitmentDate table, each time a member renews, their renewal date
is entered along with the enrollment period which is typically one year.
I created a query to give me the date to which the membership is valid. So
if a member renews 7/29/2009 for one year, the query returns 7/29/2010 as the
"valid to" date. Another column sorts them into "current" or "expired"
members based on the current date. The query is working and it follows:

SELECT MemberCommitmentDates.MemberLookup,
qryMemberNameLookup.NameLookupLNF, MemberCommitmentDates.CommitmentDate,
MemberCommitmentDates.CommitmentPeriodYears,
DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) AS MemberValidTo,
IIf(DateValue([MemberValidTo])>Date(),"Current","Expired") AS MemberStatus
FROM qryMemberNameLookup INNER JOIN MemberCommitmentDates ON
qryMemberNameLookup.ID = memberCommitmentDates.MemberLookup;

The problem I am having is with the report. I can get the report to give me
all members and their status, but when I try to filter by Member Status I get
a "Data Type Mismatch". I have tried various forms of filter statements
based on things I have read here, but I can only be sure of this attempt:
[MemberStatus] = "current"

Any suggestions are appreciated!
Click to expand...
Click to expand...
 
Thank you both for your advice. I have implemented both strategies and I am
not quite there yet.
To confirm, the date is a date/time field and the number of years is a
number field.

When I implemented Duane's advice I got the "data type mismatch" notice at
the same point -- at the report but only when I tried to filter
[MemberStatus] = "Current"

When I implemented Karls' suggestion I again got the "data type mismatch"
notification but it didn't let me see anything. Most fields say "#Name?"
([NameLookupLNF], [CommitmentDate], and [CommitmentPeriodYears])

If you have any other suggestions I sure would appreciate continued guidance.


KARL DEWEY said:
Applying criteria to MemberStatus is also the same situation as it is
derived within the same query.
Try this --
SELECT MemberCommitmentDates.MemberLookup,
qryMemberNameLookup.NameLookupLNF, MemberCommitmentDates.CommitmentDate,
MemberCommitmentDates.CommitmentPeriodYears
FROM qryMemberNameLookup INNER JOIN MemberCommitmentDates ON
qryMemberNameLookup.ID = memberCommitmentDates.MemberLookup
WHERE DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) >Date();

--
Build a little, test a little.


Duane Hookom said:
I expect the issue is with the MemberValidTo column. I never use a
derived/calculated column in another expression in the same query.
Try something like the following which expects a numeric
CommitmentPeriodYears and a date Commitmentdate:

SELECT MemberCommitmentDates.MemberLookup,
qryMemberNameLookup.NameLookupLNF, MemberCommitmentDates.CommitmentDate,
MemberCommitmentDates.CommitmentPeriodYears,
DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) AS MemberValidTo,
IIf(DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) >Date()
,"Current","Expired") AS MemberStatus
FROM qryMemberNameLookup INNER JOIN MemberCommitmentDates ON
qryMemberNameLookup.ID = memberCommitmentDates.MemberLookup;

--
Duane Hookom
Microsoft Access MVP


Mary said:
I am working on a report that should return only current members.
In the CommitmentDate table, each time a member renews, their renewal date
is entered along with the enrollment period which is typically one year.
I created a query to give me the date to which the membership is valid. So
if a member renews 7/29/2009 for one year, the query returns 7/29/2010 as the
"valid to" date. Another column sorts them into "current" or "expired"
members based on the current date. The query is working and it follows:

SELECT MemberCommitmentDates.MemberLookup,
qryMemberNameLookup.NameLookupLNF, MemberCommitmentDates.CommitmentDate,
MemberCommitmentDates.CommitmentPeriodYears,
DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) AS MemberValidTo,
IIf(DateValue([MemberValidTo])>Date(),"Current","Expired") AS MemberStatus
FROM qryMemberNameLookup INNER JOIN MemberCommitmentDates ON
qryMemberNameLookup.ID = memberCommitmentDates.MemberLookup;

The problem I am having is with the report. I can get the report to give me
all members and their status, but when I try to filter by Member Status I get
a "Data Type Mismatch". I have tried various forms of filter statements
based on things I have read here, but I can only be sure of this attempt:
[MemberStatus] = "current"

Any suggestions are appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Are any of the fields possibly null?
--
Duane Hookom
Microsoft Access MVP


Mary said:
Thank you both for your advice. I have implemented both strategies and I am
not quite there yet.
To confirm, the date is a date/time field and the number of years is a
number field.

When I implemented Duane's advice I got the "data type mismatch" notice at
the same point -- at the report but only when I tried to filter
[MemberStatus] = "Current"

When I implemented Karls' suggestion I again got the "data type mismatch"
notification but it didn't let me see anything. Most fields say "#Name?"
([NameLookupLNF], [CommitmentDate], and [CommitmentPeriodYears])

If you have any other suggestions I sure would appreciate continued guidance.


KARL DEWEY said:
Applying criteria to MemberStatus is also the same situation as it is
derived within the same query.
Try this --
SELECT MemberCommitmentDates.MemberLookup,
qryMemberNameLookup.NameLookupLNF, MemberCommitmentDates.CommitmentDate,
MemberCommitmentDates.CommitmentPeriodYears
FROM qryMemberNameLookup INNER JOIN MemberCommitmentDates ON
qryMemberNameLookup.ID = memberCommitmentDates.MemberLookup
WHERE DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) >Date();

--
Build a little, test a little.


Duane Hookom said:
I expect the issue is with the MemberValidTo column. I never use a
derived/calculated column in another expression in the same query.
Try something like the following which expects a numeric
CommitmentPeriodYears and a date Commitmentdate:

SELECT MemberCommitmentDates.MemberLookup,
qryMemberNameLookup.NameLookupLNF, MemberCommitmentDates.CommitmentDate,
MemberCommitmentDates.CommitmentPeriodYears,
DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) AS MemberValidTo,
IIf(DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) >Date()
,"Current","Expired") AS MemberStatus
FROM qryMemberNameLookup INNER JOIN MemberCommitmentDates ON
qryMemberNameLookup.ID = memberCommitmentDates.MemberLookup;

--
Duane Hookom
Microsoft Access MVP


:

I am working on a report that should return only current members.
In the CommitmentDate table, each time a member renews, their renewal date
is entered along with the enrollment period which is typically one year.
I created a query to give me the date to which the membership is valid. So
if a member renews 7/29/2009 for one year, the query returns 7/29/2010 as the
"valid to" date. Another column sorts them into "current" or "expired"
members based on the current date. The query is working and it follows:

SELECT MemberCommitmentDates.MemberLookup,
qryMemberNameLookup.NameLookupLNF, MemberCommitmentDates.CommitmentDate,
MemberCommitmentDates.CommitmentPeriodYears,
DateAdd("yyyy",[CommitmentPeriodYears],[CommitmentDate]) AS MemberValidTo,
IIf(DateValue([MemberValidTo])>Date(),"Current","Expired") AS MemberStatus
FROM qryMemberNameLookup INNER JOIN MemberCommitmentDates ON
qryMemberNameLookup.ID = memberCommitmentDates.MemberLookup;

The problem I am having is with the report. I can get the report to give me
all members and their status, but when I try to filter by Member Status I get
a "Data Type Mismatch". I have tried various forms of filter statements
based on things I have read here, but I can only be sure of this attempt:
[MemberStatus] = "current"

Any suggestions are appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top