Criteria causes query to hang

[Les Isaacs]

Hi All

This works fine, and take about 1 second to run:

SELECT [qry x all staff].staff_name, DCount("[employee_submissions]![submission_serial]","[employee_submissions]","[employee_submissions]![employee] = """ & [staff_name] & """ and [employee_submissions]![submission_type] = ""NHSP SD55(T)""") AS [NoSD55(T)sSubmitted]
FROM [qry x all staff];

... but, as soon as I add a criteria to the DCount expression, the query won't run (or not in less than 2-3 minutes anyway). The query with the crieria is:

SELECT [qry x all staff].staff_name, DCount("[employee_submissions]![submission_serial]","[employee_submissions]","[employee_submissions]![employee] = """ & [staff_name] & """ and [employee_submissions]![submission_type] = ""NHSP SD55(T)""") AS [NoSD55(T)sSubmitted]
FROM [qry x all staff]
WHERE (((DCount("[employee_submissions]![submission_serial]","[employee_submissions]","[employee_submissions]![employee] = """ & [staff_name] & """ and [employee_submissions]![submission_type] = ""NHSP SD55(T)"""))>0));

If anyone has any ideas I'd be EXTREMELY grateful
Thanks in advance
Les

 

[spikepl]

I would recommend a systematic approach to debugging rather than panic.

1. Right now you seem not even sure whether the query runs or not -did you cause the system to not provide any error messages?

2. When you concoct something complex, then a good idea is to verify that each component runs OK prior to inserting it into something more complex. Make a button on your form, and make it run MsgBox Dcount (blabla) .Your syntax seems off - lookup DCOUNt in the help file (and check examples there and in google) In fact the help file is always the first place to look when in trouble with some function. Select the function and press F1.

3. USing domainfunctions like Dcount is very expensive performance-wise. I already mentiioned to you in your other post that you could use the SQL condition EXISTS and a subquery for this. Further, you should put INDEX on all the fields you use as criteria - that would speed things up immensely.

 

[Les Isaacs]

Hi Spikepl

Thanks for your reply

I have to say though that I didn't really undertsand some of it:

1. I'm not panicking!
2. I'm not sure why you say that I "seem not even sure whether the query runs or not": it always runs, perfectly, without the criteria, and it always hangs if any criteria is added;
3. I know that the DCount function is OK - because it works perfectly when there is no criteria. The only difference between the working query and the hanging query is the addition of ">0" as the criteria for the DCount expression. Why do you think the "syntax seems off"

That all said, I am very grateful to you for encouraging me towards the EXISTS/subquery method, as this was an area in which I had very little knowledge but - following your repeated suggestion(!) I have now successfully used .

It took me a while, but the site at http://msdn.microsoft.com/en-us/library/aa217680(v=office.11).aspx was excellent - so thanks also to Mike Gunderloy.

So many thanks - although I didn't quite get some of your comments, your encouragement to learn the EXISTS/subquery method was obviously spot on and and am really pleased to have used it successfully - all on my own

Les

 

[spikepl]

Good on yer mate

I hope you did put index on the criteria fields.