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