Solved

Type Mismatch Error using DSum() in MS Access Query

Posted on 2015-02-23
6
672 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Familiarize people with the process of utilizing SQL Server views 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 Access…
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…

820 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