Syntax Error on Dsum Function with date range (1 Viewer)

hksahoo

New member
Local time
Today, 05:13
Joined
Sep 2, 2013
Messages
11
I am getting the syntax error in the following expression :

Dsum(“[Dep]”,”[ InterestTable]”,”[Tdate]>=#01/04/2021# and <=#31/03/2022#”)

Please help.
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:43
Joined
Sep 21, 2011
Messages
10,863
Need mm/dd/yyyy or yyyy-mm-dd format
Plus you need to repeat TDate for the <=

I tend to use Between with that syntax
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 19:43
Joined
Jan 23, 2006
Messages
14,437
I just ran a mockup based on one of my tables. This syntax works for me.
Code:
Sub testoct12()
Dim xx As Single
xx = DSum("Freight", "Orders", "ShippedDate between #8/10/94# and  #9/10/94#")
Debug.Print xx
End Sub

This also works:
Code:
Sub testoct12()
Dim xx As Single
xx = DSum("Freight", "Orders", "ShippedDate >=#8/10/94# and  ShippedDate <=#9/10/94#")
Debug.Print xx
End Sub

NOTE: ShippedDate is repeated in the condition/criteria.
 

hksahoo

New member
Local time
Today, 05:13
Joined
Sep 2, 2013
Messages
11
Sir,
Date format dd/mm/yyyy
Tried
Dsum(“[Dep]”,”[ InterestTable]”,”[Tdate]>=#01/04/2021# and [Tdate]<=#31/03/2022#”) but getting same syntax error

I have also tried between.
Thanks and regards
hksahoo
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 19:43
Joined
Jan 23, 2006
Messages
14,437
Access uses US date format MM/DD/YYYY
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:43
Joined
May 7, 2009
Messages
16,867
use either ISO date (#yyyy-mm-dd#) or
US-english date (#mm/dd/yyyy#)
 

hksahoo

New member
Local time
Today, 05:13
Joined
Sep 2, 2013
Messages
11
DSum("Freight", "Orders", "ShippedDate between #8/10/94# and #9/10/94#")
Sir,
Tried both the codes as mentioned by you. There is no syntax error. But result showing "Error".
Thanks and regards.
hksahoo
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:43
Joined
Sep 21, 2011
Messages
10,863
Since when has there been 31 months in a year???😔
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:43
Joined
May 7, 2009
Messages
16,867
your double-quote character is unusual:

Dsum("[Dep]","[ InterestTable]","[Tdate] Between #04/01/2021# and #03/31/2022#”)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:43
Joined
May 7, 2009
Messages
16,867
then check your table, what is [Dep], is it numeric?
what about the table, the spelling is correct?
TDate is it date or does it exists?

i think there is a space after [ in InterestTable?
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:43
Joined
Sep 21, 2011
Messages
10,863
your double-quote character is unusual:

Dsum("[Dep]","[ InterestTable]","[Tdate] Between #04/01/2021# and #03/31/2022#”)
I reckon arnel has spotted your extra problem
 

hksahoo

New member
Local time
Today, 05:13
Joined
Sep 2, 2013
Messages
11
then check your table, what is [Dep], is it numeric?
what about the table, the spelling is correct?
TDate is it date or does it exists?

i think there is a space after [ in InterestTable?
Yes.. Dep is numeric field.
There is no space in InterestTable in the expression I used. While posting this thread I made a mistake.
Tdate is a date field. It exists in the table.
If I do not use "" double quote, it throws syntax error.
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:43
Joined
Sep 21, 2011
Messages
10,863
Those are NOT the quotes you used initially😔
 

Users who are viewing this thread

Top Bottom