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
working through validation rules etc at the minute on my database. Have come across this one is sort of stumping me. I have a tick box in the field "Medication to be repeated" the next field after it is "Date to be repeated on". I am looking a validation rule which makes it that if the checkbox is ticked then date to be repeated must contain a valid date value.
View 8 Replies View RelatedI have 3 queries populating 3 subreports on a report, i have a checkbox on my main form i launch the report from - i reference it in a filter on the form to ignore certain error types - id like it do to the same in the queries that populate the reports
So the reports atm give - an over all count of error types, a count per person and then a breakdown on type per person
Thats all fine but id like the option to ignore one type of error (as we sometimes look at it and sometimes dont).
I have a subform that populates from a query from a table. I also have a checkbox as part of the table. If the checkbox is ticked, I don't want that record to show in the subform
View 4 Replies View RelatedI there a way to determine whether or not my checkbox within a form is ticked or not dependent on another value within a field?
I have a field called 'DaysRemaining' and another check box field called 'Expired'. I want the expired checkbox to be ticked if the value within 'DaysRemaining' is '<0' and unticked if '>=0'. Is there a way i can do this within the control source of my check box?
I want to lock an individual record when a certain check box is ticked so it can't be edited again. I've tried setting the form to read only when this check box is ticked on the form OnCurrent event, but this locked every record and I had to remove the code to untick the check box and be able to edit the record again.
View 2 Replies View RelatedI am writing the following code to check if 'Active' field in table TypeTable2 is ticked or not for records. If I write the following statement to select the records from Access table where Active is not ticked then it gives "data Type mismatch in criteria expression".
Code:
strsql = "SELECT distinct EnvelopeType FROM TypeTable2 where Active='Yes'
I 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. . .
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
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.
I have sort of found an answer to my problem by this thread (Thread (http://www.access-programmers.co.uk/forums/showthread.php?t=20358&highlight=show+field+ticked))
but it doesn't seem to be working.
I have (amongst others) a tickbox and a combo.
Basically, I would like it so if the tickbox is ticked, the combo is enabled, and no tickbox and it is disabled.
I have been putting this code:-
If Me.Helpdesk_Call = -1 Then
Me.Combo23.Enabled = False
Else
Me.Combo23.Enabled = True
End If
on the forms onCurrent event, and then changing the onUpdate event of the combo, but is doesn't seem to work. I have tried numerous amounts of variations, but nothing seems to be working.
Can someone help?
Thanks.
Frank.
Hey,
In the form I am creating, I have checkboxes. Some of these checkboxes, when ticked, need additional comments entered into a comments box.
Only when the box is ticked, the comments box should appear/become enabled.
Any pointers in the general direction I should help would be greatly appreciated.
Thanks.
Hi All.
What I would like to do, is have a form popup when the main form is opened in my db, which will detail all the changes I have made to it since the last version (this bit is OK) and also, I would like a checkbox on the popup form, that the user can tick once they have read the details so it will not show up everytime they open the main form. (Just like the "hidestartupform" property within the Northwind Database)
I create the form and all that, but do not know how to do the checkbox so once it is checked, the form will not appear.
Hope this makes sense and someone can enlighten me and point me in the right direction?
Many Thanks.
Frank.
I am working on a database which will basically serve to be a massive Auditing Checklist. Lots of "Is this done" type yes/no questions to record, some with a comment.
Brief Description: I have many child-forms on a tab-control, each with a different heading/category of things to check for. When someone visits a store, I would like them to record their findings in this form.
In the above example, if the Floors are clean, no input is necessary. If the floors are not clean then the user ticks the checkbox and the text "Floors were not clean" should be automatically entered into the field: Cleanliness 1. The reason for this is that I wish to link the value from this field to a Microsoft Word document using mail merge for reporting purposes, and in the report it needs to state any problems in full sentences.
I have tried using this code in order to update the comment field when the checkbox is ticked, however, the problem with this is that I would have to insert code manually for every single checkbox in my forms, and I have about 150 checkboxes in total.
Code:
Private Sub Cleanliness_A1_AfterUpdate() If Cleanliness_A1.Value = -1 Then 'If checkbox is checked
Cleanliness_C1.Value = Cleanliness_C1.DefaultValue
Cleanliness_C1.Enabled = True
Else
Cleanliness_C1.Value = ""
Cleanliness_C1.Enabled = False
End If
End Sub
I'm trying to get an Insert statement to execute whenever the user ticks the tickbox however my code is failing to find the output table 'Manager':
Code:
Private Sub Check34_AfterUpdate()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
[Code] .....
I'm a novice user of Access 2013.
I would like to be able to use a checkbox control to make either text boxes or forms appear when ticked, ultimately to stop a form being so untidy/to conserve space. I'd be using this on multiple occasions.
(If actioned, would it automatically shift the other controls in the form down to make space, or would it just overlap?
I have 2 tables both linked to SQL Server 1 has policy information and the other has error information both tables are linked with the relationship policy Ref. The error table(table2) has fields Pol ref, Error_Type,error, Comments, response_comments and response_Date.
There are 25 types of error so what I have done is copy all fields from table 2 25 times exlcuding the policy ref and Error_Type but to make them relate to the relevent error I need to setup some VBA code that does an if Error1_checkbox is ticked then make Error_Type ="error1" and Error=1 else if error1_checkbox is unticked then make error_type null(blank).
I am using ACCESS 2010....
My database only has one table of data so it's not complicated.I would like to create a form that can create customisable Excel exports of the data based on set conditions and exporting only select fields. URL...I have one table of data (tblCustomers) which contains all of the fields in the box above.
I would like export the data from the table to excel showing only the fields that are ticked in the box (frmCustomReport).The check boxes are named chk then whatever the field name is eg. chkLocation, chkStatus. The labels are the names of the fields in tblCustomers. Is there a way to do this in SQL or VBA?
how can i make a image appear in my form when there is a check in the check box from the table?
View 14 Replies View RelatedI have a table tblBookings.
In this table it has a bookingID, CustomerID and some other none relevant details.
The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings
A customer can exist in tblCustomer without existing in the booking table.
I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).
I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.
SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings
FROM tblBookings
GROUP BY tblBookings.CustomerID;
How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)
I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.
The Ritz. Bookings 0
The Hilton. Bookings 3
The Carlton. Bookings 0
The Lowry. Bookings 2
For every hotel.
That kind of thing.
If you need more information please shout.
I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.
BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797
The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.
Right now this is what i get
BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014
This is the output i would like
BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014
Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?
current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));