• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1017
  • Last Modified:

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.
0
Evan Cutler
Asked:
Evan Cutler
  • 2
1 Solution
 
mbizupCommented:
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 CutlerAuthor 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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now