Help with DSum Function

[Joe Cilinceon]

I trying to get a total for a charge in a date span. Here is as close as
I've gotten:
DSum("[qryCharges]![ChgAmt]","[qryCharges]","[qryCharges]![ChgID]= 1" &
"[qryCharges]![PaymentDate] = Between " & #11/1/2005# & " And #" & Date() &
"#")

ChgAmt is currency
ChgID is the item number
PaymentDate is from 1st to current date

Please I need some help with the syntax.

 

[Douglas J. Steele]

DSum("[qryCharges]![ChgAmt]","[qryCharges]", _
"[qryCharges]![ChgID]= 1" & _
"[qryCharges]![PaymentDate] = Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))

 

[Tom Lake]

DSum("[qryCharges]![ChgAmt]","[qryCharges]", _
"[qryCharges]![ChgID]= 1" & _
"[qryCharges]![PaymentDate] = Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))

You don't need an And in there?

DSum("[qryCharges]![ChgAmt]","[qryCharges]", _
"[qryCharges]![ChgID]= 1 And " & _
"[qryCharges]![PaymentDate] = Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))

Tom Lake

 

[Douglas J. Steele]

DSum("[qryCharges]![ChgAmt]","[qryCharges]", _
"[qryCharges]![ChgID]= 1" & _
"[qryCharges]![PaymentDate] = Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))

You don't need an And in there?

DSum("[qryCharges]![ChgAmt]","[qryCharges]", _
"[qryCharges]![ChgID]= 1 And " & _
"[qryCharges]![PaymentDate] = Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))

Quite right. I was concentrating on the dates, and missed that.

Thanks for the assist, Tom.

 

[Joe Cilinceon]

Thanks Tom and Douglas it is much appreacaited.

DSum("[qryCharges]![ChgAmt]","[qryCharges]", _
"[qryCharges]![ChgID]= 1" & _
"[qryCharges]![PaymentDate] = Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))

You don't need an And in there?

DSum("[qryCharges]![ChgAmt]","[qryCharges]", _
"[qryCharges]![ChgID]= 1 And " & _
"[qryCharges]![PaymentDate] = Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))

Quite right. I was concentrating on the dates, and missed that.

Thanks for the assist, Tom.

 

[Guest]

Hi Douglas

You are missing an "And" after the first criteria, and there is no need for
the equal mark before the between

DSum("[qryCharges]![ChgAmt]","[qryCharges]", _
"[qryCharges]![ChgID]= 1 AND " & _
"[qryCharges]![PaymentDate] Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))

DSum("[qryCharges]![ChgAmt]","[qryCharges]", _
"[qryCharges]![ChgID]= 1" & _
"[qryCharges]![PaymentDate] = Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I trying to get a total for a charge in a date span. Here is as close as
I've gotten:
DSum("[qryCharges]![ChgAmt]","[qryCharges]","[qryCharges]![ChgID]= 1" &
"[qryCharges]![PaymentDate] = Between " & #11/1/2005# & " And #" & Date()
& "#")

ChgAmt is currency
ChgID is the item number
PaymentDate is from 1st to current date

Please I need some help with the syntax.

 

[Douglas J. Steele]

Good Lord: it's taken 3 of us to answer the one question!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas

You are missing an "And" after the first criteria, and there is no need
for
the equal mark before the between

DSum("[qryCharges]![ChgAmt]","[qryCharges]", _
"[qryCharges]![ChgID]= 1 AND " & _
"[qryCharges]![PaymentDate] Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))

DSum("[qryCharges]![ChgAmt]","[qryCharges]", _
"[qryCharges]![ChgID]= 1" & _
"[qryCharges]![PaymentDate] = Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I trying to get a total for a charge in a date span. Here is as close as
I've gotten:
DSum("[qryCharges]![ChgAmt]","[qryCharges]","[qryCharges]![ChgID]= 1" &
"[qryCharges]![PaymentDate] = Between " & #11/1/2005# & " And #" &
Date()
& "#")

ChgAmt is currency
ChgID is the item number
PaymentDate is from 1st to current date

Please I need some help with the syntax.

 

[Joe Cilinceon]

lol no wonder I don't get many answers to my questions. Thanks again I think
I have it now.

Good Lord: it's taken 3 of us to answer the one question!

Hi Douglas

You are missing an "And" after the first criteria, and there is no
need for
the equal mark before the between

DSum("[qryCharges]![ChgAmt]","[qryCharges]", _
"[qryCharges]![ChgID]= 1 AND " & _
"[qryCharges]![PaymentDate] Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))

DSum("[qryCharges]![ChgAmt]","[qryCharges]", _
"[qryCharges]![ChgID]= 1" & _
"[qryCharges]![PaymentDate] = Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I trying to get a total for a charge in a date span. Here is as
close as I've gotten:
DSum("[qryCharges]![ChgAmt]","[qryCharges]","[qryCharges]![ChgID]=
1" & "[qryCharges]![PaymentDate] = Between " & #11/1/2005# & " And
#" & Date()
& "#")

ChgAmt is currency
ChgID is the item number
PaymentDate is from 1st to current date

Please I need some help with the syntax.

 

[Guest]

Try this, there are some changes
1. Add the word AND after the first criteria
2. Put the first date in the string
3. Remove the equal sign before the between

DSum("[qryCharges]![ChgAmt]","[qryCharges]","[qryCharges]![ChgID]= 1 AND
[qryCharges]![PaymentDate] Between #11/1/2005# And #" & Date() & "#")

 

[Joe Cilinceon]

Try this, there are some changes
1. Add the word AND after the first criteria
2. Put the first date in the string
3. Remove the equal sign before the between

DSum("[qryCharges]![ChgAmt]","[qryCharges]","[qryCharges]![ChgID]= 1
AND [qryCharges]![PaymentDate] Between #11/1/2005# And #" & Date() &
"#")

Ok all so far have returned and Error.

 

[Douglas J. Steele]

Given how many different versions have been suggested to you, it might be a
good idea to post what you're actually trying to use!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try this, there are some changes
1. Add the word AND after the first criteria
2. Put the first date in the string
3. Remove the equal sign before the between

DSum("[qryCharges]![ChgAmt]","[qryCharges]","[qryCharges]![ChgID]= 1
AND [qryCharges]![PaymentDate] Between #11/1/2005# And #" & Date() &
"#")

Ok all so far have returned and Error.

 

[Tom Lake]

lol no wonder I don't get many answers to my questions. Thanks again I
think I have it now.

Yeah, but do WE have it? 8^)

Tom Lake

 

[Joe Cilinceon]

Ok, let me try it this way. I have a the following:

LEDGER (table)
Transaction (number that links this table to the charges)
PaymentDate (date fields)

CHARGES (table)
Transaction (links to LEDGER to get the dates needed)
ChgID
ChgAmt

Now I want a total amount for all ChgID = 1 (Rent is what 1 is) from 11/1/05
to today).

Now I need the proper syntax for the criteria section of the DSum.

Given how many different versions have been suggested to you, it
might be a good idea to post what you're actually trying to use!

Try this, there are some changes
1. Add the word AND after the first criteria
2. Put the first date in the string
3. Remove the equal sign before the between

DSum("[qryCharges]![ChgAmt]","[qryCharges]","[qryCharges]![ChgID]= 1
AND [qryCharges]![PaymentDate] Between #11/1/2005# And #" & Date()
& "#")

Ok all so far have returned and Error.

 

[Douglas J. Steele]

I'm assuming that qryCharges is a query that links the two tables together,
and that it works properly (especially that all of the field names it
returns are what you expect, as opposed to Expr1, Expr2)

DSum("[ChgAmt]","[qryCharges]", _
"[ChgID]= 1 " & _
"AND [PaymentDate] " & _
"Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))

You do not want [qryCharges]![ChgAmt] as the first argument: you want
strictly the name of the field.

You do not want [qryCharges]![ChgID] or [qryCharges]![PaymentDate] in the
third argument: you want strictly the names of the fields.

If this still doesn't work, please post the actual code you're using. As
well, post the SQL of qryCharges, just in case.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, let me try it this way. I have a the following:

LEDGER (table)
Transaction (number that links this table to the charges)
PaymentDate (date fields)

CHARGES (table)
Transaction (links to LEDGER to get the dates needed)
ChgID
ChgAmt

Now I want a total amount for all ChgID = 1 (Rent is what 1 is) from
11/1/05 to today).

Now I need the proper syntax for the criteria section of the DSum.

Given how many different versions have been suggested to you, it
might be a good idea to post what you're actually trying to use!

Ofer wrote:
Try this, there are some changes
1. Add the word AND after the first criteria
2. Put the first date in the string
3. Remove the equal sign before the between

DSum("[qryCharges]![ChgAmt]","[qryCharges]","[qryCharges]![ChgID]= 1
AND [qryCharges]![PaymentDate] Between #11/1/2005# And #" & Date()
& "#")

Ok all so far have returned and Error.

 

[Joe Cilinceon]

Thank you Douglas that worked perfectly. Now I have another question for you
about the format function. What is the \/ designating?

I'm assuming that qryCharges is a query that links the two tables
together, and that it works properly (especially that all of the
field names it returns are what you expect, as opposed to Expr1,
Expr2)
DSum("[ChgAmt]","[qryCharges]", _
"[ChgID]= 1 " & _
"AND [PaymentDate] " & _
"Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))

You do not want [qryCharges]![ChgAmt] as the first argument: you want
strictly the name of the field.

You do not want [qryCharges]![ChgID] or [qryCharges]![PaymentDate] in
the third argument: you want strictly the names of the fields.

If this still doesn't work, please post the actual code you're using.
As well, post the SQL of qryCharges, just in case.

Ok, let me try it this way. I have a the following:

LEDGER (table)
Transaction (number that links this table to the charges)
PaymentDate (date fields)

CHARGES (table)
Transaction (links to LEDGER to get the dates needed)
ChgID
ChgAmt

Now I want a total amount for all ChgID = 1 (Rent is what 1 is) from
11/1/05 to today).

Now I need the proper syntax for the criteria section of the DSum.

Given how many different versions have been suggested to you, it
might be a good idea to post what you're actually trying to use!


Ofer wrote:
Try this, there are some changes
1. Add the word AND after the first criteria
2. Put the first date in the string
3. Remove the equal sign before the between

DSum("[qryCharges]![ChgAmt]","[qryCharges]","[qryCharges]![ChgID]=
1 AND [qryCharges]![PaymentDate] Between #11/1/2005# And #" &
Date() & "#")

Ok all so far have returned and Error.

 

[Douglas J. Steele]

\ is an escape character: it means that the next character will be used as
is.

The reason for using it when formatting dates is that if you use mm/dd/yyyy,
the / will be replaced by whatever has been designated as the date separator
in the Regional Settings. Some people use . or - as the separator, and that
can sometimes lead to problems in SQL statements. Using \/ guarantees that
forward slashes will be used, which are always going to work properly.
Similarly, simply using # without the \ won't work, since # has a specific
meaning in formats.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you Douglas that worked perfectly. Now I have another question for
you about the format function. What is the \/ designating?

I'm assuming that qryCharges is a query that links the two tables
together, and that it works properly (especially that all of the
field names it returns are what you expect, as opposed to Expr1,
Expr2)
DSum("[ChgAmt]","[qryCharges]", _
"[ChgID]= 1 " & _
"AND [PaymentDate] " & _
"Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))

You do not want [qryCharges]![ChgAmt] as the first argument: you want
strictly the name of the field.

You do not want [qryCharges]![ChgID] or [qryCharges]![PaymentDate] in
the third argument: you want strictly the names of the fields.

If this still doesn't work, please post the actual code you're using.
As well, post the SQL of qryCharges, just in case.

Ok, let me try it this way. I have a the following:

LEDGER (table)
Transaction (number that links this table to the charges)
PaymentDate (date fields)

CHARGES (table)
Transaction (links to LEDGER to get the dates needed)
ChgID
ChgAmt

Now I want a total amount for all ChgID = 1 (Rent is what 1 is) from
11/1/05 to today).

Now I need the proper syntax for the criteria section of the DSum.

Douglas J. Steele wrote:
Given how many different versions have been suggested to you, it
might be a good idea to post what you're actually trying to use!


Ofer wrote:
Try this, there are some changes
1. Add the word AND after the first criteria
2. Put the first date in the string
3. Remove the equal sign before the between

DSum("[qryCharges]![ChgAmt]","[qryCharges]","[qryCharges]![ChgID]=
1 AND [qryCharges]![PaymentDate] Between #11/1/2005# And #" &
Date() & "#")

Ok all so far have returned and Error.

 

[Joe Cilinceon]

Thanks for the answer Douglas.

\ is an escape character: it means that the next character will be
used as is.

The reason for using it when formatting dates is that if you use
mm/dd/yyyy, the / will be replaced by whatever has been designated as
the date separator in the Regional Settings. Some people use . or -
as the separator, and that can sometimes lead to problems in SQL
statements. Using \/ guarantees that forward slashes will be used,
which are always going to work properly. Similarly, simply using #
without the \ won't work, since # has a specific meaning in formats.

Thank you Douglas that worked perfectly. Now I have another question
for you about the format function. What is the \/ designating?

I'm assuming that qryCharges is a query that links the two tables
together, and that it works properly (especially that all of the
field names it returns are what you expect, as opposed to Expr1,
Expr2)
DSum("[ChgAmt]","[qryCharges]", _
"[ChgID]= 1 " & _
"AND [PaymentDate] " & _
"Between #11/1/2005# And " & _
Format(Date(), "\#mm\/dd\/yyyy\#"))

You do not want [qryCharges]![ChgAmt] as the first argument: you
want strictly the name of the field.

You do not want [qryCharges]![ChgID] or [qryCharges]![PaymentDate]
in the third argument: you want strictly the names of the fields.

If this still doesn't work, please post the actual code you're
using. As well, post the SQL of qryCharges, just in case.


Ok, let me try it this way. I have a the following:

LEDGER (table)
Transaction (number that links this table to the charges)
PaymentDate (date fields)

CHARGES (table)
Transaction (links to LEDGER to get the dates needed)
ChgID
ChgAmt

Now I want a total amount for all ChgID = 1 (Rent is what 1 is)
from 11/1/05 to today).

Now I need the proper syntax for the criteria section of the DSum.

Douglas J. Steele wrote:
Given how many different versions have been suggested to you, it
might be a good idea to post what you're actually trying to use!


Ofer wrote:
Try this, there are some changes
1. Add the word AND after the first criteria
2. Put the first date in the string
3. Remove the equal sign before the between

DSum("[qryCharges]![ChgAmt]","[qryCharges]","[qryCharges]![ChgID]=
1 AND [qryCharges]![PaymentDate] Between #11/1/2005# And #" &
Date() & "#")

Ok all so far have returned and Error.