Dsum with Dates (1 Viewer)

Tophan

Registered User.
Local time
Today, 16:20
Joined
Mar 27, 2011
Messages
362
Good morning,

I am trying to sum the amount after December 31, 2019 and a date selected on a form. I am not getting an error message, the result is just blank.

Code:
=DSum("[Amount]","qryStatement2","[TransactionDate] > #31-12-19# and [TransactionDate]<#" & [txtLastStatement] & "# AND [AccountName]='" & [AccountName] & "'")

Where am I going wrong?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:20
Joined
Feb 19, 2013
Messages
16,553
for sql, date formats need to be in the US format of mm/dd/yyyy or the generic yyyy-mm-dd. You also need to specify a full year

try

[TransactionDate] > #2019-12-31# and [TransactionDate]<#" & format([txtLastStatement],"yyyy-mm-dd") & "# AND [AccountName]='" & [AccountName] & "'"
 

oleronesoftwares

Passionate Learner
Local time
Today, 13:20
Joined
Sep 22, 2014
Messages
1,159
I am trying to sum the amount after December 31, 2019 and a date selected on a form. I am not getting an error message, the result is just blank.
Can you give sample data
 

Tophan

Registered User.
Local time
Today, 16:20
Joined
Mar 27, 2011
Messages
362
for sql, date formats need to be in the US format of mm/dd/yyyy or the generic yyyy-mm-dd. You also need to specify a full year

try

[TransactionDate] > #2019-12-31# and [TransactionDate]<#" & format([txtLastStatement],"yyyy-mm-dd") & "# AND [AccountName]='" & [AccountName] & "'"
Hi,

The result is still blank :(
 

Tophan

Registered User.
Local time
Today, 16:20
Joined
Mar 27, 2011
Messages
362
I realised I made an error by referencing the wrong query. I have corrected that and now instead of no result I am getting #Error. Below is the formula with the correct query name.

Code:
=DSum("[Amount]","qryAccountLedger2","[TransactionDate] > #2019-12-31# and [TransactionDate]<#" & format([txtLastStatement], "yyyy-mm-dd") & "# AND [AccountName]='" & [AccountName] & "'")
 

Tophan

Registered User.
Local time
Today, 16:20
Joined
Mar 27, 2011
Messages
362
Can you give sample data

Can you give sample data
I don't think I can. I'm just trying to find the total between the two dates. The information is coming from a query which uses a table called tblJournalEntries. The banking details changed on January 1, 2020 so rather than doing a new db I added the date criteria >2019-12-31.

I wonder if I add the >2019-12-31 to the actual query if that would make a difference and I can take that section out of the formula. Going to try.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:20
Joined
Sep 21, 2011
Messages
14,047
You have <# :(

Put the criteria into a string variable and debug.print that until you get it correct.
THEN use that in your DSum()
 

Tophan

Registered User.
Local time
Today, 16:20
Joined
Mar 27, 2011
Messages
362
I don't think I can. I'm just trying to find the total between the two dates. The information is coming from a query which uses a table called tblJournalEntries. The banking details changed on January 1, 2020 so rather than doing a new db I added the date criteria >2019-12-31.

I wonder if I add the >2019-12-31 to the actual query if that would make a difference and I can take that section out of the formula. Going to try.
That didn't work...back to blank result
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:20
Joined
Feb 19, 2013
Messages
16,553
what is the value of txtLastStatement you are using?
 

Tophan

Registered User.
Local time
Today, 16:20
Joined
Mar 27, 2011
Messages
362
what is the value of txtLastStatement you are using?
That is a date chosen on a form. The Dsum formula is then to find the total of [Account] after December 31, 2019 and [txtLastStatement]. Once the info is input in the form, there is a button which would then open the report.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:20
Joined
Feb 19, 2002
Messages
42,974
1. You probably don't want to use hard-coded dates in the query. Use two fields on the form, You can "calculate" the start date if that is possible or you can fill it in using code when the form opens and let the user override it.
2. You definitely don't want to use domain functions inside queries (or VBA loops). Each domain function runs a separate query so they get very expensive very fast.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:20
Joined
Feb 19, 2013
Messages
16,553
That is a date chosen on a form
I realise that - but what date is it that you are using that doesn't return anything? Perhaps there are no amounts for the account number you are choosing after 2019-12-31
 

Users who are viewing this thread

Top Bottom