DCount Crashes Access 2010 - worked fine in earlier versions

[sxschech]

Was upgraded to 2010 from 2007 over the weekend. Had a few problems getting things working, but have narrowed it down to this statement:

DCount("Term", "qryUGStatOverviewExcel_College")

When vba hits that item, access 2010 crashes. I also tried it in the immediate window as well as placing it inside a regular query and it crashes there too. The results of the query in question have only 16 rows. I tried the same dcount on other queries and a table that contains 1028 rows example: DCount("Term", "HASUG") and it worked fine without crashing.


The query was originally created and last modified in 2009. The field "Term" for the 16 rows all have values there are no blanks or nulls. Term contains six characters. The query is a union query and contains some functions such as Max(), Right() and Left(). It also grabs criteria from a form such as IIf(Right([forms]![frmMain].[txtCurrentTerm],2)="20","Spring ","Fall ") & Left([forms]![frmMain].[txtCurrentTerm],4) AS TermYear, [forms]![frmMain].[txtCurrentTerm] AS TERM,
...

The query itself runs without crashing. I also did the query equivalent of DCOUNT and this did not crash either:
SELECT Count(qryUGStatOverviewExcel_College.term) AS CountOfterm
FROM qryUGStatOverviewExcel_College;

Lastly I tried a dlookup on the above "new" count query and it said query was too complex so perhaps 2010 can't handle what 2007 had no trouble doing?

 

[oharab]

I've not used 2010 (or 2007 come to that!), but have a look at the modules in

http://access.mvps.org/access/modules/mdl0012.htm

They're dead simple, drop in replacements for Dlookup, DCount etc. that are much quicker and are more flexible than the built in functins.
Just import the files and do find & replace.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[sxschech]

Thanks Ben that seems to have worked. Will see next week when I do a full normal run.