By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,333 Members | 1,962 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,333 IT Pros & Developers. It's quick & easy.

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

P: 16
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("yyyy",[tblPosting].[strdate]))=DatePart("yyyy",[me].[cbodate])))
GROUP BY tblPosting.MstClock
HAVING (((tblPosting.MstClock)=[me].[empid]));)".
If I put this in a query and run the query it works.

Any help will be greatly appreciated.
Oct 23 '06 #1
Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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))
Oct 23 '06 #2

P: 16
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"
Oct 23 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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]))
Oct 23 '06 #4

P: 16
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"
Oct 23 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Try

=DSum("[Occurance]","tblPosting","DatePart('yyyy',[strdate])=" & DatePart("yyyy", [cboDate]) & " AND [MstClock]=" & [empid])
Oct 23 '06 #6

P: 16
Try

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



Still has "#Error" What is [/quote]?
Oct 23 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Try

=DSum("[Occurance]","tblPosting","Year([strdate])=" & Year([cboDate]) & " AND [MstClock]=" & [empid])
Oct 23 '06 #8

P: 16
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...
Oct 23 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.