Dsum with Dates

[Tophan]

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.

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

Where am I going wrong?

 

[CJ_London]

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]

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]

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]

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.

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

 

[Tophan]

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]

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]

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]

what is the value of txtLastStatement you are using?

 

[Tophan]

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]

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]

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