Correct Syntax of Countiif Function

[JithuAccess]

Hello Guys,

In the Unbound Text Box control I have put the following Code

=Count([strClaim ID])(iif([datPayment Date]) between #2020-04-01# and #2020-04-30#)

I want to find the Total Number of Claims ID Between 01-Apr-2020 and 30-Apr-2020

I am getting an Error:

I think the Syntax is wrong. Could you please let me know what is the correct Syntax for this?

 

[plog]

I think techonthenet.com is the best reference source for Access functions. Here's the one for iif:

MS Access: iif Function

This MSAccess tutorial explains how to use the Access iif function with syntax and examples. The Microsoft Access iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

www.techonthenet.com

 

[JithuAccess]

I think techonthenet.com is the best reference source for Access functions. Here's the one for iif:

MS Access: iif Function

This MSAccess tutorial explains how to use the Access iif function with syntax and examples. The Microsoft Access iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

www.techonthenet.com

Thanks a lot

 

[JithuAccess]

I think techonthenet.com is the best reference source for Access functions. Here's the one for iif:

MS Access: iif Function

This MSAccess tutorial explains how to use the Access iif function with syntax and examples. The Microsoft Access iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

www.techonthenet.com

Sorry but Problem Not Solved. I am getting the count of Claims if I am not giving any criteria:

=Count([strClaim ID])

But getting Error when I am giving criteria like this:

=Count([strClaim ID]) where [datPayment Date] Between #2020-04-01# and #2020-04-30#

Thanks

 

[theDBguy]

If you're trying to do conditional counting, I would suggest using the Sum() function instead. For example,

=Sum(IIf(SomeCondition=True,1,0))

 

[JithuAccess]

If you're trying to do conditional counting, I would suggest using the Sum() function instead. For example,

=Sum(IIf(SomeCondition=True,1,0))

Thanks so much for your suggestion. My fields are [strClaim ID] and [datPayment Date] I want to find how many claims we received in a month. So could you please let me know how to do this?

Thank You

 

[JithuAccess]

If you're trying to do conditional counting, I would suggest using the Sum() function instead. For example,

=Sum(IIf(SomeCondition=True,1,0))

I gave like this:

=sum([strClaim ID]iif([datPayment Date]between #2020-04-01# and #2020-04-30#,1,0))

But getting Error.

Thanks

 

[theDBguy]

I gave like this:

=sum([strClaim ID]iif([datPayment Date]between #2020-04-01# and #2020-04-30#,1,0))

But getting Error.

Thanks

Try it this way:

=Sum(IIf([datPayment Date]Between #4/1/2021# And #4/30/2021#,1,0))

 

[JithuAccess]

Try it this way:

=Sum(IIf([datPayment Date]Between #4/1/2021# And #4/30/2021#,1,0))

Thanks it worked perfectly.

I know I am annoying you sorry about that.

How to find the Total Sum of Payments made between these Dates? I have another Field [curPaid Amount] and how to find the Sum of this field by giving the criteria as Between 4/1/2021 and 4/30/2021.

Sorry again for bothering you again.

Thanks

 

[JithuAccess]

Try it this way:

=Sum(IIf([datPayment Date]Between #4/1/2021# And #4/30/2021#,1,0))

Thanks it worked perfectly.

I know I am annoying you sorry about that.

How to find the Total Sum of Payments made between these Dates? I have another Field [curPaid Amount] and how to find the Sum of this field by giving the criteria as Between 4/1/2021 and 4/30/2021.

Sorry again for bothering you again.

Thanks

Hi,

I think I solved this. I use DSum Function and it is working.

Thanks a lot for your Help

 

[theDBguy]

Hi,

I think I solved this. I use DSum Function and it is working.

Thanks a lot for your Help

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

 

[JithuAccess]

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

Thanks a lot

 

[Pat Hartman]

To explain a little more:

Count() and dCount() count the number of items returned by the selection criteria. They don't count the individual values of Count("SomeField") When you use a column name in the count, only non-null values of that field are counted. So,

dCount("SomeFieldName", "YourDate Between #4/1/2021# And #4/30/2021#") Will count only instances where SomeFieldName is not null
dCount("*", "YourDate Between #4/1/2021# And #4/30/2021#") Will count all rows that satisfy the criteria.

Count() is used when you want to count items of the form or report's RecordSource
dCount() is used when you want to count items in some other RecordSource

And as others have suggested, it can be easier to use Sum() rather than Count() when you have criteria to apply to select rows.