need help with DSUM

[Gator]

I getting invalid syntax with DSUM.

I trying to pull an amount from a atable where criteria is the year in
txtDate AND the account number is in txtAccount.

Both txtDate and txtAccount are on the same form.

Here is my DSUM...

=DSum("Amount","Deposits","[AccountColumn]="[Me!txtAccount] AND
"[DateColumn]="(Year[Me!txtDate])

 

[Douglas J. Steele]

Your Where condition is incorrect.

From the fact that you've got the equal sign there, I'm assuming that you're
trying to set the ControlSource for a text box. When doing that, you must
leave out the "Me" keyword. As well, the values need to be put outside of
the quotes.

Assuming AccountColumn is a numeric field, try:

=DSum("Amount", "Deposits", "[AccountColumn] = " & txtAccount & " AND
DateColumn = " & Year(txtDate))

If AccountColumn is a text field, try:

=DSum("Amount", "Deposits", "[AccountColumn] = '" & txtAccount & "' AND
DateColumn = " & Year(txtDate))

In case it's not apparent, the difference between the two is that the second
includes single quotes as well. Exagerated for clarity, it's

=DSum("Amount", "Deposits", "[AccountColumn] = ' " & txtAccount & " ' AND
DateColumn = " & Year(txtDate))

 

[Gator]

Doug
I don't get a error now but the textbox where the DSUM is stays blank. Have
I left something out?
Date is formatted as Date/Time in the table and form control.
Account is format Number.

=DSum("Amount","Deposits","[AccountColumn]= " & [txtAccount] & " AND
[DateColumn] = " & Year([txtDate]))

thanks

Your Where condition is incorrect.

From the fact that you've got the equal sign there, I'm assuming that you're
trying to set the ControlSource for a text box. When doing that, you must
leave out the "Me" keyword. As well, the values need to be put outside of
the quotes.

Assuming AccountColumn is a numeric field, try:

=DSum("Amount", "Deposits", "[AccountColumn] = " & txtAccount & " AND
DateColumn = " & Year(txtDate))

If AccountColumn is a text field, try:

=DSum("Amount", "Deposits", "[AccountColumn] = '" & txtAccount & "' AND
DateColumn = " & Year(txtDate))

In case it's not apparent, the difference between the two is that the second
includes single quotes as well. Exagerated for clarity, it's

=DSum("Amount", "Deposits", "[AccountColumn] = ' " & txtAccount & " ' AND
DateColumn = " & Year(txtDate))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I getting invalid syntax with DSUM.

I trying to pull an amount from a atable where criteria is the year in
txtDate AND the account number is in txtAccount.

Both txtDate and txtAccount are on the same form.

Here is my DSUM...

=DSum("Amount","Deposits","[AccountColumn]="[Me!txtAccount] AND
"[DateColumn]="(Year[Me!txtDate])

 

[Gator]

I got it...I needed the Year([DateColumn]).......thanks

Your Where condition is incorrect.

From the fact that you've got the equal sign there, I'm assuming that you're
trying to set the ControlSource for a text box. When doing that, you must
leave out the "Me" keyword. As well, the values need to be put outside of
the quotes.

Assuming AccountColumn is a numeric field, try:

=DSum("Amount", "Deposits", "[AccountColumn] = " & txtAccount & " AND
DateColumn = " & Year(txtDate))

If AccountColumn is a text field, try:

=DSum("Amount", "Deposits", "[AccountColumn] = '" & txtAccount & "' AND
DateColumn = " & Year(txtDate))

In case it's not apparent, the difference between the two is that the second
includes single quotes as well. Exagerated for clarity, it's

=DSum("Amount", "Deposits", "[AccountColumn] = ' " & txtAccount & " ' AND
DateColumn = " & Year(txtDate))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I getting invalid syntax with DSUM.

I trying to pull an amount from a atable where criteria is the year in
txtDate AND the account number is in txtAccount.

Both txtDate and txtAccount are on the same form.

Here is my DSUM...

=DSum("Amount","Deposits","[AccountColumn]="[Me!txtAccount] AND
"[DateColumn]="(Year[Me!txtDate])