I have a query that has several checkbox fields. When I open the query it shows the checkbox not the value (see attached 1), when I export the data it comes into excel as TRUE and FALSE. Is there a way I can in the query show the value ie True or False and secondly add some code in the query that takes a True value and makes it say something else like "BOB".
View RepliesI have created a query for telling me the dates when I need to check driving licenses from my drivers.
I have also made a Yes/No check box for when they are 'Live' employees and another Yes/No check box for when they have left. I need to exclude the records from the query with the 'Left Employment' box checked ('Live' box un checked).
I need to keep the record for 12 months after they leave so I cant delete it.
I have a form with a check box. A query is run that looks at that check box and decides what the criteria are based on that. So, if the check box is checked, it should pull in all data in the field that is a Y. If it is not checked, i want it to pull all data (Y's and N's and blanks).
here is my criteria:
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","*")
This does not seem to work. I have also tried:
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y")
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y",Like "*")
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","like "*"")
I have a database used to manage teaching assignments (which kid is assigned to which teacher so to speak). I have this relationship defined through three tables, a teacher table, a student table, both with unique ID's. The third table is used to define the assignment. Also, the kid table has an extra GroupID. The group ID is what is used to define. So in the definition table, Teacher 1 is assigned to Group 1, and so on (though their may be 20 kids in group 1). When a new teacher is added to the teacher table, I need it to add it to the corresponding field in the definition table. The groupID is in the table as an Autonumber so that will populate automatically.
I need to appendTeacherID to tblassignment (TeacherassignmentID) and have only one occurrence of the TeacherID. So, if I have teachers 1-8 listed, each assigned to a group# in the tblassignment, and I add Teacher 9, I need it to add Teacher 9's unique ID to the TeacherassignmentID field without adding 1-8 again. I can't figure out how to "check" for ID's 1-9 and add only those I've added to the teacher list that aren't already assigned to a group.
I've tried a few different SQL queries append/select queries but nothing seems to do what I need it to do....
Attached I have a database that I've been working on which has a form called "frmCriteriaSearch". It is based off of the qryCriteriaListBoxUpdate query. I am trying to get the listbox in the second tab of the results section to work. It queries fine for the checkboxes, but I cannot get the comboboxes to affect the query (unless a checkbox has already been selected)
View 6 Replies View RelatedI use the code below when a form is closed to either save or undo changes. However I only want the msg box to appear if there have been any changes to the record that the form is based on, if there aren't any changes then it will just docmd.close without the msgbox. How would I check if there have been any changes to the record? I think you have to use 'Dirty' but im not really sure how to use it.
Private Sub CLoseUR_Click()
Dim strMsg As String, strTitle As String
strMsg = "Do you want to save changes?"
strTitle = " Save Changes?"
[Code] .....
I have created a query and one of the columns of the output displays a check box because this is the data type of the underlying table.
I would like the query to output either Y or N rather than displaying the checkbox.
How would I do this?
I have one query (lets call i Q1) and I have two tables with identical structure.
Table 1:
- id
- iddesc
- comment
Table 2
- id
- iddesc
- comment
Table 1 consists of an imported xslx-file. The idea with Table 2 is to use it to edit content. Sometimes only one field is edited in Table 2 and in those cases i need to get the other values from Table 1. Q1 should (but doesnt) generate something like this:
Q1:
- id [always from Table 1]
- iddesc [from Table 2]
- comment [from Table 1]
My question: How do i, in a query, check if data exists in a field in Table 2 and if it does, add data from that field and not from Table 1?
I need to run a query in Access 2007 to find any records that have been entered in the last 24 hours.
My field is of type datetime and the name in the database is "EntryDate"
How would I check which records had been input in the last 24 hours?
Help! I'm designing a database for my A level project at college and I just can't get this one section to work. I've spent alot of time linking things up so that you can't double book one of 4 rooms. It's a booking system for a recording studio. The thing is I have 3 basic packages of equipment and you can only choose one for each booking. The price is diplayed at the bottom of each package. I want to be able to check a box and then I want this price to be added to total price. I cannot link update queries to a check box I've tried every songle way I know. I'm not too familiar with access either :S. One more thing, I want to be able to tick a box which says Discount and then I want this to update the total price field again so it is multiplied by 0.9. I have manage to figure this out in an update query and it is also based on the fact that a band has made a deposit that is bigger than £0.00. I just cannot link it to the form.
If anyone can help it'd be much appreciated, I'm panicking too much over this project.
lets say I have a table with five tasks for five employees and each employee's data type is with 'Yes or No'.
I need to have a desired result to check
1.if all the employees have completed that particular task then the result should be 'YES'
2.if anyone employee has not completed that particular task then the result should be 'NO'
so, for each task there must be a check on all the five employee's data type.
I have a table and in that table I have multiple columns one being check boxes and another is the description. There are others but these are the relevant ones. I filter under description for HR Payroll but this description is not uniform. After HR Payroll it could vary so the description is not always the same. I want to select all check boxes under this description and was told use a query no clue how to do that though.
View 3 Replies View RelatedI have form that user can filter the records and generate a report but I have difficult trying filter null date.
If I have check box called filter null if it has a tick in I would like it only show records that have no value (is null) in field "date start" but if unticked I would like it to only show records with a date in field "date start" ...
I 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 two date columns in my table called "End date" and "Closing date".
An example could be 14-06-2015 and 13-04-2017.
I need to make a query which is checking if the two dates are equal to the last day of their respective month. I don't have two columns in the table with the last day of month, so I first need to find out what the last day in the month is.
I have 2 tables:
-tblServices
-tblPricesPeriods
Each of the tables has fields for:
-StartDate
-EndDate
The logic behind the database is that each service is contracted for certain period, from StartDate until EndDate (if EndDate is not given - null - it means it is a permanent contract). For each service, we need to input prices. As prices may change during time, we may have different prices records for a service - we need to have the service period broken down into subperiods with different prices. So we need StartDate and EndDate for each period for which particular prices are valid (again, if EndDate is not given - null - it means the prices are valid "forever" into the future from the StartDate).
My problem is: I want to have a check that would return the number of services which period (tblServices) is not fully reflected in the prices periods (tblPricesPeriods). Those would be for example:
-gaps between subperiods (subperiod1 finishing 30.09.2014 and subperiod 2 starting 3.10.2014)
-service period (tblServices) limited by EndDate (not null), while prices period for this service (tblPricesPeriods) having null as EndDate
-start date for service (tblServices) earlier than earliest start date for prices periods for this service (tblPricesPeriods)
-...
Preferably, I'd like to use a normal query for that (or set of queries), but I'm opened to other options.
I currently use
Code:
strSQL = "INSERT INTO tblDepartments (Department) VALUES(txtnewdept)"
To insert new departments into a table, however id like it to check to see if a department name exists in tbldepartments.department to prevent duplicates being added?
I've created a database with entries corresponding to different participants in a tasting experiment. All the entries are stored in a single table. The participants are each members of a family, with participants of a family given a family number (increments with family) (see image link below).
imgur.com/yM1vO59
I want to be able to check that for either 'Mother' or 'Father' of 'Child1','Child2', etc. within the same family, the location within the sweet, sour, bitter column is the same.
(I'm asssuming five queries will have to be run, one for each of the flavours).
To reiterate, I want to check for example if 'Mother' and 'Child1' within 'Family Number' 3 taste 'Bitter' in the same place (Which they do - 'Middle').
Is there a way to check if a series of action queries can be run (without any error) before actually running the queries?
View 1 Replies View RelatedThe problem I have is, that I need to insert an apointment into de database but first I need to verify if there is a record or an apointment in that room that day the same hour, if that is so send a msgbox saying an apointment already exist in that room this day at this hour.
i tried using dlookup but it only works in one record using just one criteria
What I am trying to do is create a stock database and iv imported a blank version of the northwind template and filled it in with my own products which is fine up to a point.
What i also have though is i get weekly reports of the stock of each product with their batch numbers, expiry date and quantity remaining for that individual lot sent to me from another company. (So i might have several lines with the same batch number and expiry date but diffeent quantities) So what i want to ask is:
Is there any way for me to combine the data in the weekly reports with the stock level data in the northwind template?
What i want to be able to have is where somehow i can see the total quantity of eac product with the same batch number and expiry date and then it tells me when that batch has expired and it also automatically deducts the stock from my total stock when it gets to within e.g 9 months of the expiry date as we will not likely sell those.
I need to do this as at the moment all i can get from my database is how much stock has been ordered, sold and what we have in total on hand but if the stock is close to expiring then i wouldnt know and i need to be able to see when to order stock in advance as i want a minimum of 6 months cover and ideally 9 months cover as orders for stock dont come in straight away.
Also, how i can import the data from the weekly reports into excel/access easily as they come as word files presented as a table but the data is put in frames i think and iv tried removing them but then it all looks messed up.
I am trying to write a query that will check all records in a table but exclude the 10 newest records in the table.
The table is from a stock program i have wrote for the company i work for (i am a novice access user). what ive been asked to do is write a duplicate order system that will flag up if the order has already been packed.
the table logs the [OrderID] with each item [barcode] scanned out with a barcode scanner what i want is a query that checks the OrderID for a duplicate entry in the entire table but because the OrderID is entered with every item scanned i want to ommit the last 10 records as prety much no order has more than 10 items i understand this may not be 100% fool proof but it is close enough.
The other option is to have it ommit all records within the last 15 minutes there is also a [Time] and [PackDate] Field which im guessing could be used for this the time field records Now() and the PackDate records Date(). After searching the web i cant seem to find anyway to ommit the last 'n' records and the few things i have found with the Date("m",-15,Date()) doesnt seem to work
I have a database that records information on the staff working in the building (over 400). This information includes the licence number of any vehicles (up to three) they might be parking in the parking lot. I have created three fields in the table to record the licence numbers of the vehicles.
My problem is that to search the table for a licence number that was recorded, unknown to me, in the third field, I have run the search three times, once for each field.
Is there any way I can setup a query that will check all three fields in one search request.
Alternately is there a way to set up the field so that it contains all three Licences numbers in such a way that a single query will find any one of them when a search is run for a single Licence number.
I have a simple form that has a subform with its datasource being a query that is a multi-table select (complex joins)... the subform is set to allow dataentry, edits, etc. I have a checkbox that displays a value from the query (0, -1). Is there a way I can enable this checkbox to write back to one of the tables? Temp table to hold the contents of the query?
View 8 Replies View RelatedI have a transaction table the last record is [EquipIn]. during the equipment checkOut process all the other headings in the table are filled in. The idea is to fill in the [EquipIn] record a couple of days later when the equip is returned. How the heck can I make a equipment check in form that will update that one record? I can't do this manually as we have 1000+ pieces of equipment.
View 13 Replies View RelatedI'm trying to create a query to work out the total amount to invoice based upon some selections. Currently my query looks at the values in several fields (numberOfRollsUsed etc) by taking the value in these fields and multiplying by a fixed amount to calculate the total.
What I would like to add to the query is fixed values based upon some check box selections. So if check box A is selected, add 5 to the total, is check box B is selected, add a further 10, and so on. All fields and check boxes are held within the same table (Job).
Am I being daft or trying to do something in the worst way possible? I'm actually adding this to an existing system so I don't have so many options to completely redesign the system to calculate this in a better way.