Solved

Type Mismatch Error using DSum() in MS Access Query

Posted on 2015-02-23
6
647 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
  • 3
  • 3
6 Comments
 
LVL 49

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 49

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 49

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now