DSum function criteria using between

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 14 Dec 2005 22:16 GMT

Hello all,

I need to get a sum between a date range. I am using to varibles on date to
do this, but haven't found the correct syntax yet. What am I doing wrong?
vSDate and vEDate are my varibles.

vSQL = DSum("[qty_picked]", "VIAWARE_AFRM_TO_VIA_T", "[dtimemod] between
vSDate and vEDate")

Reply to this Message

Klatuu - 14 Dec 2005 22:42 GMT

It is not the between that is the problem, it is that the variables you are
referencing are inside the quotes. Try this:
vSQL = DSum("[qty_picked]", "VIAWARE_AFRM_TO_VIA_T", & _
"[dtimemod] between " & vSDate & " and " & vEDate & "")
The above syntax is if the variables vSDate and vEDate are date type
varialbes. If they are textbox values, then they have to be considered
strings and delimited with # so they will be seen as dates
vSQL = DSum("[qty_picked]", "VIAWARE_AFRM_TO_VIA_T", & _
"[dtimemod] between #" & vSDate & "# and #" & vEDate & "#")

> Hello all,
>
[quoted text clipped - 4 lines]
> vSQL = DSum("[qty_picked]", "VIAWARE_AFRM_TO_VIA_T", "[dtimemod] between
> vSDate and vEDate")

Reply to this Message

Tim Ferguson - 15 Dec 2005 18:09 GMT

> vSQL = DSum("[qty_picked]", "VIAWARE_AFRM_TO_VIA_T", & _
> "[dtimemod] between #" & vSDate & "# and #" & vEDate & "#")

This is, of course, not safe for the rest of the world that does not use
USAian dates.

Tim F

Reply to this Message