Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Type Mismatch Error using DSum() in MS Access Query

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

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 52

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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 52

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

782 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