data type mismatch
in [Reports]
|
Prev: using iif and like in control Source of report
Next: Different addresses must be accessed depending on time of year.
From: Mary on 22 Dec 2009 14:47 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!
From: Duane Hookom on 22 Dec 2009 14:58 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" wrote: > 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!
From: KARL DEWEY on 22 Dec 2009 18:42 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" wrote: > 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" wrote: > > > 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!
From: Mary on 29 Dec 2009 15:37 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" wrote: > 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" wrote: > > > 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" wrote: > > > > > 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!
From: Duane Hookom on 30 Dec 2009 10:46
Are any of the fields possibly null? -- Duane Hookom Microsoft Access MVP "Mary" wrote: > 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" wrote: > > > 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" wrote: > > > > > 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" wrote: > > > > > > > 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! |