Syntax Error on Dsum Function with date range

hksahoo

New member
Local time
Today, 13:49
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.
 
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
 
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.
 
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
 
Access uses US date format MM/DD/YYYY
 
use either ISO date (#yyyy-mm-dd#) or
US-english date (#mm/dd/yyyy#)
 
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
 
Since when has there been 31 months in a year???😔
 
your double-quote character is unusual:

Dsum("[Dep]","[ InterestTable]","[Tdate] Between #04/01/2021# and #03/31/2022#”)
 
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?
 
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
 
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.
 
Those are NOT the quotes you used initially😔
 

Users who are viewing this thread

Back
Top Bottom