DCount with Calculated Field

[steve21nj]

Good evening,

I am working with DCount in a query based on a calculated value [MyCalValue] that is a status.

The status can be: Complete, On Going, Past Due, Additional Information, No Date.

In the DCount, I want to return 0 if null.

Sample that works fine

[FONT=Verdana][COLOR=black]Green: DCount("[[/COLOR][/FONT][FONT=Calibri][SIZE=3]MyCalValue[/SIZE][/FONT][COLOR=black][FONT=Verdana]]","Open Issues","[[/FONT][/COLOR][FONT=Calibri][SIZE=3]MyCalValue[/SIZE][/FONT][COLOR=black][FONT=Verdana]] = 'COMPLETE’")[/FONT][/COLOR]

But I want combine values in an OR statement but can’t get it to work.

[COLOR=black][FONT=Verdana]Green: DCount("[[/FONT][/COLOR][FONT=Calibri][SIZE=3]MyCalValue[/SIZE][/FONT][COLOR=black][FONT=Verdana]]","Open Issues","[[/FONT][/COLOR][FONT=Calibri][SIZE=3]MyCalValue[/SIZE][/FONT][COLOR=black][FONT=Verdana]] = 'COMPLETE’ OR ‘On Going’")[/FONT][/COLOR]

Also not working is when I’m trying to get a count based on the month like this:

[COLOR=black][FONT=Verdana]LastMonth: DCount(Month("[[/FONT][/COLOR][FONT=Calibri][SIZE=3]RequestDate[/SIZE][/FONT][COLOR=black][FONT=Verdana]]","Open Issues","[[/FONT][/COLOR][FONT=Calibri][SIZE=3]RequestDate[/SIZE][/FONT][COLOR=black][FONT=Verdana]] = fLastMonth(Now())",0) [/FONT][/COLOR]

Any suggestions?

 

[CJ_London]

almost right

Green: DCount("[MyCalValue]","Open Issues","[MyCalValue] = 'COMPLETE’ OR [MyCalValue] = ‘On Going’")

and try this

LastMonth: DCount("*","Open Issues","month([RequestDate]) = " & month(dateadd("m",-1,Date())))

 

[Galaxiom]

A better design would return MyCalValue as a number. It is faster to search for a number than a string.

It also appears that you are doing multiple DCounts on every record. This will become painfully slow as the number of records increases. Each DCount is like running another separate query for each record in the main query.

If that is what you are doing then the query should be restructured to use the database engine which has Count function included. It will require subqueries but it will potentially be several orders of magnitude faster.

Domain functions should only be included in the Select clause of a query as a last resort.

LastMonth: DCount("*","Open Issues","month([RequestDate]) = " & month(dateadd("m",-1,Date())))

This is not an efficient where clause. "Month([RequestDate])" requires a function to be applied to every record in the database. While this works fine with a small number of records, it will become increasingly slow as records accumulate.

Querying a date field should be done by converting the criteria into a date range and querying the field for that range. This means that the functions required are used just once and allows the engine to select the records based on the index.

It can easily be one hundred times faster.

 

[CJ_London]

@Galaxiom

It can easily be one hundred times faster.

Quite agree. I responded late at night to the OP's question. I was going to mention subqueries would be better but forgot

 

[steve21nj]

Could you provide an example of the sub queries so I may develop this in my database?

 

[CJ_London]

Here are a couple of links

http://msdn.microsoft.com/en-us/library/office/aa217680(v=office.11).aspx

http://allenbrowne.com/subquery-01.html

 

[steve21nj]

I'm not sure if I am over thinking this or what. But you want me to remove all the DCounts in my main query and make subqueries based on the DCounts?
Honestly I'm not really sure what you both mean by making it easier. I'm hoping to get one of those moments where it all clicks, but I'm not there yet.
The purpose of this is to have a dashboard that displays results based on the number of tickets for this month, last month, and the year. Followed up with the number of completed tickets. I am attaching a sample database, could you help create the first part so I can finish the remaining.

Also, I was having issues with the suggestion as it wouldn't work, but I know we are moving from Dcount. Just looking for insight.

Green: DCount("[MyCalValue]","Open Issues","[MyCalValue] = 'COMPLETE’ OR [MyCalValue] = ‘On Going’")

 

[CJ_London]

But you want me to remove all the DCounts in my main query and make subqueries based on the DCounts?

It is entirely up to you, we are just saying that using subqueries will result in a faster running query. If it is fast enough for you using dcount then don't change it.

With regards sub queries they are very useful things to learn how to use so if you want your eureka moment, visit the links I have provided or search for subqueries on the net and learn how to use them.

as it wouldn't work

Doesn't help me to help you at all. As it stands, it is perfectly valid code as displayed in the query builder so this implies there is something wrong with mycalvalues or open issues

With regards your attachment, sorry I do not have the time.