I want to run a query that runs off of two list boxes on a form. However, I want both these parameters to filter a single field in my query. Why I want to do this is I have several types of accessories made by several manufacturers, both included in the "Description" field. I don't want to include an entirely new field for manufacturer because it's only 4 out of about 5 items that need this.
Is there a way this can be done or will I be forced to include a manufacturer's field.
I am trying to create a listbox that shows "calculated" dates for a two week payroll. I have a field in a table that is set to a start date (05.31.05) and another field that specifies the number of days between dates (14). I would like set a listboxes recordsource to show the start date and then calculate the next 4 dates using the first date (05.31.05) and the range (14) without actually storing any dates in the table. I can figure out how to do it if the values are stored, but I just want to know if this is possible to get the results another way.This may sound crazy and unecessary, but I want to know if this is possible without storing values in the table.
Listbox values: 05.31.05 - (first date from table) 06.14.05 - (first date + 14) 06.28.05 - (first date + 2*14) 07.12.05 - (first date + 3*14) 07.18.05 - (first date + 4*14)
I am currently using the column history to log the history of inputs into a memo field.
But i now need to swap how my database runs and now require a combo box to have the same history function, as this is for a status updates and i require users to only input certain status's.
I no that I cant use the columnhistory command with a combo box.
When selecting a status from the combo box it automatically update the memo field (which will be hidden on the form) so the column history function works.
I have a form that has three different pull down menus. When I click a button on the form it opens a second form that is filtered by a query. In order to allow the user to leave a pull down menu blank the query has 6 different columns. Three are headed with the field name and three are headed with the form reference, under the form reference it either says Is Null or Is Not Null. I have a total of 8 options in order to cover all possible scenarios. I would like to be able to add more search parameters but my query is getting ridicules. :confused: Is there a way to build the same thing with a code?
I need to be able to do an SQL update on this table to subtract 1 from the product_quantity field.
I could do this with two SQL queries (SELECT product_quantity, then subtract 1, then UPDATE table) but I'm sure there must be a way just to run one query which subtracts 1 from the field.
I have set up a parameter query in Access 2003 that asks the user for the "Section", such as "Admin", "Accounting" etc.
I need them to be able to respond to the prompt with more than one section if they want- sometimes just one, sometimes two or three or four.
So they can get "Accounting" and "Admin" both in the records that are returned.
The code: SELECT T_ElainesMaster.Section, T_ElainesMaster.Login, T_ElainesMaster.Workstation, T_ElainesMaster.NT, T_ElainesMaster.Barcode, T_ElainesMaster.[PC model], T_ElainesMaster.[Emp Name], T_ElainesMaster.[swap or not], T_ElainesMaster.[Exp Date] FROM T_ElainesMaster WHERE (((T_ElainesMaster.Section) Like [What section would you like to include?])) ORDER BY T_ElainesMaster.Section;
Hi Guys, I'm new here and fairly new to anything indepth with Access.
I'm using Access 2003 and have a table which has a number of customer records. The two relevant fields are the CustomerID which is a simple integer and OrderDate obviously this is a date field!
What I need (to link in with the other forms) is a query that will return one record for each CustomerID it should be the most recent OrderDate.
I've had a few attempts and done a couple of searches but can't seem to find exactly what I'm looking for. If someone could point me in the right direction I'm happy to play with sample code and read up on bits other people have done (I tend to learn better that way) rather than just being given some code!
I am designing a database in Access 2010 to handle customer discounts. The problem I am facing is that the discount can vary by customer. One could be a straight percentage based on certain sales totals, even the percentages can vary by customer, and some customer discounts are tiered. I was hoping there was a way to handle all through one query, because there are so many variations, it would require several queries if I separate by each calculation available.
Is there a way to set up a table listing the customer and their corresponding calculation expression then running a query using that table and the sales data table to do all the calculations. Or is there another way to handle this level of complexity in Access? The end result I would like is one data set with each customer and their calculated discount.
I have a form with several fields and combo boxes on which the user will select different criteria to generate a report.The default value for each of these fields/cbo's is "*", so if the user changes nothing from the default, I execute a basic "SELECT *" query.
If however the user enters some value (selects a specific date range, customer number, salesman,etc), I would like to 'easily' modify my query to accommodate the entered search criteria.Trying to construct SQL when there are many search options like this is difficult. I took a stab at using QueryDefs but I'm struggling to find example VBA code as an example.
I am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. Here is the code that I currently have:
Code: Private Sub Command33_Click() Dim strEmail As String Dim strMsg As String Dim oLook As Object Dim oMail As Object Set oLook = CreateObject("Outlook.Application") Set oMail = oLook.CreateItem(0)
[code]...
There are two issues I keep running into:
1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.
2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is: one paragraph a blank line a hyperlink to a website a blank line another paragraph
I am trying, and failing quite spectacularly to create a single query that will contain multiple expressions on the same field.
Please see attached jpg for example.
SQL: SELECT Count(StockData.status) AS FaultyPCs FROM StockData HAVING (([StockData].[Status] In ("Waiting Repair") And [StockData].[DeviceType] In ("Thick PC")));
What I want to be able to do is then add additional expressions to filter and count in the same way for "Thin PC" "Thin Laptops" "Thick Laptops" and so on.
But when I add one of these expressions to the next column in the builder, it seems to apply itself to the expression to it's left, causing that one to now give a result of 0.
Is there a way of separating one from another or do I have to create a separate query for each expression and then use another query to pull them all together (as I have done in another situations - but this will involve creating probably 20 separate queries.
I'm creating a database for my wife to use in her work, one of the fields is dates visited and the user should enter "00/00/0000, 11/11/1111, 22/22/2222, 33/33/3333" in this format. The field is a large text format because there could be anything from 1 date to hundreds. What I need to be able to do is in my query I need to enter 2 dates and have access return a how many dates exist between those 2 date values. eg It will pop up a box asking for start date and then another asking for end date and then it has to return the count total of how many dates exist between those two date values. I don't know VB and have had only basic training in Java and C# none of which involved Access. The only way I can think of doing it is to have access count the number of commas within the date field required but I don't know the formula required assuming it can even be done.
Basically, what's the best practice or how do we store a query's value into an array then checking what the max or min value is and how to check if let's say "4" is in the array?
I would like to run a query that allows we to enter as many criteria in one field as I want
For example
I have an order for wheel for a car order no "1234" then another order comes in for another wheel order no "23456" not the same wheel type
Each wheel could contain 10 parts to make the wheel both orders could contain 5 parts that are the same
What I want to do is enter as many order numbers as I want then when I run the query and it groups all the parts together for me to order in that query.
Hello I am new to Access and slowly finding my way round.
I have built 30 queries which eventually build two Union Queries of which each query as a slight different criteria.
In all 30 queries there is an indentical Field name called Arrived. What i want the user to be able to do is change the criteria in the Arrived figure throught a message box. So far the criteria is in the field is:
>=date()-3
This will show me everything 3 days ago. What i want to do is to let the user check what happened 5 days ago 10 days ago. I know i can create the following to solve the message box:
>=date()-3-[Enter in the days less than today]
Is there away to update all 30 queries at once with the same code?
Hi There, first time on here, so hopefully i can get an answer to my problem.
i am trying to split data in a sigle column accross multiple columns. basically i have a table like the one below:
Member DATA A 34 A 65 B 234 B 456 B 22 C 88 X 6 X 34 Y 23 Y 353 Y 25 Y 9 Z 45
so what i want the query to do is to show a list of Unique Members, and then split the data column so it show an entry for each Member accross mulitple columns.
is this possible? i have very limited access knowledge, so a comprehensive answer will be very much appreciated.
I am trying to make a user friendly form where the user can select mulitple items from a list box and then from that selection this info is listed in another table (Form) where they can add additional info.
For example,
List box lists Products to be made (1,2,3,4) If I choose item 3 and 4 i want them to be listed in a seperate form where I can add more info (quantity). Any help or other suggestions is appreciated.
I have created a form in access that contains a multiple select list box and a command button "New Record," that creates a new record. I select values in the list box for the current record. When I click on the "New Record" button, the values that I selected in the previous record are still selected. How can I reset the list box so that no values are selected when I create a new record?
I have about 36 names and each of those names appears anywhere from 2 7 times each. Each name entry has a comment1 and a comment2.What I would like to do is create a form that allows me to choosea name and the display the comments 1 and comments 2 in text boxes.
I have made the form to allow me to choose a name and it shows one of the comment1 and one of teh comment2 in the adjacent text boxes as planned but, I want all the comment1s for Bob to show in the text box for comment1 and all of the comment2s for Bob to show in the Comment2 text box.I believe I need to concatenate the results in the comment1 and comment2 boxes, but I do not know where or how I would do that.
I am trying to creat a DB that can potentially recieve input from a serial port which has a unique ID on it and updates the corresponding feild within the DB. I don't need to demonstrate full functionality (although if I have time I might give it a go!).
What I want to show is that the DB can accept a unique ID from this external source. The ID will just be to identify a user and a peice of equipment, potentially the feilds in the table would be updated with information like, time of session, duration, speed. I am assuming I may need to use VB to do some programming and create an incoming event. Just wondering what the most logical solution is. I want to bring up an interface when an input is recieved, like a user interface or pop up which says eg. "Hello Jim". Then it is possible to demonstrate that they have been detected.
Hi all. I'm working on a temporary database (Access 2003) for work until we get our system validated. Is it okay or acceptable to have a single-field table or do I need to throw an autonumber on there for the key? I really just need this table to populate a combobox/listbox in the form. I don't want to to use the "OnNotinList" with the combobox so I need to store them somewhere. Thanks!
I am fairly new to Access, and need some help with what I thought would be an easy project. I have an Acceess table that contains data on settings for machines in my plant. There are columns for part #, Machine #, part type, etc. All in all there are about 20 different columns. Now what I need to do is have a form where a user can enter one or two fields, and print a report that contains all the information from all 20 columns.
For example, the user would enter Part number "AB1234" on machine "15-1" and would get the following data from the table:
Part # part code Equip. Temp height etc AB1234 A 15-1 100 5.3 ... AB1234 B 15-1 200 6.3 ... AB1234 C 15-1 250 7.3 ...
They could then somehow choose exactly which record they wanted (example : ABC1234 B) and print a report that contained just the data from that selection.
I hope I explained this clearly enough. Basically I want a user to be able to enter 1 or 2 selection fields and then be able to print a report that contains info from 1 row in 1 table. Any help would be appreciated. I am somewhat new to this and havee a basic understanding of Access and A moderate understanding of VB. Thank you in advance for your help.
