Microsoft Access counting part of a field

95 pts.
Microsoft Access
I have a field that I need to count per group. The field contains whole numbers and decemal numbers. I only want to count the whole numbers. The field is a text field, because there is a chance the actual text can be a value. I tried using IIf, but could only put one condition; the field is "[Key Contl_Num]"<KC_NumCount: IIf([Key_Cntl_Num] Not Like "#.#","1","0")>



Software/Hardware used:
Microsoft Access 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.

Thank you all for your time, I did eventually figure out what it was I needed to show. This is the statement that gave me the ability to show both critieria that resulted in a “1”; KC_NumCount: IIf([Key_Cntl_Num] Not Like “#.#” And [Key_Cntl_Num] Not Like “#.##”,”1″,”0″). With this I am able to count only the whole numbers.

Discuss This Question: 4  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    So, what's the other condition you would have wanted to put ? A condition to check if the field contains a number or text ? If so, you could try something like this:
    IIf(IsNumeric([Key_Cntl_Num]),IIf([Key_Cntl_Num] Not Like "*.*",1,0),0)
    Please let us know if that is what you meant.
    85,925 pointsBadges:
  • RKJohn
    Let me better explain what I need it may very well not take an immedite IIF condition. My Key Control table has a Key_Cntl_Num field that contains values such as: (1, 2, 2.1, 2.10, 3 ,4 ,5). On the report I need to count the Key Controls, but only the control without a period in them. I was letting you know that this field is a text field just in case that had anything to do with how it need to be addressed. I recieved an invalid Syntax error when I used the expression in your answer. Thanks
    95 pointsBadges:
  • msi77
    Try this KC_NumCount: IIf([Key_Cntl_Num] Not Like '*.*',1,0) OR KC_NumCount: IIf([Key_Cntl_Num] Not Like '%.%',1,0)
    1,670 pointsBadges:
  • RKJohn
    I continue to recieve an invalid Syntax error when I use the expression, KC_NumCount: IIf([Key_Cntl_Num] Not Like ‘*.*’,1,0). I believe it's because u can not use the * with the IIF. When I use the percentages, the only add the value of one to the fields that are blank, but not the fields with the period/decimal in it. On the reports where I only need to see totals, I used a query with the critieria "Not Like *.*" and this works fine. The report I'm working on now has to show the detail of every Key Control but only count the ones without the period/decimal, therefore I could not use the that particular query. I appriciate the time you all are putting into to this. Let me know what I can do to make it any clearer. Thanks
    95 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: