DSUM with Between And operator?

  • Thread starter Thread starter Barbara
  • Start date Start date
B

Barbara

Can I use the following in a DSUM function:

xx = DSum("[Amount]", "tblAllCalls", "[CallingNumber]
= '" & searchnumber & "'" AND startdate Between #" &
varbeg & "# And #" & varend & "#")

For some reason I'm getting a syntax error.
tia.
 
Try

xx = DSum("[Amount]", "tblAllCalls", "[CallingNumber]
= '" & searchnumber & "' AND startdate Between #" &
varbeg & "# And #" & varend & "#")

Hope This Helps
Gerald Stanley MCSD
 
Also, make sure that the dates are in mm/dd/yyyy, regardless of what your
locale settings are.

If there's a chance that the user's short date format might be dd/mm/yyyy,
change Gerald's suggestion to:

xx = DSum("[Amount]", "tblAllCalls", "[CallingNumber]
= '" & searchnumber & "' AND startdate Between #" &
Format(varbeg, "mm/dd/yyyy") & "# And #" & Format(varend, "mm/dd/yyyy") &
"#")


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Gerald Stanley said:
Try

xx = DSum("[Amount]", "tblAllCalls", "[CallingNumber]
= '" & searchnumber & "' AND startdate Between #" &
varbeg & "# And #" & varend & "#")

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Can I use the following in a DSUM function:

xx = DSum("[Amount]", "tblAllCalls", "[CallingNumber]
= '" & searchnumber & "'" AND startdate Between #" &
varbeg & "# And #" & varend & "#")

For some reason I'm getting a syntax error.
tia.
.
Click to expand...
Click to expand...
 
Doug

Good spot - I was preoccupied with the AND startdate being
outside of the quotes.

Gerald
-----Original Message-----
Also, make sure that the dates are in mm/dd/yyyy, regardless of what your
locale settings are.

If there's a chance that the user's short date format might be dd/mm/yyyy,
change Gerald's suggestion to:

xx = DSum("[Amount]", "tblAllCalls", "[CallingNumber]
= '" & searchnumber & "' AND startdate Between #" &
Format(varbeg, "mm/dd/yyyy") & "# And #" & Format(varend, "mm/dd/yyyy") &
"#")


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Gerald Stanley said:
Try

xx = DSum("[Amount]", "tblAllCalls", "[CallingNumber]
= '" & searchnumber & "' AND startdate Between #" &
varbeg & "# And #" & varend & "#")

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Can I use the following in a DSUM function:

xx = DSum("[Amount]", "tblAllCalls", "[CallingNumber]
= '" & searchnumber & "'" AND startdate Between #" &
varbeg & "# And #" & varend & "#")

For some reason I'm getting a syntax error.
tia.
.
Click to expand...
Click to expand...


.
Click to expand...
 
Thanks both of you, it works fine now!!!
-----Original Message-----
Doug

Good spot - I was preoccupied with the AND startdate being
outside of the quotes.

Gerald
-----Original Message-----
Also, make sure that the dates are in mm/dd/yyyy, regardless of what your
locale settings are.

If there's a chance that the user's short date format might be dd/mm/yyyy,
change Gerald's suggestion to:

xx = DSum("[Amount]", "tblAllCalls", "[CallingNumber]
= '" & searchnumber & "' AND startdate Between #" &
Format(varbeg, "mm/dd/yyyy") & "# And #" & Format
Click to expand...
(varend,
"mm/dd/yyyy") &
"#")


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Try

xx = DSum("[Amount]", "tblAllCalls", "[CallingNumber]
= '" & searchnumber & "' AND startdate Between #" &
varbeg & "# And #" & varend & "#")

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Can I use the following in a DSUM function:

xx = DSum("[Amount]", "tblAllCalls", "[CallingNumber]
= '" & searchnumber & "'" AND startdate Between #" &
varbeg & "# And #" & varend & "#")

For some reason I'm getting a syntax error.
tia.
.
Click to expand...


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