Dcount with two conditions (1 Viewer)

Alhakeem1977

Registered User.
Local time
Today, 23:09
Joined
Jun 24, 2017
Messages
308
Hi All,

I have got 4 text boxes to count the following:

1. Today's items.

2. Today's & Current Items less than 60 days.

3. Items Greater than 60 days.

4. Items Greater than 90 days.


I need help to validate the above with below expression control source.

Dcount("[ID]","[Open Cases]","[Form] = 2 Or 3 And [Open Date] = Date()")

Thanks in advance!
 

Isaac

Lifelong Learner
Local time
Today, 13:09
Joined
Mar 14, 2017
Messages
8,738
what about

Dcount("[ID]","[Open Cases]","[Form] in (2,3) And [Open Date] = #" & Date() & "#")
 

Alhakeem1977

Registered User.
Local time
Today, 23:09
Joined
Jun 24, 2017
Messages
308
what about

Dcount("[ID]","[Open Cases]","[Form] in (2,3) And [Open Date] = #" & Date() & "#")
Thanks for your earliest response, unfortunately I got 0 value where there are two records on today's date.

How about the other text boxes conditions how can I get their count if more than 60 days and so on.

I know I can't figure out those conditions.

Thanks for help.🤗
 

Isaac

Lifelong Learner
Local time
Today, 13:09
Joined
Mar 14, 2017
Messages
8,738
Can you check your table design and tell me what datatype is the column [Open Date], in table [Open Cases] ?
 

Isaac

Lifelong Learner
Local time
Today, 13:09
Joined
Mar 14, 2017
Messages
8,738
Do you have values in that column (in existing records), which contain a Time value?
 

Micron

AWF VIP
Local time
Today, 16:09
Joined
Oct 20, 2018
Messages
3,476
You really have a field in that table named [Form]? Also, I would not have expected IN operator to work in a vba expression?
You might have to post a zipped db copy in order for us to see through the haze. Or you could see if this is any help.
 

Isaac

Lifelong Learner
Local time
Today, 13:09
Joined
Mar 14, 2017
Messages
8,738
Also, I would not have expected IN operator to work in a vba expression?
Believe it or not, it does, just tested this in the immediate window with a table Table1, column Column1, and three records - "one", "two", "three"
I was actually kind of surprised myself - not sure I've used that before.
Code:
?dcount("*","Table1","column1 in('one','two','three')")
3
 

Micron

AWF VIP
Local time
Today, 16:09
Joined
Oct 20, 2018
Messages
3,476
Wish M$ would try these things or at least update the sites so that the documentation was more complete.
 

Isaac

Lifelong Learner
Local time
Today, 13:09
Joined
Mar 14, 2017
Messages
8,738
Wish M$ would try these things or at least update the sites so that the documentation was more complete.
Agree. My outlook fiasco really shocked my sensibilities earlier. Something you learn to totally count on, suddenly removed.
 

Alhakeem1977

Registered User.
Local time
Today, 23:09
Joined
Jun 24, 2017
Messages
308
Hi All,
here is my db attached, if anyone could help the four text boxes ([txtCardsToday] , [txtCardsCurrent] , [txtCards60Days] and [txtCards90Days]) at the on startup form.

Thank you in advance
 

Attachments

  • Sample.accdb
    2.8 MB · Views: 203

namliam

The Mailman - AWF VIP
Local time
Today, 21:09
Joined
Aug 11, 2003
Messages
11,696
You would be better of using a single query instead of using 4 dcounts which in essence is 4 queries.

Your dcount would work with
Dcount("[ID]" ,"[Open Cases]"," [Opened Date] >= #" & Date() & "# and [Opened Date] >= #" & Date() + 1 & "# ")

You obviously have some idea about naming conventions, but your database hurts my developers heart and brain :(

Also for example your "filters"
Filters Filters

Filter NameFilter String
Opened Date = Today(Year([Open Cases].[Opened Date])=Year(Date()) AND Month([Open Cases].[Opened Date])=Month(Date()) AND Day([Open Cases].[Opened Date])=Day(Date()))
While it works, it breaks any chance of the database using any index, instead something like I did in above DCount will use indexes if added to your table. All these things will ultimately generate a thread here, "Why is my database so slow" :(
 

Alhakeem1977

Registered User.
Local time
Today, 23:09
Joined
Jun 24, 2017
Messages
308
Thanks, I amended the code provided to filter today's records as below and it works fine.

Code:
=DCount("[ID]","[Open Cases]","[Form] >= 2 And [Form] <= 5 And [Opened Date] >= #" & Date() & "# And [Opened Date] <= #" & Date()+1 & "#")

How can I apply it to filter the followings:
1. Todays & Current Items for less than 60 days.
2. Items Greater than 60 days.
3. Items Greater than 90 days.

Appreciate your help.. Thanks again.
 

namliam

The Mailman - AWF VIP
Local time
Today, 21:09
Joined
Aug 11, 2003
Messages
11,696
As per my first line.... you dont.
You do this like with your "open cases" query .... in a query
 

Users who are viewing this thread

Top Bottom