Syntax Error on Dsum Function with date range

[hksahoo]

I am getting the syntax error in the following expression :

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

Please help.

 

[Gasman]

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]

I just ran a mockup based on one of my tables. This syntax works for me.

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:

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]

=

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]

Access uses US date format MM/DD/YYYY

 

[arnelgp]

use either ISO date (#yyyy-mm-dd#) or
US-english date (#mm/dd/yyyy#)

 

[hksahoo]

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]

Since when has there been 31 months in a year???

 

[arnelgp]

your double-quote character is unusual:

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

 

[hksahoo]

-

Tried this too but getting results as "Error"

 

[arnelgp]

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]

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

 

[Gasman]

Crossposted https://www.accessforums.net/showthread.php?t=84559

 

[hksahoo]

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]

Those are NOT the quotes you used initially