We help IT Professionals succeed at work.

DSum when RecordDate is a text field

SteveL13
SteveL13 asked
on
I have a form and on the form I have a currency field that I am trying to set the default value for.


My default value code right now is:

=DSum("[Credit]","tblImportedRecords","[RecordDate] = #" & [Forms]![frmAnnualEarningsSummary]![txtEarningsYear] & "#")

But that isn't working because the field RecordDate in table tblImportedRecords is a date field.  For example the table may show 2/16/2015 but the form field txtEarningsYear is a text field that may show 2015 on the form.

How can I make the DSum value work?  What do I need to change the default value to to make it work?
Comment
Watch Question

Top Expert 2016
Commented:
if you want to sum for the year

=DSum("[Credit]","tblImportedRecords","Year([RecordDate]) = " &CInt([Forms]![frmAnnualEarningsSummary]![txtEarningsYear]))
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You would use:

=DSum("[Credit]","tblImportedRecords","Year(CDate([RecordDate])) = " & [Forms]![frmAnnualEarningsSummary]![txtEarningsYear] & "")

/gustav

Author

Commented:
Neither suggestion is working for me.  I've attached a downsized file.  If you open frmAnnualEarningsSummary the year 2015 should display $4,318 which is the sum of Credits in tblImportedRecords.

I'm sure I'm doing something wrong.
Test.accdb
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
1. You are inserting the DSum result as DefaultValue. Doesn't make any sense.
2. The field is bound to YTDearnings in the summary table, so no use for the DSum.
3. This field is Null for 2015, thus no value to display.
4. The sum for 2015 is 2855, not 4318.

Seems like you need to redesign this from the ground up.

/gustav

Author

Commented:
You are correct.  I do need to start over.  I am going to keep both suggestions in file however.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You only need mine. The other won't work.

/gustav
Top Expert 2016

Commented:
<You only need mine. The other won't work.>

:-0
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Sorry. Had something else in mind.

/gustav