Using DSum and Date Parameters in a Query Together

Posted on 2004-04-26
Last Modified: 2006-11-17
Good morning!

I have the following query that is subtracting two fields based on date criteria.  I am getting a data type mismatch error.  Can you please advise how to correct this error.

Subtraction: DSum("[Number of Personnel Assigned] - [Number of Personnel Attended]","Query By Month","[Date of Meeting] >='4/1/2004' and [Date of Meeting] <='4/30/2004'")

Thanks is advance for your assistance!

Question by:eaglecmg
  • 2
LVL 17

Accepted Solution

walterecook earned 250 total points
ID: 10919241
Try wrapping the dates in #

LVL 54

Assisted Solution

nico5038 earned 250 total points
ID: 10919388
Walter is "right on the money", but would like to add that using BETWEEN is faster, try:
"[Date of Meeting] BETWEEN #4/1/2004# AND #4/30/2004#"

Between dates are so-called "inclusive"

LVL 17

Expert Comment

ID: 10919401
well done Nico.
Nice pickup.

Author Comment

ID: 10919749
Thanks to both of you it worked great!   I appreciate the help!


Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question