Sum of numbers in text field using DSUM function

Greetings,
I am using a DSUM function:

DSum("NUMCHILD", "tOfficer", "DOD between #" & Format([StarteDt], "Short Date") & "# and #" & Format([EndDt], "Short Date") & "#" & Val("NUMCHILD") > 0)

my problem is NUMCHILD is in a text field, and it has other values.
What I want it to do is Add all of the NUMCHILD values for any values that are numbers.
Ie.

NUMCHILD
-------
3
4
2
"" (is null or blank)
5
Unk
After 3pm

and the response would be 3 + 4 + 2 + 5 = 14.

Normally in a query I would just do a where Val(NUMCHILD) > 0 and it works but the parameters necessary prohibit (I am able to be told I'm wrong).

Help?
thanks.
LVL 9
Evan CutlerVolunteer Chief Information OfficerAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Try this (noting that the date criteria you have posted is incomplete):


DSum("NUMCHILD", "table1", "IsNumeric(NUMCHILD) = True AND DOB between #" & Format([StarteDt], "Short Date")

Open in new window

0
 
mbizupCommented:
Looks like you edited your post a bit since I first looked at it.

Try this:

DSum("NUMCHILD", "tOfficer", "DOD between #" & Format([StarteDt], "Short Date") & "# and #" & Format([EndDt], "Short Date") & "#  AND Val([NUMCHILD]) > 0")

Open in new window


Or this:

DSum("NUMCHILD", "tOfficer", "DOD between #" & Format([StarteDt], "Short Date") & "# and #" & Format([EndDt], "Short Date") & "#  AND IsNumeric ([NUMCHILD]) = True")

Open in new window

0
 
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
Thanks...
That did it...
the final query was:
DSum("NUMCHILD", "table1", "IsNumeric(NUMCHILD) = True AND DOB between #" & Format([StarteDt], "Short Date") & "# and #" & Format([EndDt], "Short Date") & "#")
0
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.

All Courses

From novice to tech pro — start learning today.