Hi everyone
I'm very much a low-level Access user and I am struggling with something I'm sure must be very simple. I am happy with using the Totals function to count records within a query, but when I come to adding criteria (for example: only count the record if a field within the record is a certain value) I just cannot make it work.
In a Planning Application database, I have a self-calculating field (using an IIf statement) in a table that is either 1 or 0, [InAgreement]. This is dependent on two dropdown box fields within the same table having opposing answers.
For example (in basic logic): If [OurPlanningOfficer] = "Disagree" And [TheirPlanningOfficer] = "Agree" Then [InAgreement] = 1.
In my table, [InAgreement] works perfectly, returning either 1 or 0, dependant on the dropdown fields.
In my query, I want to calculate the number of times the officers have not been in agreement over a date range so that I can display this as a one-off statistic in a report header (report based on this query). My query displays the correct number of records within a specified date range ([ReceivedDate] query criteria is: >=[Forms]![Main Menu]![Start Date] And <=[Forms]![Main Menu]![End Date]...
...but as soon as I add criteria to count ONLY the records where [InAgreement] = 1, the query returns nothing at all.
Please can anyone help me with the query criteria syntax to only count where [InAgreement] = 1. Please see the attached image, which is my latest attempt! I've tried using Count in the Totals box, then '=[InAgreement] = 1' for the Criteria, I've tried using the Criteria 1, or "1", or =1, or True, or ="True" etc etc...but nothing works!
Thanking you in advance.
I'm very much a low-level Access user and I am struggling with something I'm sure must be very simple. I am happy with using the Totals function to count records within a query, but when I come to adding criteria (for example: only count the record if a field within the record is a certain value) I just cannot make it work.
In a Planning Application database, I have a self-calculating field (using an IIf statement) in a table that is either 1 or 0, [InAgreement]. This is dependent on two dropdown box fields within the same table having opposing answers.
For example (in basic logic): If [OurPlanningOfficer] = "Disagree" And [TheirPlanningOfficer] = "Agree" Then [InAgreement] = 1.
In my table, [InAgreement] works perfectly, returning either 1 or 0, dependant on the dropdown fields.
In my query, I want to calculate the number of times the officers have not been in agreement over a date range so that I can display this as a one-off statistic in a report header (report based on this query). My query displays the correct number of records within a specified date range ([ReceivedDate] query criteria is: >=[Forms]![Main Menu]![Start Date] And <=[Forms]![Main Menu]![End Date]...
...but as soon as I add criteria to count ONLY the records where [InAgreement] = 1, the query returns nothing at all.
Please can anyone help me with the query criteria syntax to only count where [InAgreement] = 1. Please see the attached image, which is my latest attempt! I've tried using Count in the Totals box, then '=[InAgreement] = 1' for the Criteria, I've tried using the Criteria 1, or "1", or =1, or True, or ="True" etc etc...but nothing works!
Thanking you in advance.