H hksahoo New member Local time Tomorrow, 04:20 Joined Sep 2, 2013 Messages 11 Oct 12, 2021 #1 I am getting the syntax error in the following expression : Dsum(“[Dep]”,”[ InterestTable]”,”[Tdate]>=#01/04/2021# and <=#31/03/2022#”) Please help.
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, 23:50 Joined Sep 21, 2011 Messages 17,036 Oct 12, 2021 #2 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
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 Today, 18:50 Joined Jan 23, 2006 Messages 15,566 Oct 12, 2021 #3 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.
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.
H hksahoo New member Local time Tomorrow, 04:20 Joined Sep 2, 2013 Messages 11 Oct 12, 2021 #4 hksahoo said: = Click to expand... 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
hksahoo said: = Click to expand... 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 Today, 18:50 Joined Jan 23, 2006 Messages 15,566 Oct 12, 2021 #5 Access uses US date format MM/DD/YYYY
arnelgp ..forever waiting... waiting for jellybean! Local time Tomorrow, 06:50 Joined May 7, 2009 Messages 20,346 Oct 12, 2021 #6 use either ISO date (#yyyy-mm-dd#) or US-english date (#mm/dd/yyyy#)
H hksahoo New member Local time Tomorrow, 04:20 Joined Sep 2, 2013 Messages 11 Oct 12, 2021 #7 jdraw said: DSum("Freight", "Orders", "ShippedDate between #8/10/94# and #9/10/94#") Click to expand... Sir, Tried both the codes as mentioned by you. There is no syntax error. But result showing "Error". Thanks and regards. hksahoo
jdraw said: DSum("Freight", "Orders", "ShippedDate between #8/10/94# and #9/10/94#") Click to expand... 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, 23:50 Joined Sep 21, 2011 Messages 17,036 Oct 12, 2021 #8 Since when has there been 31 months in a year???
arnelgp ..forever waiting... waiting for jellybean! Local time Tomorrow, 06:50 Joined May 7, 2009 Messages 20,346 Oct 12, 2021 #9 your double-quote character is unusual: Dsum("[Dep]","[ InterestTable]","[Tdate] Between #04/01/2021# and #03/31/2022#”)
your double-quote character is unusual: Dsum("[Dep]","[ InterestTable]","[Tdate] Between #04/01/2021# and #03/31/2022#”)
H hksahoo New member Local time Tomorrow, 04:20 Joined Sep 2, 2013 Messages 11 Oct 12, 2021 #10 arnelgp said: - Click to expand... Tried this too but getting results as "Error"
arnelgp ..forever waiting... waiting for jellybean! Local time Tomorrow, 06:50 Joined May 7, 2009 Messages 20,346 Oct 12, 2021 #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?
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, 23:50 Joined Sep 21, 2011 Messages 17,036 Oct 12, 2021 #12 arnelgp said: your double-quote character is unusual: Dsum("[Dep]","[ InterestTable]","[Tdate] Between #04/01/2021# and #03/31/2022#”) Click to expand... I reckon arnel has spotted your extra problem
arnelgp said: your double-quote character is unusual: Dsum("[Dep]","[ InterestTable]","[Tdate] Between #04/01/2021# and #03/31/2022#”) Click to expand... I reckon arnel has spotted your extra problem
Gasman Enthusiastic Amateur Local time Today, 23:50 Joined Sep 21, 2011 Messages 17,036 Oct 12, 2021 #13 Crossposted https://www.accessforums.net/showthread.php?t=84559
H hksahoo New member Local time Tomorrow, 04:20 Joined Sep 2, 2013 Messages 11 Oct 12, 2021 #14 arnelgp said: 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? Click to expand... 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.
arnelgp said: 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? Click to expand... 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, 23:50 Joined Sep 21, 2011 Messages 17,036 Oct 12, 2021 #15 Those are NOT the quotes you used initially