Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Nick Bygrave is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2021
    Posts
    7

    Problem with query field where I need to limit results to result level of calculation

    I have two fields in a query:
    i} Expr2: Val(Right(Year(Now()),2)) - the last two digits of this Year
    ii) Expr1: Val(Right([Past Renewal Years],2)) - Past Renewal Years contains final two digits of past years the field of which is in the format eg: /17/18/19/20

    I created another query field (subtracting one from the other): [Expr3]: [Expr2]-[Expr1]

    I only want the query to show those records where Expr3 is greater than 2 but I can't make it work. I tried making the Criteria field for Expr3 be >2 but that turned to field into a Yes/No result.

    Can anyone advise me how to do this?

    Thanks in advance.



    Nick Bygrave

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Due to the order in which clauses are evaluated, you can't use an alias in the criteria. Try this (in SQL view)

    WHERE Val(Right(Year(Now()),2)) - Val(Right([Past Renewal Years],2)) > 2
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Nick Bygrave is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2021
    Posts
    7
    Thanks very much. I will try it.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    think your logic is wrong

    [Expr2]-[Expr1]

    Expr2=say 19
    Expr1=21

    19-21=-2

    where you appear to expect to be seeing +2

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Nick Bygrave View Post
    Thanks very much. I will try it.
    No problem, post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    another option is to ignore the first two, and write [Expr3] out in full, which would give you this:
    Code:
    (Val(Right(Year(Now()),2))) - (Val(Right([Past Renewal Years],2)))
    
    WHERE (((Val(Right(Year(Now()),2))) - (Val(Right([Past Renewal Years],2))))>2)
    although I would personally use Date() rather than Now() unless you want the time as well.


    good luck with your project,



    Cottonshirt

  7. #7
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    Quote Originally Posted by Ajax View Post
    think your logic is wrong

    [Expr2]-[Expr1]

    Expr2=say 19
    Expr1=21

    19-21=-2

    where you appear to expect to be seeing +2
    as a kind of general rule, the number of the current year tends to be higher than previous years. on average.


    Cottonshirt
    Last edited by Cottonshirt; 10-22-2021 at 04:30 AM. Reason: spelling

  8. #8
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Maybe it is easier to use the function Datediff to calc the date intervals?
    something like:
    Code:
    Where Datediff("yyyy", [Past Renewal Years], date()) > 2

  9. #9
    Nick Bygrave is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2021
    Posts
    7
    I'm having trouble with inserting the clause you suggested. Could you please help me and say where it should go in the full SQL statement below. I have tried several places but am always told there is an error. Let me know if I can provide more information.
    The statement is:
    SELECT MEMBERS.Surname, MEMBERS.[First names], MEMBERS.Title, MEMBERS.Salutation, [Title] & " " & [First names] & " " & [Surname] AS FullName, MEMBERS.[Address 1], MEMBERS.[Address 2], MEMBERS.[Address 3], MEMBERS.[Address 4], MEMBERS.[Address 5], MEMBERS.Postcode, MEMBERS.Phone, MEMBERS.Mobile, MEMBERS.Email, MEMBERS.[Category 4], MEMBERS.[Friends Renewal Month], [Address 1] & " " & [Address 2] & " " & [Address 3] & " " & [Address 4] & " " & [Postcode] AS [Full Address], MEMBERS.GDPRSigned, MEMBERS.[Last Amount Donated], MEMBERS.[Date Last Amount Recd], MEMBERS.GDPRSigned, MEMBERS.[Past Renewal Years], MEMBERS.[Preferred Contact Method], Val(Right([Past Renewal Years],2)) AS Expr1, Val(Right(Year(Now()),2)) AS Expr2, [Expr2]-[Expr1] AS Expr3
    FROM MEMBERS
    WHERE (((MEMBERS.[Category 4])="Friend" Or (MEMBERS.[Category 4])="Friend-Volunteer" Or (MEMBERS.[Category 4])="Friend-User" Or (MEMBERS.[Category 4])="Friend-Honorary" Or (MEMBERS.[Category 4])="Friend-Lapsed"));

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    As a kind of general rule, the number of the current year tends to be higher than previous years. on average.
    Pretty sure OP's original post had it the other way round, but perhaps I imagined it

  11. #11
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    MEMBERS.[Preferred Contact Method], Val(Right([Past Renewal Years],2)) AS Expr1, Val(Right(Year(Now()),2)) AS Expr2, datediff("yyyy",date(), cdate("1-1-" & Right([Past Renewal Years],2))) AS Expr3

  12. #12
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do your self a favor and remove ALL spaces in object names - fields, tables, queries, forms, reports.

    [Address 1] <<-- BAD name - has spaces
    [Address_1] <<-- Better name
    [Address1] <<-- Best name

    [Friends Renewal Month] <<-- BAD name - has spaces
    [Friends_Renewal_Month] <<-- Better name
    [FriendsRenewalMonth] <<-- Best name



    The WHERE clause
    Code:
    WHERE (((MEMBERS.[Category 4])="Friend" Or (MEMBERS.[Category 4])="Friend-Volunteer" Or (MEMBERS.[Category 4])="Friend-User" Or (MEMBERS.[Category 4])="Friend-Honorary" Or (MEMBERS.[Category 4])="Friend-Lapsed"));
    could be written
    Code:
    WHERE MEMBERS.[Category 4] Like "Friend*";

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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*";
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So , in the OP first's post, he says
    Quote Originally Posted by Nick Bygrave View Post
    <snip> i} Expr2: Val(Right(Year(Now()),2)) - the last two digits of this Year
    ii) Expr1: Val(Right([Past Renewal Years],2)) - Past Renewal Years contains final two digits of past years the field of which is in the format eg: /17/18/19/20
    and
    Quote Originally Posted by Nick Bygrave View Post
    <snip> I only want the query to show those records where Expr3 is greater than 2 <snip>

    Using Colin's modified query (Post#13) , this is the query that has some form of "Friend" in Category4 AND Expr2-Expr1 > 2
    Code:
    SELECT MEMBERS.Surname, MEMBERS.FirstName, MEMBERS.Title, MEMBERS.Salutation, [Title] & " " & FirstName & " " & Surname AS FullName, MEMBERS.Address1, MEMBERS.Address2, MEMBERS.Address3, MEMBERS.Address4, MEMBERS.Address5, MEMBERS.Postcode, MEMBERS.Phone, MEMBERS.Mobile, MEMBERS.Email, MEMBERS.Category4, MEMBERS.FriendsRenewalMonth, Address1 & " " & Address2 & " " & Address3 & " " & Address4 & " " & Postcode AS FullAddress, MEMBERS.GDPRSigned, MEMBERS.LastAmountDonated, MEMBERS.DateLastAmountRecd, MEMBERS.GDPRSigned, MEMBERS.PastRenewalYears, MEMBERS.PreferredContactMethod, Right(PastRenewalYears,2) AS Expr1, Right(Year(Date()),2) AS Expr2, Right(Year(Date()),2)-Right([PastRenewalYears],2) AS Expr3
    FROM MEMBERS
    WHERE ((MEMBERS.Category4 Like "Friend*") AND ((Right(Year(Date()),2)-Right([PastRenewalYears],2))>2));
    (the field MEMBERS.GDPRSigned was added twice, so I deleted one)

    Note that if the data in the field PastRenewalYears is "/17/18/19/20/" , the calculation will fail... as will the query!!

    Also note that having data like "/17/18/19/20" in a field violates the rules of normalization. "PastRenewalYears" should be in its own table, with 1 year per record.
    I would have

    tblPastRenewalYears
    -----------------------
    PastRenewalYearsID_PK - Autonumber
    MembersID_FK - Long Integer (foreign key link to table tblMEMBERS)
    PastRenewalYear - Integer

  15. #15
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    another option for the criteria

    WHERE MEMBERS.Category4 Like "Friend*" AND Right([PastRenewalYears],2)<Right(Year(Date()),2)-2

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-07-2017, 02:40 PM
  2. Replies: 7
    Last Post: 01-19-2016, 05:28 PM
  3. Replies: 9
    Last Post: 10-30-2015, 05:14 PM
  4. Replies: 9
    Last Post: 05-27-2014, 04:53 PM
  5. Replies: 20
    Last Post: 03-30-2014, 12:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums