Microsoft Access counting part of a field
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
Software/Hardware used:
Microsoft Access 2007
ASKED:
February 23, 2010 7:50 PM
UPDATED:
March 1, 2010 5:02 AM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
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:
Please let us know if that is what you meant.
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
Try this
KC_NumCount: IIf([Key_Cntl_Num] Not Like ‘*.*’,1,0)
OR
KC_NumCount: IIf([Key_Cntl_Num] Not Like ‘%.%’,1,0)
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