DSum when RecordDate is a text field

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?
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
if you want to sum for the year

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
You would use:

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

/gustav
SteveL13Author 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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
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
SteveL13Author Commented:
You are correct.  I do need to start over.  I am going to keep both suggestions in file however.
Gustav BrockCIOCommented:
You only need mine. The other won't work.

/gustav
Rey Obrero (Capricorn1)Commented:
<You only need mine. The other won't work.>

:-0
Gustav BrockCIOCommented:
Sorry. Had something else in mind.

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.