Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    15

    Unanswered: Running Sum Like ([Value])

    Hello... I was hoping someone could help me out with a bit of syntax.

    From a tutorial I have worked out a running sum query that is fine until I ask it to search by a name parameter value. I'm trying to filter the results by customer_name and between jobdate. ie you get a msg box asking for both values...

    Between dates works fine, but when you add Like [Customer Name] it ask for the name but there are no results returned. Between dates works fine on its own. I guess it comes from having jobTBL return as T1...

    Thanks for any help

    SELECT T1.jobID, T1.jobdate, T1.customer, T1.rate_sterling, (SELECT Sum(jobTBL.rate_sterling) AS Total
    FROM jobTBL
    WHERE jobTBL.jobID <= T1.jobID) AS Total
    FROM jobTBL AS T1
    GROUP BY T1.jobID, T1.jobdate, T1.customer, T1.rate_sterling
    HAVING (((T1.jobdate) Between [Enter Jobs Start Date:] And [Enter Jobs Finish Date:]) AND ((T1.customer) Like [Customer Name]));

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Macneil View Post
    Hello... I was hoping someone could help me out with a bit of syntax.

    From a tutorial I have worked out a running sum query that is fine until I ask it to search by a name parameter value. I'm trying to filter the results by customer_name and between jobdate. ie you get a msg box asking for both values...

    Between dates works fine, but when you add Like [Customer Name] it ask for the name but there are no results returned. Between dates works fine on its own. I guess it comes from having jobTBL return as T1...

    Thanks for any help

    SELECT T1.jobID, T1.jobdate, T1.customer, T1.rate_sterling, (SELECT Sum(jobTBL.rate_sterling) AS Total
    FROM jobTBL
    WHERE jobTBL.jobID <= T1.jobID) AS Total
    FROM jobTBL AS T1
    GROUP BY T1.jobID, T1.jobdate, T1.customer, T1.rate_sterling
    HAVING (((T1.jobdate) Between [Enter Jobs Start Date:] And [Enter Jobs Finish Date:]) AND ((T1.customer) Like [Customer Name]));
    try:
    Code:
    ((T1.customer) = [Customer Name]))
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Feb 2010
    Posts
    15
    Hi... Thanks but no joy.

    'This expression is typed incorrectly... etc'

    cheers

    When 'Like' is there it runs but no results returned.

  4. #4
    Join Date
    Feb 2010
    Posts
    15
    Sorted it the long way but it works...

    I just made another qry with all the fields from jobTBL but asking for the parameter for customer_name.

    Then changed the running sum qry to reference this jobQRY between dates. T1 wasn't a child of the customer table so I couldn't use customerTBL.customer_name.

    SELECT T1.jobID, T1.jobdate, T1.customer_name, T1.rate_sterling, (SELECT Sum(jobQRY.rate_sterling) AS Total
    FROM jobQRY
    WHERE jobQRY.jobID <= T1.jobID) AS Total
    FROM jobQRY AS T1
    GROUP BY T1.jobID, T1.jobdate, T1.customer_name, T1.rate_sterling
    HAVING (((T1.jobdate) Between [Enter Jobs Start Date:] And [Enter Jobs Finish Date:]));


    Works fine

  5. #5
    Join Date
    May 2010
    Posts
    601
    Glad you got it sorted out.

    Thanks for posting your solution so others may benefit.

    Cheers.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •