Access: DCount Code help

[SusanK4305]

This is what I have:
=DCount(-"[Action Date]","Action Log","[Action Type] = 'Complete'")

I placed it in a criteria field in a Query. File Name reads "Action Date"

I need to count how many Records have an Action Date if the Action Type doesn't = Complete.

[SusanK4305]

Sorry not File Name... it should be Field Name

[Stewart Ross]

Hi. When you mention that you want to count the records which have an action date is it the case that for those records that don't the Action Date field is null? If so you will need to include this in the Where clause. You will also need to change the '=' to '<>' in the Where clause and remove the '-' at the start of the DCount.

Finally, as you are counting rows the 'Count all' symbol, "*", can be used instead of a specific field:

=DCount("*","Action Log","[Action Type] <> 'Complete' AND [Action Date] IS NOT NULL")

-Stewart

[SusanK4305]

Correct the Records that should be counted are ones w/o an Action Type that = Complete and therefore will not have a date because the action "complete" will not even be in JAne Doe's action records.

I tried entering ur code insted of mine (which btw I know was Incorrect but I thought it could help explain what i was looking for :) ) but it didn't work I am going to try it as an Expr1:

[SusanK4305]

Yay! That worked... Thank you so much. one task down many more aspirin's to go.

For others that find this and need it.

Please note: Action Log (table name), Action Type(dropdown options w/ in Action Log), Complete (Criteria),and Action Date (Date time field w/in Action Log) are the areas that u will change to match your database.
As a Field Name place this in your Query:

Expr1: DCount("*","Action Log","[Action Type] <> 'Complete' AND [Action Date] IS NOT NULL")

And Expression in the Total type

[SusanK4305]

ok I don't know what I did but I jacked somthing up.
have the above exp where it is and another
Expr2: " [tblAction Log].[Action Date] Between [Start Date] And [End Date]" as a count. I have a total of 14 records to be sorted 2 Completed.
This is what i get when I run the query

Action Type CountOfAction Date Expr1 Expr2
Delayed 6 12 6
Issue Complete 2 12 2
New Security Packet 1 12 1
OPM Rejection 1 12 1
Other 2 12 2
Sent to EOD 2 12 2

[SusanK4305]

Correction:

Action Type CountOfAction Date Expr1 Expr2
Delayed 6 12 6
Issue Complete 2 12 2
New Security Packet 1 12 1
OPM Rejection 1 12 1
Other 2 12 2
Complete 2 12 2

[SusanK4305]

Ok Well I cant get it to line up. so Delay should = 4 because there are 2 completed packets. Exp 1 is counting all records and the Date is counting w/o exp 1. Y?

[SusanK4305]

Please some one help.
I need this Expr1: DCount("*","tblAction Log","[Action Type] <> 'Sent to EOD' AND [Action Date] IS NOT NULL")
to work only for Action types that read "Delay".

Example: I have 17 total records
Delay ..... 5
Dropped ...... 1
New ..... 7
Complete ..... 4

When I add the bove Expr I get 13 for all, but I only need the Expr to work for "Delayed"

So it should look like this

Delay ..... 1 (this is Delay (5)- Completed (4))
Dropped ..... 1
New ..... 7
Complete ..... 4

How do I do this.

* Please not this info will be put in a report. I have a nother question post for the report part but you may want to read it to find out the end game.
Thank you!