I have a report that has a nummer of check boxes, I want to count the number of boxes that are true.
Example:
I have 10 boxes on my report and only three of them are True.
I have put in the bottom of the report
=Count([checkbox])
But this seems to count all boxes
Please help
Cheers
Andy
Hi! When it rains, it pours, right? I'm wondering if any of you know of a way to count true statements...
What I mean is, I have a query where I have a series of 5 different OR statements, meaning I want to see records with either a min. ed. number, or a years of experience number, etc...
The query returns every record with at least one of the criteria being met. What I need is to count the statements that are true for one record. Let's say I have someone who meets all of the OR criteria, I need a way to count the number of criteria that they met. Is there a way to do this? Maybe with a crosstab query??? Thanks!!
KellyJo
Hello everyone, i am trying to get 2 listboxs (the first is for name, the second is for state). I need to be able to select a name from one listbox and a state from the other and have a query display after a button is clicked. At the moment i have found a section of code which runs under the OnClick Event Procedure of the button to display the query (one listbox only though). It is as follows:
Private Sub cmdOpenQuery_Click()
On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM tblCompanies"
'Build the IN string by looping through the listbox
For i = 0 To name_listbox.ListCount - 1
If name_listbox.Selected(i) Then
If name_listbox.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & name_listbox.Column(0, i) & "',"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [strCompanyCounty] in (" & Left(strIN, Len(strIN) - 1) & ")"
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "qryCompanyCounties"
Set qdef = MyDB.CreateQueryDef("qryCompanyCounties", strSQL)
'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryCompanyCounties", acViewNormal
'Clear listbox selection after running query
For Each varItem In Me.name_listbox.ItemsSelected
Me.name_listbox.Selected(varItem) = False
Next varItem
Exit_cmdOpenQuery_Click:
Exit Sub
Err_cmdOpenQuery_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If
End Sub
Is anyone familiar with the code to tell me how i go about adding code to this so to have a second listbox working?
The working example with one list box can be found here: http://www.databasedev.co.uk/downloads/multi_select_listbox_2000.zip
Cheers
Pete
I have a text box "txtCode" which i want the user to fill in but when the user moves to the next box i want a msgbox to appear and ask the user if they have checked that the code is already in existance, i need the msgbox to have a yes or no.
if the user selects yes i need my checkbox "ckChecked" which is hidden on my form, to be set to true.
if the answer is no, i want the current record to be reset to null and the following form opened:
"fFMbyEcode"
can this be done via the Msgbox? if so can someone please help me with the code.
cheers
Andy
I have a report called "orders" and on this is records for orders of the day inputted.
On this report I have a control called "urgent" and a picture called "UrgentPicture" with the picture set to visible.false?
If any of the controls for Urgent is True then this will show my image, but this is not happening?
I am using this in the current event on the report
If Me.Urgent = True Then
Me.UrgentPicture.Visible = True
Else
Me.UrgentPicture.Visible = False
End If
Hi All
I have set up a form to run a query. There are 3 main variables; 'Status', 'Substatus' and 'Publications'. The publications variable has multiple choices on the form as it is likely i will be sending out more then one publication at a time.
What I need to be able to do is that if a dropdown box is left empty it returns all fields of that variable but at the same time only those that are within the criteria set out by the other combo boxes.. i.e. status is left empty, but the query still runs and filters out those specific substatus and publication. I think this is becoming complicated because the publication criteria relys on five combo boxs.
So in short, how do i get a criteria to return all results for that field if the combo box is left empty but still filter the entire list by the other combo boxes.
I have tried setting the parameter in the criteria as "*" or forms.combobox1 etc but this doesnt really allow for the multiple selections for publictions!
help!, im a little lost.
many thanks in advance
Shapman,
I have a combo box in my report design and I would like it to display "true" values as a list with check boxes.
View 5 Replies View RelatedI have a table that contains the following fields:
township
male - yes/no
female - yes/no
number of kids
I need to create a query that will give me the count of males and females and sum of number of kids - all grouped by township.
I have created 3 separate queries that calculate each part and they work. My question is how do I display the results of all these queries in one report, all grouped by townships?
Or if there's a way to create one query that will add all of this up? when I tried to create one query, the check boxes were not calculated properly, because Access did not distinguish between filled and empty checkboxes and would just count them all...
Any ideas or suggestions would be really appreciated.
I was just wondering if there is any way that I can create a query to find all of the records which have a tick box checked.
There is a tick box on each form and I want to know what I type in the 'Criteria' box is Query Design so that I can quickly and easily search for all those which are checked.
I know that in a normal query, to search for a particular object you can type it in (e.g. 'Mugs' - will find all the records under the name of 'Mugs' when typed under the right heading) but i'm unsure of what to type in under the 'Target' heading to make it find all those which are checked.
Any ideas. . .
hi
i have table for attendance monitor and contaion following attribute:
session ID
Student Id
Attended session which is in yes/no format or in other work it give u option to tick it
date
and now what i want to do is that create a query and tell it to only count the number of attended session which has been ticked not count all the attended session for one student ,only count the ticked one
any idea please?
thanks
I have a query problem that I have had no luck solving either through books or other post on this fourm. It might be because of my lack of Access skills or just might not be possible. I have a query that looks at one field in a table to count how many records have the check box checked. It works great when the query can find at least one as it reports it back, but when there are none with the boxes checked it comes back blank. What I really need it for when there are no records checked for it to come back with a 0. I have tried using NZ with no luck. If anyone has a solution to this either using NZ or some other way please let me know. Also please explain in beginner terms as Im still learning.
Thanks in advance
Hello,
I'm using Access 03 and have a query that checks for the existence of a value from another table. This works well but what I'm not able to figure out is how to then get a count of the times that entry is found in the table.
In other words...
I have a table called 'jcaScore'
in this table are unique text values in a column named 'Tier3'
My query needs to check the 'analyze' table, 'Tier3' column and COUNT the number of times each of those unique Tier3 values from the jcaScore table are present and place that count in the 'jcaScore'.gapscore field.
I've almost got it but I can't figure out the last argument.
UPDATE scoreJCA LEFT JOIN analyze ON scoreJCA.[Tier3] = analyze.Tier3 SET scoreJCA.gapcount = ???
Much adulation for any assist!
TGIF too!
All the best,
Aaron
1) If I check recordcount without move rst to last position I get wrong number of recordcount.
Code:
rst = CurrentDb.OpenRecordset(strSQL)
msgbox nz(rst.RecordCount, 0)
2) If I move rst to last position, number of recordcount is correct, but! If there's no record in my string I get an error that rst can't move to last position.
Code:
rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveLast
msgbox nz(rst.RecordCount, 0)
how to check correctly RecordCount even if my query in string has no record?
1) If I check recordcount without move rst to last position I get wrong number of recordcount.
Code:
rst = CurrentDb.OpenRecordset(strSQL)
msgbox nz(rst.RecordCount, 0)
2) If I move rst to last position, number of recordcount is correct, but! If there's no record in my string I get an error that rst can't move to last position
Code:
rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveLast
msgbox nz(rst.RecordCount, 0)
So the question is, how to check correctly RecordCount even if my query in string has no record?
How do I check, before opening the form, whether any records exist in the table bound to the form?
Thanks
Is there a way to hide a label on a report if the sub report count is 0?
View 2 Replies View RelatedOther than a check box, is there any way to check if a user has printed a report when they open it to view and give them a reminder on close?
Thanks
DBL
I have a report with an if then statement for a checkbox.
If checkbox is true...etc versus if checkbox is false...etc
When there isn't a record that has this checkbox checked, I get the error
"You entered an expression that has no value." (Runtime error 2427) How can I ignore this error
when the record doesn't apply to the criteria?
I have create a report which contains a check box. What I want to do is to hide the box if it null. I have attached an example database to show what I'm trying to do. I have tried using:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.[Written Work Seen1]) Then Me.[Written Work Seen1].Visible = False
If IsNull(Me.[Written Work Seen2]) Then Me.[Written Work Seen2].Visible = True
End Sub
in the OnFormat event of the Details section of the report.
I'm trying to figure out how to filter a report using a check box.
I have created a form, which if you push an industry labled button on that form it will show you the report with what work was done for that industry.
Now I'm trying to modify it so that I have a group of check boxes; each check box being a different office location the company has.
What I want to make happen is if say out of office A. B. C. and D., A. and D. are checked and I hit the button of a specific industry it will bring me up a report of office A. and D. Combined for that industry.
From what I've figured I can create a bunch of reports of all the different possiblities and link those to that senerio, but that seems tedious and ridiculous.
Is there a way I can code VBA to do this for me? Or am I going to have to go through the proccess of creating a bunch of different querries and reports beyond what I have now to pull up an industry.
I have a check box in a form (set to yes/no in the table) and when I generate a report I would like the check box value to show up as yes or no in text box, but I can't seem to figure it out.
View 3 Replies View RelatedI have a report that is broken down by regions. Each Region could have multiple entries for each. I would like to have a Summary at the bottom that counts each Region on the report and gives a total for the report. What I am getting is it counts each time there is an entry for the region. I assume since I have the Region as a Header it still thinks it is listing it multiple times. Any help???
View 1 Replies View RelatedHi, I have a Report called Product Details Display, which shows a few items. I want to create another report showing the number of times the "Product Details Display" report has been visited on a single day. I dont know if it is possible to do it for a single day, but if it isn't I can due an overall counting of the number of visits made to this report.
Thanx in advance
I needed to get a report that is based on year. The default report doesn't group them by year. Right now I have a query that extracts the year from each date. I'm guessing the next step is to count the number of occurences for each year. Then finally take each distinct year and base the graph on the count for each year. While I have an idea on how this may work, I don't know how to implement it.
scratch
Good afternoon! I have a report that looks just like a spreadsheet. I have names down the side and dates across the top. The grid contains the count of date for each name. These are the dates that an employee is scheduled to work. Is there a way to also document within this report (with "R" for requested off, or "A" for available), to note when they are available or have requested off? I'm attaching a zip of the report and the code that calculates the grid also. Thank you for your help...
KellyJo
This is the query I used:
PARAMETERS [Forms]![frmDates]![Date] DateTime;
TRANSFORM Count([Staffing Query].Title) AS CountOfTitle
SELECT [Staffing Query].ERName, [Staffing Query].Department, [Staffing Query].Shift, Count([Staffing Query].Department) AS [Total Of Department]
FROM [Staffing Query]
GROUP BY [Staffing Query].ERName, [Staffing Query].Department, [Staffing Query].Shift
PIVOT "d" & DateDiff("d",[Forms]![frmDates]!Date,[Date Scheduled]) In ("d0","d1","d2","d3","d4","d5","d6","d7","d8","d9","d10","d11","d12","d13","d14","d15","d16","d17","d18","d19","d20","d21","d22","d23","d24","d25","d26","d27");
Hello!
I have a report with a field that returns data according to a date that the user enters when he opens the report for viewing (this is based on a parameter query). So for date 1 you have let's say 10 names and for date 2 you have 5 names. Now here is my question:
How do I create a box on the report wich returns a count of the names field? So for date 1 it should return the number 10 and for date 2 return 5. I don't particularly want to have this in the query but would prefer to have this just on the report...
Can anyone suggest a solution?
Thanx