Get YTD totals on a form selecting from a table not in the form

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • JH001A
    New Member
    • Oct 2006
    • 16

    Get YTD totals on a form selecting from a table not in the form

    I have a form with a text box for YTD totals on an employee which is selected from a combo box, the YTD total displays #name? when I run the form. The control source is "=(SELECT Sum(tblPosting. Occurance) FROM tblPosting WHERE (((DatePart("yy yy",[tblPosting].[strdate]))=DatePart("yy yy",[me].[cbodate])))
    GROUP BY tblPosting.MstC lock
    HAVING (((tblPosting.M stClock)=[me].[empid]));)".
    If I put this in a query and run the query it works.

    Any help will be greatly appreciated.
  • MMcCarthy
    Recognized Expert MVP
    • Aug 2006
    • 14387

    #2
    You can't use a query as a control source. Try:

    =Sum(DLookup("[Occurance]","tblPosting", "DatePart('yyyy ',[strdate])=" & DatePart("yyyy" , Me.cboDate) & " AND [MstClock]=" & Me.empid))

    Comment

    • JH001A
      New Member
      • Oct 2006
      • 16

      #3
      Originally posted by mmccarthy
      You can't use a query as a control source. Try:

      =Sum(DLookup("[Occurance]","tblPosting", "DatePart('yyyy ',[strdate])=" & DatePart("yyyy" , Me.cboDate) & " AND [MstClock]=" & Me.empid))

      Now it dispalys "#Error"

      Comment

      • MMcCarthy
        Recognized Expert MVP
        • Aug 2006
        • 14387

        #4
        Sorry syntax error you can't use Me. in control source. Try this

        =Sum(DLookup("[Occurance]","tblPosting", "DatePart('yyyy ',[strdate])=" & DatePart("yyyy" , [cboDate]) & " AND [MstClock]=" & [empid]))

        Comment

        • JH001A
          New Member
          • Oct 2006
          • 16

          #5
          Originally posted by mmccarthy
          Sorry syntax error you can't use Me. in control source. Try this

          =Sum(DLookup("[Occurance]","tblPosting", "DatePart('yyyy ',[strdate])=" & DatePart("yyyy" , [cboDate]) & " AND [MstClock]=" & [empid]))

          Still has "#Error"

          Comment

          • MMcCarthy
            Recognized Expert MVP
            • Aug 2006
            • 14387

            #6
            Try

            =DSum("[Occurance]","tblPosting", "DatePart('yyyy ',[strdate])=" & DatePart("yyyy" , [cboDate]) & " AND [MstClock]=" & [empid])

            Comment

            • JH001A
              New Member
              • Oct 2006
              • 16

              #7
              Originally posted by mmccarthy
              Try

              =DSum("[Occurance]","tblPosting", "DatePart('yyyy ',[strdate])=" & DatePart("yyyy" , [cboDate]) & " AND [MstClock]=" & [empid])



              Still has "#Error" What is [/QUOTE]?

              Comment

              • MMcCarthy
                Recognized Expert MVP
                • Aug 2006
                • 14387

                #8
                Try

                =DSum("[Occurance]","tblPosting", "Year([strdate])=" & Year([cboDate]) & " AND [MstClock]=" & [empid])

                Comment

                • JH001A
                  New Member
                  • Oct 2006
                  • 16

                  #9
                  Originally posted by mmccarthy
                  Try

                  =DSum("[Occurance]","tblPosting", "Year([strdate])=" & Year([cboDate]) & " AND [MstClock]=" & [empid])

                  That one got it... Many many thanks for yout help. Guess the datepart function dosen't like DSum...

                  Comment

                  Working...