DSUM syntax on Form

  • Thread starter Thread starter connie
  • Start date Start date
C

connie

I am using Access 2000. I have a field on a form that
needs to add two dollar amounts from two different
tables. It works fine if if I just do the calculation on
the one field in the one table but when I add the syntaxt
to add the other field from the other table I get an error
about invalid syntax.

This one works:
=Format(Nz(DSum("[amountTendered]-
[change]","orders","[CustomerID]=" &
[customerid]),0),"Fixed")

This one doesnt:
=Format(Nz(DSum("[amountTendered]-
[change]","orders","[Customer ID]=" &
[customerid]),0),"Fixed")+(Nz(DSum
("PaymentAmount","Charges","[CustomerId]=" &
[customerid]),0),"Fixed")
 
You have too many arguments for the Format function. Try this:

=Format(Nz(DSum("[amountTendered]-
[change]","orders","[Customer ID]=" &
[customerid]),0)+(Nz(DSum
("PaymentAmount","Charges","[CustomerId]=" &
[customerid]),0),"Fixed")
 
Thank you for your response. When I use this format I
still get an error: The expression you entered is
missing a closing parenthesis.

-----Original Message-----
You have too many arguments for the Format function. Try this:

=Format(Nz(DSum("[amountTendered]-
[change]","orders","[Customer ID]=" &
[customerid]),0)+(Nz(DSum
("PaymentAmount","Charges","[CustomerId]=" &
[customerid]),0),"Fixed")

--

Ken Snell
<MS ACCESS MVP>

I am using Access 2000. I have a field on a form that
needs to add two dollar amounts from two different
tables. It works fine if if I just do the calculation on
the one field in the one table but when I add the syntaxt
to add the other field from the other table I get an error
about invalid syntax.

This one works:
=Format(Nz(DSum("[amountTendered]-
[change]","orders","[CustomerID]=" &
[customerid]),0),"Fixed")

This one doesnt:
=Format(Nz(DSum("[amountTendered]-
[change]","orders","[Customer ID]=" &
[customerid]),0),"Fixed")+(Nz(DSum
("PaymentAmount","Charges","[CustomerId]=" &
[customerid]),0),"Fixed")
Click to expand...


.
Click to expand...
 
Sorry... forgot one of the inner parentheses:

=Format(Nz(DSum("[amountTendered]-[change]","orders","[Customer ID]=" &
[customerid]),0)+Nz(DSum("PaymentAmount","Charges","[CustomerId]=" &
[customerid]),0),"Fixed")


--

Ken Snell
<MS ACCESS MVP>

connie said:
Thank you for your response. When I use this format I
still get an error: The expression you entered is
missing a closing parenthesis.

-----Original Message-----
You have too many arguments for the Format function. Try this:

=Format(Nz(DSum("[amountTendered]-
[change]","orders","[Customer ID]=" &
[customerid]),0)+(Nz(DSum
("PaymentAmount","Charges","[CustomerId]=" &
[customerid]),0),"Fixed")

--

Ken Snell
<MS ACCESS MVP>

I am using Access 2000. I have a field on a form that
needs to add two dollar amounts from two different
tables. It works fine if if I just do the calculation on
the one field in the one table but when I add the syntaxt
to add the other field from the other table I get an error
about invalid syntax.

This one works:
=Format(Nz(DSum("[amountTendered]-
[change]","orders","[CustomerID]=" &
[customerid]),0),"Fixed")

This one doesnt:
=Format(Nz(DSum("[amountTendered]-
[change]","orders","[Customer ID]=" &
[customerid]),0),"Fixed")+(Nz(DSum
("PaymentAmount","Charges","[CustomerId]=" &
[customerid]),0),"Fixed")
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top