Full Version: Dsum Function
UtterAccess Forums > Microsoft® Access > Access Forms
sag
Hello,
I am trying to use the DSum function as the control source of a text box in a sub-form. I show this on the main form with =[InvoiceSubForm].[Form]![Text44]
It goes directly against a table, but I think I have the syntax wrong.
Could you please help if possible.
=DSum("[Invoice Amount]","Invoice Detail","[DCode] = [Forms]![Invoice Detail]![Dr Code] AND " & Format$([Entry Month Year],"yyyy") & " = " & format$(Now, "1/1/yyyy")")
All I need is the sum of the Invoice Amounts for the Current year.
Thank you.
JuanSoto
Hi SAG,
Try this:
=DSum("[Invoice Amount]","Invoice Detail","[DCode] = " & [Forms]![Invoice Detail]![Dr Code] & " AND Year([Entry Month Year]) = " & Year(Date)
Kind Regards
Juan
sag
JuanSoto,
Thank you for your quick response.
However, I get an error saying it has an invalid string.
Thank you.
sag
This seems to work:
DSum("[Invoice Amount]","Invoice Detail","[DCode] = [Forms]![Invoice Detail]![Dr Code] AND Year([Entry Month Year]) = 2011")
but not the syntax you gave.
There is something missing in the Year(Date) part - I tried various combinations of *) and =" & Year(Date) & *) but it won't work.
Thank you.
sag
Narrowing it down further if I put in an actual date, it works, else if I use the word Date it does not.
o I need some sort of reference to use this?
=DSum("[Invoice Amount]","Invoice Detail","[DCode] = [Forms]![Invoice Detail]![Dr Code] AND Year([Entry Month Year]) = " & Year([Date]))
=DSum("[Invoice Amount]","Invoice Detail","[DCode] = [Forms]![Invoice Detail]![Dr Code] AND Year([Entry Month Year]) = " & Year("1/1/2011"))
JuanSoto
SAG,
Try Date() instead of [Date], the latter would be considered a field and the former the actual date function that returns today's date.
BruceM
Two things come to mind. When used in an expression you need the parentheses for the Date function:
...Year(Date())
lso, if [Dr Code] is text, you need to do the quotes differently:
=DSum("[Invoice Amount]","Invoice Detail","[DCode] = '" & [Forms]![Invoice Detail]![Dr Code] & "' AND Year([Entry Month Year]) = " & Year(Date())
Expanded for clarity, the quotes are thus:
=DSum("[Invoice Amount]","Invoice Detail","[DCode] = ' " & [Forms]![Invoice Detail]![Dr Code] & " ' AND Year([Entry Month Year]) = " & Year(Date())
The above assumes [Dr Code] does not contain an apostrophe. If it does, substitute "" for each ' in the expression.
Odon't see how it could work if you leave [Forms] etc. inside the quote marks.
I see you have variously used DCode and Dr Code. Are they intended to be the same?
sag
Thank you so much JuanSoto and Bruce.
It worked with the Date().
However, it does not work with the [Forms]...outside the quote marks and I am not sure why. It seems rather strange, but I had played around with this prior to putting out the post. I am not sure if Acees 2007 is more forgiving - but I will look into that into more detail when I am not so swamped.
DCode and DrCode are the names of the field in the table vs. the field on the Main form.
Thanks a ton, though, to both of you for helping me out and to the forum team, who always answers all my questions.
Regards,
SAG
BruceM
You haven't mentioned whether Dr Code is a number or text field. In any case, you should test with:
=[Forms]![Invoice Detail]![Dr Code]
just to see if you get a value.
sag
Bruce,
FOr Code is a text field.
I was curious and tried this again, just because I was not confident about why the reference "Forms!....." was working within the quotes, especially after your input.
It seems to work now (both ways) and I choose to do it your way, rather than the wrong one (my way).
=DSum("[Invoice Amount]","Invoice Detail","[DCode] = '" & [Forms]![Invoice Detail]![Dr Code] & "' AND Year([Entry Month Year]) = " & Year(Date()))
Thanks again for your help.
BruceM
As with everybody at UA, glad to help. Good luck with the rest of the project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.