SELECT DISTINCT tblvisit.[Pen ID]
FROM tblvisit
WHERE (((tblvisit.[Visit Date From])<=[forms]![fRMBOOKINGAVAILABILITY]![From]) AND ((tblvisit.[Visit Date To])>=[forms]![FRMBOOKINGAVAILABILITY]![To])) OR (((tblvisit.[Visit Date From]) Between [forms]![FRMBOOKINGAVAILABILITY]![From] And [forms]![FRMBOOKINGAVAILABILITY]![to])) OR (((tblvisit.[Visit Date To]) Between [forms]![FRMBOOKINGAVAILABILITY]![From] And [forms]![FRMBOOKINGAVAILABILITY]![to]));
This successfully finds booked pens in a cattery. How though, can I get to see the pens that are free? Is there a feature that will allow me to conduct this query before conudcting another one to show the values which didn't appear? Thanks:D
I have a report based on a query and in that report I added a text box to be yes or no depending on 1) the number of days to complete and 2) the type of urgency. There's 3 different urgency levels so I was trying to create iif statements based on each one with the corresponding days using the OR operator between them like below:
=IIf([t_Days]<=2 And [Urgency Level]="Expedite (1-2 days)","Yes","No")_ Or IIf([t_Days]<=7 And [Urgency Level]="Normal (5-7 days)","Yes","No")_ Or IIf([t_Days]<=30 And [Urgency Level]="Low (14-30 days)","Yes","No")
Any 1 of those by themselves works fine but I need it based on all of that. So with that said, is there a way to do this? I tried using VBA code, but I think with the type of report I'm running that that wont work. It has a repetitive one line detail section with the data I'm trying to view.
I have a table called estimates and a field in that report is called RFA and this is a checkbox. How can i open a report i created (called EReport) for all rhe records in this table that have the RFA field checked (therefore true).
I have a main form with 3 subforms. Each subform is identical except for the value of the filter property. The filter is for the same field, but with a different value for each subform. So, for example, the first subform has a filter of:
Code: [WBS Element]="DEF" And [Period]=Forms!frm_ProjectFinancials!Period
while the second subform has a filter of:
Code: [WBS Element]="PPE" And [Period]=Forms!frm_ProjectFinancials!Period
and the third subform has a filter of:
Code: [WBS Element]="EXP" And [Period]=Forms!frm_ProjectFinancials!Period
The recordset for each subform results in a single record with numeric values in each field or no records at all. When the resulting recordset is empty (no records), the bound text fields on the subform display as blank. I want these fields to display 0 instead of blank so I can use them in other calculated fields. Functions such as Nz or IsNumeric do not work since there are no records and the values are neither null nor numeric.
How I can display zeroes in the bound fields when no records exist that meet the filter criteria? Or is there a way that I can dummy a resulting recordset to have all zero values when there would otherwise be no records?
SELECT Count(T_STUDENTS.studentsID) AS res1yes, T_COACHING.COACH1res, T_COACHING.COACH1port, T_COACHING.COACHworkgroup FROM T_STUDENTS, T_COACHING WHERE (((T_STUDENTS.res_vrijstelling)=False) And ((T_STUDENTS.stud_year)="1") And ((T_STUDENTS.stud_coach)=forms!F_cboCOACHING!cboCOACHING)) GROUP BY T_COACHING.COACH1res, T_COACHING.COACH1port, T_COACHING.COACHworkgroup;
It runs fine, untill the moment the count is 0. At that point it breaks the form. How can i tell access to deal with this? Somehow i need to replace the count result with 0 if there are no records that meet the criteria.
|I have completed a stockcontrol database, which i have managed to crack after a lot of hard work. Now there is one more thing that i need to accomplish. This should be dead simple but for some reason i am having a little trouble. I am trying to display all products which have been scrapped. To show that the product has been scrapped there is a tick box. Yes/no value. I presume i need to use a query, would I need to use vb to get ONLY the products which have ticked the check box to be shown? If anyone can help me that would be great!
I have two synchronised combo boxes that are working brilliantly except for 1 thing.
The two boxes are Site and Building. I find a site from the Site box dropdown and only the buildings for that site are shown in the Buildings box dropdown. Perfect!
However, I have got 3 fields showing in my Buildings dropdown.
Private Sub cboSite_AfterUpdate() Me.cboBuilding.RowSource = "SELECT BuildingName, BuildingCode, Status FROM " & _ "BuildingT WHERE SiteID = " & _ Me.cboSite & _ " ORDER BY BuildingName" Me.cboBuilding = Me.cboBuilding.ItemData(0) End Sub
that is BuildingName, BuildingCode and Status.
All three show in the dropdown BUT only BuildingName shows in the actual Building Box and I would like all 3 to be visible once I have decided on the building name I need!!
Can this be done by changing
Me.cboBuilding = Me.cboBuilding.ItemData(0) (as above) to Me.cboBuilding = Me.cboBuilding.ItemData(0), Me.cboBuilding.ItemData(1), Me.cboBuilding.ItemData(2) or something similar??
Or do I need to create 2 new boxes to return the other values?? I am so close to achieving what I want with this form!!
I have two tables to track our engineer visits, one tracks the visit as a whole and the other tracks the individual instruments the engineer worked on during that visit. This way I can track visits to customer sites separately to the visits made to an individual instrument.
Right now, I create a visit and then add Visit Lines (containing the details of the instrument visited). These instruments, or 'Visit Lines', are being displayed in the Visits Table via a related items box. Visit lines are associated with Instruments in the Instrument Table via a serial number lookup. All this works great...
However, I want to display both the serial number and the instrument description in the related items control in the Visits Table. Since the Visit Lines table only has the instrument ID lookup and not the instrument description I can't display it in the control.
I need to either:
a) Create an instrument description in the Visit Lines table as a lookup and have this automatically pull in the description based on the serial number the user selects.... which I can't figure out how to do - it's just an autocomplete.
b) Create an instrument description in Visit Lines and have a macro grab the corresponding description from the Instruments table based on the serial number input - but just for this record...
I need for Access only to sum the "hours worked" of my payroll data, only if another field, "pay code" meets a certain criteria.
In Payroll, some codes are used to designate actual hours worked like REG (Regular Hours) and like OTS (Overtime Straight) while other codes are added as "premiums" to these hours and do not mean that you actually worked those hours, like OTP and HOD (Overtime Premium & Holiday Premium). The hours are only added as a reference to the actual hours worked they are attached to. Their units should not be counted as hours worked.
I want Access to 1) Group By Cost Center, 2) Sum "Hours Paid" that are tied to a REG or OTS (only), but 3)Sum the Earnings for all pay types.
Basically, (in excel-like terms: Hours Paid Column= SUMIF of Paycode = REG OR OTS. But Earnings Amount Column= Sum of all Paycodes)
Cost Center Job Type Hours Paid Pay Code Earnings Amount
I'm a little stuck on three of my nine queries. Bascially, three of them display records for a given day, three for a given week and three for a given month. The criteria for days and months was pretty easy; I just got:
[Enter Date] for a single date DatePart("m", [Date]) = [Month] for a given month
However, I'm really stumped on how to work out the criteria needed for working out records for a given week. All I managed to work out was:
<=[Week Ending] And >"Date[Week Ending]-7"
This works when there is no test data, but not when there is. I know it's something to do with the -7 bit, but I can't work out what.
I can't get my reports' unbound field's to display the criteria parameter I enter when report is opened. I have done this before but not in Access 2010.
In my parameter, I do have a long string inside the brackets (e.g. =[Enter START Date "1/1/14" or Leave Ranges Blank to show ALL]). I am wondering if this or the quotes inside the brackets are causing it not to work.
I was trying to filter a word using Not Like "word" in my query cirteria, some data is blank also, From this record i want to retrieve the result. But when i use Not Like Criteria, filter is working but blanks columns are also filtered.
I want to display all records (including blanks) except what i shown in the criteria.
I am tyring to set up a query, that runs from a selected date range, to return only the records that don't meet the requirments. For example, I have four fields:
Name Date Attended Required
I want to start the query from a date range usesing Between [StartDate] And [EndDate]. There are mulitpule records containing the same date. Then from there I want to return only the Names that don't attend the required number of sessions.
Ex. Attended = 2; Required = 4; Return name Attended = 3; Required = 3; Don't return name
Would this be possible with one query? If so, how would I go about setting up the criteria. If not then how would i link the 2 queries together?
Been experimenting on some code to prevent deletion or changes to records that meet certain conditions.
The problem I am having is the code is contradicting each other preventing some of the records to be locked down.
Code: Private Sub Form_Current() 'Code 1 - Prevent edits if 21 day period has lapsed If TestDate2(Me.txtStartedHidden) > 0 Then MsgBox "Editing NOT allowed - 21 day time period has been reached" Me.txtBasic.Locked = True
[Code] ....
Rules are:
Edits allowed if invoice date not reached Edits allowed if within 21 day but NOT if invoice date is reached
Edits prevented if invoiced date reached and if records still within 21 days Edits prevented if invoiced date reached & records past 21 days
I just want to know how to manage items in set and individual item. Suppose my product list are
individual items = A,B,C,D,E,F,G,H,I,J,K and 5 pc set = A,B,C,D,F and 3 pc set = G,H,K
How should I design the table. Previously I designed the table for individual items and whenever orders for set is placed user had to enter individual items with quantity.
E.g. order is for 5 pc set = 3000
A=3000 B=3000 C=3000 D=3000 F=3000
Now I need to just say 5 pc set and it should be automatically populated. And also if order have combination of individual and set items.
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
I created a form that allows the user to choose the criteria that they want to see on a report using =Forms!formname!controlname in my query. It works great but I want to also allow the user to choose nothing and return all records instead of limiting them to choose just one type of record. Is this possible? Before I created the form my query had the [Enter parameter] on one criteria line and [Enter parameter] Is Null on the next criteria line and that was working great for my use but I need to create a simple form for other users.
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
MsgBox "Booking successfully added!", vbInformation 'Depending on which month is booked, will determine which listbox is goes in on frmMain If Month(Me.Date_1) = 1 Then Forms!frmCurrentBookings!ListJ.AddItem = Me.BoI 'Add to ListJ on frmCurrentBookings ElseIf Month(Me.Date_1) = 2 Then Forms!frmCurrentBookings!ListF.AddItem = Me.BoI 'Add to ListF on frmCurrentBookings End If
End Sub
So basically... I have a date field in the form 'frmAddBooking'... if the month of the date is 1 (January), then the conntents of the date field is added to a listbox in a different form (frmCurrentBookings)... but this doesn't work...
If somebody could help me I'd appreciate it greatly.
I've been running into this problem in Access. I have records which have more than 255 fields. The easiest solution would have been to get a table with more than 255 fields, but I can't do this, so I've had to split up the tables. The next easiest solution would be if there is a way to link the tables--I have a data field "IntAccNum" identified as the primary key in each table--so that when a new row is formed in one, it is formed in the others. This wouldn't waste space, since any row in the first table would necessarily need a corresponding row in other tables. Is there a way to do this?
If not...
The problem I run into is when I populate a row in the demographic table and then try to fill a corresponding row in another table. I use a command button to for a macro to "OpenForm", and then try to "SetValue" of the appropriate field in the second table. This adds a new row in the second table. This does let me populate the correct row of the second table the first time I try for each row. However, after this initial population, when I switch back and forth between the first and second table, I get an error in access since obviously I can't keep adding a primary key with the same value in the second table.
Do I need to do some sort of query to see if that row exists in the second table already. Or is there some simple solution to what must be a very common situation that I am overlooking?
Up until recently, when I selected multiple objects in design view (on a form) and right-clicked the mouse, I had several options in the 'Size' sub-menu (including 'Size to Widest / Tallest / Narrowest / Shortest').
Then I tried to be clever(!) and create a new custom toolbar which had just these options on it, as I use them frequently. This was all very well until now, when I can't find the custom toolbar I created, and the options have disappeared from the Size submenu aswell!
Has anyone got any ideas on how I can get them back??
(I have tried going into Customize to drag them back, but they're not there either! :( )
I have created a switchboard that has 8 more sub switchboards. They have got items from 1 to 8. My database is setup with users and their authorised access levels. Admin has level 1, Manager level 2, Users Level3. Now what i want is that when admin logs in all the items in the switchboard must be available, but when a Manager with leve 2 or a user with level 3 logs in to the database then certain items on a specific Switchboard must be diabled For example lets say Sub Switchboard No 3 and its item number 4 (SwitchboardID = 3 and Item Number 4) should be disabled. I tried searching it on the Forum but no use. All i request you is to help me in this issue.
