If you remove spaces, you don't need the [] brackets. So the WHERE clause could be written:
Code:
WHERE MEMBERS.Category4 Like "Friend*";
and because all fields are from one table, the complete query SQL could be:
Code:
SELECT Surname, FirstName, Title, Salutation, [Title] & " " & FirstName & " " & Surname AS FullName, Address1, Address2, Address3, Address4, Address5, Postcode, Phone, Mobile, Email, Category4,
FriendsRenewalMonth, Address1 & " " & Address2 & " " & Address3 & " " & Address4 & " " & Postcode AS FullAddress, GDPRSigned, LastAmountDonated, DateLastAmountRecd, GDPRSigned, PastRenewalYears,
PreferredContactMethod, Val(Right(PastRenewalYears,2)) AS Expr1, Val(Right(Year(Now()),2)) AS Expr2, Expr2-Expr1 AS Expr3
FROM MEMBERS
WHERE Category4 Like "Friend*";
Even then, I'm fairly sure the two Val expressions are required as each will be a number ...so you can omit those
But, it is likely Access won't cope with reusing calculated fields in Expr2-Expr1 AS Expr3, I would change that to:
Code:
SELECT Surname, FirstName, Title, Salutation, [Title] & " " & FirstName & " " & Surname AS FullName, Address1, Address2, Address3, Address4, Address5, Postcode, Phone, Mobile, Email, Category4,
FriendsRenewalMonth, Address1 & " " & Address2 & " " & Address3 & " " & Address4 & " " & Postcode AS FullAddress, GDPRSigned, LastAmountDonated, DateLastAmountRecd, GDPRSigned, PastRenewalYears,
PreferredContactMethod, Right(PastRenewalYears,2) AS Expr1, Right(Year(Now()),2) AS Expr2, Right(Year(Now()),2)-Right(PastRenewalYears,2) AS Expr3
FROM MEMBERS
WHERE Category4 Like "Friend*";