Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Type Mismatch Error using DSum() in MS Access Query

Posted on 2015-02-23
6
Medium Priority
?
758 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 51

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 51

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
How to Create Failover DNS Record Sets in Route 53

Route 53 has the ability to easily configure DNS record sets specifically for failover scenarios. These failover record sets can be configured to failover to full-blown deployments in other regions or to a static HTML page that informs your customers of the issue.

 

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 51

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

721 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