Karl,
I was going to post a different question to the group, but I find that I
haven't solved this problem. I hope you have time to direct me.
Here's my subquery that I struggled over until the Discussion Group staged
an intervention(!):
SELECT Contacts.ContactID, Max(DuesLineItem.DateCreated) AS
MaxOfDateCreated, Max(DateAdd("d",365,[DateCreated])) AS [Membership
Expiration]
FROM Contacts INNER JOIN DuesLineItem ON Contacts.ContactID =
DuesLineItem.ContactID
GROUP BY Contacts.ContactID
HAVING (((Max(DateAdd("d",365,[DateCreated]))) Is Not Null));
I'll post the whole query (sorry and hope that's OK) and highlight the issue:
SELECT DISTINCT Contacts.ContactID, Contacts.LastName,
Val(Diff2Dates("y",[MemberDate],Date())) AS Years,
IIf(IsNull([NickName]),[FirstName] & " " & [LastName],[Nickname] & " " &
[LastName]) AS Name, IIf([AddressFlag]=1,[contacts].[MailingAddress1] & ",
"+[contacts].[OptAddress1],[contacts].[MailingAddress2] & ",
"+[contacts].[OptAddress2]) AS Address,
IIf([AddressFlag]=1,[contacts].[City1],[contacts].[City2]) AS City,
IIf([AddressFlag]=1,UCase([contacts].[StateOrProvince1]),UCase([contacts].[StateOrProvince2]))
AS State,
IIf([AddressFlag]=1,[contacts].[PostalCode1],[contacts].[PostalCode2]) AS
PostalCode, Contacts.MemberDate,
FldConcat3("AnimalNB2","Animal","ContactID",[Contacts].[ContactID]) AS
[Animal List], [Renewal subquery].[Membership Expiration],
Val(Diff2Dates("m",[Membership Expiration],Date())) AS DueDate,
Contacts.MembershipLetterDate,
DateDiff("m",[MembershipLetterDate],[Membership Expiration],True) AS
MonthsToExp
FROM (Contacts INNER JOIN MemberStatus ON Contacts.MemberStatusID =
MemberStatus.MemberStatusID) INNER JOIN [Renewal subquery] ON
Contacts.ContactID = [Renewal subquery].ContactID
GROUP BY Contacts.ContactID, Contacts.LastName,
Val(Diff2Dates("y",[MemberDate],Date())), IIf(IsNull([NickName]),[FirstName]
& " " & [LastName],[Nickname] & " " & [LastName]),
IIf([AddressFlag]=1,[contacts].[MailingAddress1] & ",
"+[contacts].[OptAddress1],[contacts].[MailingAddress2] & ",
"+[contacts].[OptAddress2]),
IIf([AddressFlag]=1,[contacts].[City1],[contacts].[City2]),
IIf([AddressFlag]=1,UCase([contacts].[StateOrProvince1]),UCase([contacts].[StateOrProvince2])),
IIf([AddressFlag]=1,[contacts].[PostalCode1],[contacts].[PostalCode2]),
Contacts.MemberDate,
FldConcat3("AnimalNB2","Animal","ContactID",[Contacts].[ContactID]), [Renewal
subquery].[Membership Expiration], Val(Diff2Dates("m",[Membership
Expiration],Date())), Contacts.MembershipLetterDate,
DateDiff("m",[MembershipLetterDate],[Membership Expiration],True),
Contacts.MemberOption, MemberStatus.MemberStatus
HAVING (((Val(Diff2Dates("m",[Membership Expiration],Date()))) Between 0 And
1) AND ((Contacts.MemberOption)=1) AND
((MemberStatus.MemberStatus)="Current-Active")) OR
(((Val(Diff2Dates("m",[Membership Expiration],Date()))) Between 0 And 1) AND
((Contacts.MemberOption)=1) AND
((MemberStatus.MemberStatus)="current-Active"))
ORDER BY Val(Diff2Dates("y",[MemberDate],Date()));
The issue:
DateDiff("m",[MembershipLetterDate],[Membership Expiration],True)
You told me that I can't use [Membership Expiration] since it's a calculated
field
but I can't seem to get this:
Max(DateAdd("d",365,[DateCreated]))
to work in the datediff calculation. How can I write it so that I can get:
DateDiff("m",[MembershipLetterDate],[Membership Expiration],True) AS
MonthsToExp
I'll try to explain what I'm ultimately trying to do as it may affect how I
need the query criterion (and I'll be glad to post this question as a new
one)- I want to add a MembershipLetterDate field so that one month before a
membership is due, I send out a renewal letter (flagging the
MembershipLetterDate field as today's date). Every year, I'll want to send
the letter one month before the expiration date. So if I use the statement
"between 0 and 1", that doesn't take into consideration the SerialDate. I'll
want to see if the MembershipLetterDate is blank or within 11 months of
expiration. To append the MembershipLetterDate, I have code I'll modify:
strWhere = "[MembershipLetterDate] Is Null Or [MembershipLetterDate] <
DateSerial(Year(Date()),1,1)" then I'm not sure how to change the
DateSerial...
Sorry to dump- one step at a time. The question at hand is how to handle
DateDiff("m",[MembershipLetterDate],[Membership Expiration],True) AS
MonthsToExp
with Max(DateAdd("d",365,[DateCreated])) AS [Membership Expiration]
I appreciate your time if you have any to spare ;-)
Cheers!
KARL DEWEY said:
You can not use a calculated field name in the same query that does the
calculation. You have to use the calculation instead.
--
KARL DEWEY
Build a little - Test a little
:
Interesting!
I do have a Diff2Dates function and I use it all of the time so I forgot
about DateDiff. I still can't quite figure out what in my function wasn't
allowing the date calculation, but you are correct- DateDiff was the way to
go.
Thanks for solving my frustration!
Cheers!
:
Unless you've created your own Diff2Dates function, I think you need the
DateDiff Function.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
Arg!
I have a date field in a table: MembershipLetterDate
I have a subquery on a different table that contains:
Membership Expiration: Max(DateAdd("d",365,[DateCreated]))
In a query, I'm trying to calculate the number of months between the
MembershipLetterDate and the Membership Expiration.
I refer to the subquery and try Diff2Dates:
MonthsToExp: Diff2Dates("m",[MembershipLetterDate],[Membership
Expiration],True)
And receive the datatype mismatch. These are both dates, although one is
calculated. Is that a problem? How can I fix it? Thanks- subqueries while
useful, usually trip me up.
