Ah! Thanks for that info. Queries are always challening for me so it's good
to know where I went wrong to avoid at least that issue on the next query! I
appreciate your help.

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!