DSUM with Between And operator?

[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.

 

[Gerald Stanley]

Try

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

Hope This Helps
Gerald Stanley MCSD

 

[Douglas J. Steele]

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)

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.
.

 

[Gerald Stanley]

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)

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.
.

.

 

[Barbara]

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

(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.
.

.

.