Solved

Type Mismatch Error using DSum() in MS Access Query

Posted on 2015-02-23
6
698 Views
Last Modified: 2015-03-03
Hello everyone,

I have two queries in MS Access 2010.  The first is a crosstab query that sums the number of events for each month of the year. Here are the  fields I'm using with their expressions for the first query:

Row Headings:
ID: CLng(Format(DateSerial(Year(yearVariable),Month(monthVariable),15),'General Number'))
Years: Year(dateVariable)
Months: Month(dateVariable)

Column Heading:
Event: Text Field

Value:
CLng(Nz(Count(idFieldFromOtherTable,0))


The second query us a SELECT uses the values of the first query along with DSum() to produce a cumulative sum as time goes on.  Here are the fields and their expressions in query builder:

YearID: CLng(Format(DateSerial([Years],[Months],15),'General Number'))
AYear: ("Years" field from first query)
AMonth: ("Months" field from first query)
Event: DSum("Event","[QueryOne]","[ID] <= " & [YearID])


The first query runs fine, but when I run the second query, the fields that should hold the value shows "#Error."  If I click on one of those fields, I get a "Data Type Mismatch in Criteria Expression" error that loops infinitely.  Ctrl-Break does help break it either.  But the thing is, this query worked fine a week ago.  I have absolutely no idea what's wrong here.

ID and YearID are both of type Long Integer
Years and Months in Query One are of type integer

Some things I've tried:
- I called the DSUM() function in the debug window and manually input some field values and it worked fine
- Compared field types for both queries and nothing appears to be mismatched
- Compact/repair and decompile of the database
0
Comment
Question by:dsoderstrom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40627545
I guess you want Count, not Sum, as you can't sum text.
Also, there's no reason to use Format for obtaining a numeric value from a date which - by nature - is a number.
Thus:

Row Headings:
 ID: CDbl(DateSerial(Year(yearVariable),Month(monthVariable),15))
 Years: Year(dateVariable)
 Months: Month(dateVariable)

Value:
 CLng(Nz(Count(idFieldFromOtherTable),0))

 YearID: CDbl(DateSerial([Years],[Months],15))
 AYear: ("Years" field from first query)
 AMonth: ("Months" field from first query)
 Event: DCount("*","[QueryOne]","[ID] <= " & [YearID] & "")

/gustav
0
 

Author Comment

by:dsoderstrom
ID: 40628021
Gustav,

Thanks for the reply.  I mistyped in my description above.  "Event" is just the column heading and the values were actually of type long integer.

Either way, I tried what you suggested, but still no luck.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40628042
Ok, then perhaps it doesn't like the reuse of Event. Try:

AEvent: DSum("Event","[QueryOne]","[ID] <= " & [YearID] & "")

/gustav
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Accepted Solution

by:
dsoderstrom earned 0 total points
ID: 40628336
Thanks for taking the time to help out Gustav.  I figured it out though, but I still don't quite get how I got the error.  The problem was with the original table I used to count the number of "events" in each month, year.  Turns out I had a couple null values in the table QueryOne was pulleing from.  However, QueryOne read all those values and got rid of any null values with NZ(), so in my mind queryTwo shouldn't have been affected by the null values in the table.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40628354
OK, that explains.

/gustav
0
 

Author Closing Comment

by:dsoderstrom
ID: 40641484
Figured out the solution as stated in the answer
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

739 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