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?
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]))
0

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
0
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

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

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

/gustav
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.