Solved DCount and Like (2 Viewers)

[JithuAccess]

Hi,

This is my code to find the total number of Denials in an Application and I want to use Like

=DCount("[strID]","[tblMaster Table]","[datDate Application Received] between DateSerial(Year(Date())-1,4,1) And DateSerial(Year(Date())+0,3,31) And [strStatus of Application] like "*" &  'Denied' & "*"")

But I am getting "#Type!" Error message. Could you please let me know what I have done wrong?

Thanks

 

[theDBguy]

Try

... Like '*denied*'")

 

[JithuAccess]

Try

... Like'*denied*'")

I tried it worked

Thank You

 

[GPGeorge]

What happens if you change the concatenation?
=DCount("[strID]","[tblMaster Table]","[datDate Application Received] between DateSerial(Year(Date())-1,4,1)
And DateSerial(Year(Date())+0,3,31)
And [strStatus of Application] like '*Denied*'")

====
too slow....

 

[SHANEMAC51]

But I am getting "#Type!" Error message. Could you please let me know what I have done wrong?

try this, splitting into substrings for clarity, for the forum

=DCount("[strID]",
"[tblMaster Table]",
"[datDate Application Received]
 between DateSerial(Year(Date())-1,4,1)
 And DateSerial(Year(Date())+0,3,31)
 And [strStatus of Application] like '*Denied'*")

 

[theDBguy]

I tried it worked

Thank You

Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

You should assign a group to your Status codes. That way you can pick by the group and not have to use like. Line is extremely inefficient since it almost always forces a full table scan. So, the larger your table, the slower the query as it reads RBAR (row by agonizing row) instead of using indexes to whittle down the selected recordset.