|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!
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 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 am trying to enter multiple parts into the same field and using commas to separate them. Is there a way to create multiple records with the same information, but change the part numbers in them according to comma's?
Would look like this.
Primary Key Location Part number 789 bin89756 89756,87564,78965,12565,45896
I want it to do this in the end.
Primary Key Location Part number 789 bin89756 89756 790 bin89756 87564 791 bin89756 78965 792 bin89756 12565 793 bin89756 45896
I am using Access 2000 to build a database of car parts, and the cars they are currently installed on. I can assign each item on the "parts" table to a particular car listed on the "cars" table (or to its current location if it is not on a car). I can look at the cars individually, and have a subform that shows the parts currently assigned to that car in a list. That much is great, and is exactly how I want it.
Here is my question:
One of the fields in the "parts" table is "hours." Every time I use a particular car, I would like to increment each of the parts assigned to that car with the hours they were used. Ideally, I would have a form called "Use Vehicle" or similar, and in it I could select the particular car from a dropdown, and a subform would show the particular parts assigned to it. Then, I want to either: a) push a button with a particular time increment (like 0.25 hours) OR b) just enter a value in a box, which would then ADD that number to the "hours" field for every part in the subform.
I want to be able to perform an AND search on a field where both specified values must be present for an item to be selected.
I have a table with some properties of items. Each record in the table is for a specific item and one of its properties. Say for instance that I want to list the items that are described with both "red and green". Assume that there are entries for widgetA to widgetG with colors "red, blue, yellow, purple" and widgetB and widgetE also has the property "green" in addition to the others ... the query should return widgetB and widgetE only.
I tried to use MSAccess 2003 query builder but cannot get the AND result to work. I switched to an OR and verified that the two values I am searching for do exist for two items in my table.
Here is the SQL statement that did not work. SELECT Properties.PropertyIndex, Properties.HerbOilLink, Properties.PropertyTypeIndex, Properties.PropertyDescriptionIndex FROM PropertyType INNER JOIN Properties ON PropertyType.PropertyTypeIndex = Properties.PropertyTypeIndex WHERE (((Properties.PropertyDescriptionIndex)=80 And (Properties.PropertyDescriptionIndex)=15));
I need to ask Access to calculate a total number of records but only includes duplicate entries once.
For example: If the field entries were A, A, A, B, B, C, C, D
It would return a total of 4 (counting A, B, C & D only once).
Can anyone advise on how I can do this? My only alternative is to print the database out and cross out duplicates. Not the most interesting way to spend my afternoon.
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 a field in a query that I would like to display differently than what is stored in the table. The field is call apt# and the output looks like this 0000000011409468Ø. I would like to display this data without showing Ø. Is there a way to do this? Thanks..
Is it possible to display two result in one field. In essence i have a table of tasks with a due date. I would like to display a count in red of overdue tasks in reference to the current date and a count in green of the tasks which are after the current date. The date is based on month year.
I think I may have to have two seperate fields and make them look like one by having no outlining but if its possible i would rather do it in one field.
My knowledge of VB etc is very limited but I can follow code and sometimes adapt it successfully.
I am trying to make a query that includes 3 other queries. The primary key is customer number and I am trying to pull the sales for each customer by week, month, and quarter. If a customer has not purchased anything it does not display at all. Also, if they purchased something in the quarter or month but not in the past week it doesn't display. I would like the sales for the week, month, or quarter to display 0 if no transactions were made. It is not feasible to add a record for each customer with "0" since this would be updated daily.
I'm new to access. Hope someone can offer some help!
I have created a simple database to better organise some day-to-day office data management. For instance, I have a form (just) to record event attended by staff. The data would then be output as a brief report on overall office activities. Since it's event-oriented, staff attendance is just one complementary information. I went through most of the post re multi-select. I understand that it may not be a good idea to return values from multi-select list boxes into a text field, as it would be problematic for using data-analysis in the future.
In my case, I just need to have a text box, neatly (and easy for user, too) shows who attended. As suggested in one of the posting, I follow the instruction of http://support.microsoft.com/default.aspx?scid=kb;en-us;210203. But it doesn't seem working. I attach my databsae (sorry, it's very simple, as I want to test whether the multi-select work).
Your help would be greatly appr:confused: :confused: :confused: ciate!
Also, what about if I group option button, will it allow multiple return by the way?
So I have a combobox that pulls values from a table. I am being requested to do an update to the database which means that I have to filter some values on that combobox based on a field on a previous form.For example:The main form is called "frmcontacts" and has a table field called "txttypeofcustomer" which can have two values: "customer" or "dealer".
The form that contains the combobox is called "frmnewissue" the combobox is called "cmbissuetype". "frmNewIssue" is opened through a button on "frmContacts". This combobox pulls the values from a table "tblissuetype". The values in that combobox are then: "Repair" "Return" "Question" "Replace".
If the value on txtTypeofCustomer is "Dealer" then all the values for the combobox remain visible. If the value of txtTypeOfCustomer is "Customer" then "Replace" cannot be visible or has to be greyed out.
I have a form with a textbox, listbox and a button.
my textbox is based on one of my tables, and when I enter a value (which is saved in my table) in my textbox and press enter, certain values in my table goes to my listbox, and my listbox will just additem whenever I do same thing in my textbox all overagain.
how can I subtract listbox.column(1) items in my table field "Item_Quantity" where my Listbox.column(0) is equal to my table field "Item_Description".
My Listbox column count property = 4 Row source type = value list
we are moving our database from access 2007 to access 2010. I have a query where the results are shown in a pivot chart. when i try to add field items to the pivot chart, the chart goes blank. It works fine in access 2007. none of the columns have illegal characters in them and i tried recreating the form from scratch and that didn't work either.
Appreciate some help on a update query if possible. In this query I wish to total two sets of figures in about five seperate queries and then export them to another DB. the Dsum function is fine and will produce a result but that result needs to pick up the record ID (called PlanID) which is the primary key for the records being updated. I have struggled with this a bit as no matter what I did the PlanID field would not even display in the query results.
The section of code that is highlighted will now display an empty field and as such the rest of the query is blank. If I manually insert the PlanID figure into the record that the query is updating the other figures will appear.
How can I change this query to add the Planid to the update after it gets it from the customers form?
I would like to know whether it's possible to retrieve the field names, in order to display them in table format within a html page. I would like to dress the table with the cell data together with field names as 'headers' for each column.
Here is a snippet of what i have manages to produce. Currently, it displays all the entries that coincide with thier field names. The inly thing i wish to do now is to display the field names:
<html> <head> <title>date</title> </head> <body > <h3>Try It Out - Sailors Table With a Counter</b></i></font></h3>
<p><br> <% Dim oRSeofc Set oRSeofc=Server.createObject("ADODB.recordset") oRSEOFc.Open "People", "dsn=20527796a" oRSeofc.MoveFirst response.write "<table border='1'>"
Dim PersonCounter PersonCounter = 0 Do While Not oRSeofc.EOF PersonCounter =PersonCounter + 1
I'm having an issue with pound signs in my table for specific items. I've create a DB that when you click a command button it opens a form and in the form it has a notes field. In the notes field once where i had data is now pound signs.
The DB is setup so when you click another command button to close out, it saves the record. It works 98% of the time but the i'm concern about the other 2% of the time.
I'm creating a Form called Pharmacy where I can select a [Diagnosis] from a combo box in the form. This combo box source references a table called tblDiagnosis where each Diagnosis also has a multi-valued field called [Indications].
The Pharmacy form also has a multi-select listbox with all possible Indications. I would like to write a VBA code such that when a Diagnosis is selected, the Indications for that Diagnosis are automatically selected/highlighted in the listbox on the form as well.
As such, by selecting a Diagnosis, all the indications attached to that Diagnosis will automatically be selected; however, if additional indications are needed, they can still be selected afterward.
I know that there is a function Me!Listbox.Selected(i) = True, where i is the row of the entry in the listbox. However, the i in the listbox does not correspond to the ID of the Indication. I think that if there is a way to select listbox items by name, that would be much more efficient.
The reason I have a listbox, is because this listbox of Indications then references another table called tblDrugs where all drugs that are approved for the selected indications selected are filtered and displayed.
How would I create a relationship between a subform and a coverage field such that when i multi select items in the subform, it will show what items are selected in the coverage field as in the example.
I have a combox with the following query in its RowSource: SELECT [Error Codes].[ID], [Error Codes].[ErrorCode], [Error Codes].[Active] FROM [Error Codes] ORDER BY [Error Codes].[ErrorCode];
I now need to change the query's Rowsource via code and so far I have figured out that this works:
However, the table "Error Codes Missing" also has a field called "Active" which I need to take into account; If the error code is not active then it should not show up in the combobox.
I know how to do that in the original RowSource, but how do I do it with code?
I have a field in a form which displays the Sum of 10 values from other textboxes. I want to display the values of that calaculated field in a report and somehow I am stuck. I am running the report based on a query. How would I get those field calculation values to display in a report?
Hi. Back again with a badly designed number of tables that I inherited. Anyway...
I have 20 tables each with an ID field and a dozen-or-so text fields. - (I planned on using a master report with sub-reports, but perhaps I am ahead of myself here.)
I need the user to enter the ID and a value which would only appear in one of the dozen+ fields for that record, in that table. I set up queries with parameters for the ID and the desired text value. However, when run, I get the correct client record, but also all of the fields for that record... Not only the field with the desired value.
Is there a 'simple' way to do this knowing ho wmany possible fields froma ll of the tavbles would have to be searched? And not all tables have the same field names!
