Hi, I have created a field called age, where I have a formula that takes the date of birth out of the IDENTITY NUMBER and calculated the peoples age, I have then created a field where I would like to allocate groups depending on their age, i.e, 18-25 = Group1, 26-35 = Group 2,etc... I am having trouble with this, firstly, I cannot get the formulas to work individually and secondly, I get an error that says I have duplicate fields to update.
Was wondering if there is a way without building individual update queries, to update info in one field that has multiple criteria ?
Basically I need to change/update daily multiple ID numbers to new ID numbers, long story on why this needs to be done but for now I need to do it this way.
Example: 12345 update to ABCDE, 6789 update to FGHI, etc. These ID's are all within the same field in the table.
It works fine running each ID one at a time but was wondering if it is possible to do all these updates within one query or code ?
I wrote a database several years ago and recently pulled it out to give to a friend. The problem is, back then (not knowing better) I set the Employee table up with as a single field "NAME". Now, in order to make it effective, I need the Employee's name in four (4) parts (First, Middle, Last, Suffix).
I have several queries based on the "NAME" field and and would like to avoid changing all of them. I have a simple form "frmUpdateEmployees" that populates the Employee table and Name field. I was hoping to change the form and/or add a query that would be easier and more simple.
Table: Employee Field: Name Form: frmUpdateEmployees Queries: 16 that depend on the table and field above.
I need to update the periodtype field in my table depending on different values in the field Formtype- I am looking to do this without having to use VBA. I have the following fields in a table - I want to update the value of the field Period_type as follows -
When Formtype is 10-Q, update Periodtype to "Quarterly"
When Formtype is 10-K, update Periodtype to "Annual"
The current value of Periodtype for both formtypes is "Semiannual"
I want to create a form that allows users to update multiple fields for multiple assets. Below is what I came up with:
Ideally, I'd like the subform to be filled in by having the user select multiple Assets from the S/N combobox field which would then auto-populate the "Type" field. Then they would fill out the appropriate fields they want edited in the top part of the form. They hit save and magic happens. This would also be nice because only assets they want edited would be displayed (easier on the eyes) and no distinguishing would be necessary. To do it this way, I know I would need to use a temp table but I wanna avoid using temp tables.
I know I can do this by adding a Yes/No field in the "Asset" table, setting the "Asset" table as the subform's recordsource, and then putting a checkbox in the subform and allowing them to check the assets that they want to edit (which would also allow me to sort it instantly so that checked Assets are at the top of the datasheet for easy viewing), but I would like to know if there's a way of accomplishing this without the use of checkboxes.
I know I could also use a listbox and that allows them to multi-select items, but I'm not sure if that allows me to group all selected items at the top of the listbox for easy viewing of selected items. Plus it would involve a lot of scrolling (there are over 2k assets).
Code:Dim DAOdb As Database Dim DAOrs As DAO.Recordset Dim sSQL As String Set DAOdb = CurrentDb() 'select the record corresponding with the value in the hidden textbox sSQL = "Select * from table where field =" & "'" & Me.textbox.Value & "'" & ";" Set DAOrs = DAOdb.OpenRecordset(sSQL) With DAOrs .Edit 'update this field in the table .Fields("Status") = "Complete" .Update End With DAOrs.Close DAOdb.Close i want to be able to update all of the fields that match the textbox. as it is now, it only updates one. how can i loop through all of the records and update all of the ones that meet the criteria??
Hey guys- I have 2 identical tables. I want to update the data from Table1 to go into Table2. Each table has well over 70+ fields in them. Instead of handwriting out each [Table].[Field] in either SQL or the Designer- is there a shortcut to tell Access to grab all the fields from Table1 and update all the fields in Table2 (all the fields have the same name)? I just don't have the energy to type it all out- I figure there's got to be a way...
I know when you do an APPEND query in the designer- it will do this for you- but not the UPDATE query... Thanks!
Hello, I would like to update multiple records in one query. The scenerio is that i have 3x3 (3 in row and 3 in column) text feilds and i want to update it once, in one query after entering the data in text fileds. Can anybody help me please......????
When a unit has changed its type or area, then the db needs to be updated. My question is how to do it. Can I get a recordset by joining three table, and do the update in one go?
Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.
I would like to create a text box where I can input todays date, then I want to be able to select the tables I want to update with that information. So if I input todays date I can then update my Stock, Sales and Control tables with todays date. Tomorrow I would only like to update the Control and Tape tables with it as its the weekend. I been playing around with 'new date for next record' scenario but the problem is that on the weekend we would not input any data into some of the tables. Hope I've explained myself clear enough.
I am trying to setup a database for vehicle stock control. Im not sure if I have gone about this the right way as I am new to this but thus far it is working correctly except for one annoying problem. The database consists of so far
In the Vehicle Details table a stock number has to be manually added as this will be used for new stock as well as current stock (Number range from 100 - whatever) "IAWVehicleNo". This table contains all relevent data with reguards to make, model, bodytype, color etc. The sellers table contains the details of the seller Name, address, Phone, LicenceNo etc. The Buyers table contains employee names. The States table contains all Australian states. The Status table contains current vehicle status Retail, Wholesale, Wrecking etc. The Sold table contains the details of the person who purchased the vehicle if sold. The Safety details table contains a safety checklist for pre purchase inspections eg: Headlights yes/no checkbox, Headlight text field for any extra info. It also has a field for a safety Certificate No once the vehicle is checked and recieves a Safety cert. The forms are setup as frmIAWVehicleDetails frmIAWSellers Subform frmIAWVehicleSafetyDetails frmIAWVehicleSafetyDetails Subform
The Vehicle Details,Sellers,Sold & SafetyDetails tables all have the IAWVehicleID & IAWVehicleNo Fields but when the details are entered through the forms the IAWVehicleNo which is the manually entered number only updates to the tblIAWVehiclesDetails but the other IAWVehicleNo fields in the other tables remain blank. Any advice or help would be greatly appreciated.
I have an orders form and subform, ie one order can have several product records with the following firlds Product Ordered Received At the bottom I have a command button 'ReceiveOrder' What I want is on click of this button that the received field (that is 0 by default) will equal Ordered. ie If I had orderd 2 and I hit the button it would change the recieved to 2 as well. This much I have done with the following code Private Sub ReceiveButton_Click() Me.frmReceivingSubform.Form!QtyReceived = Me.frmReceivingSubform.Form!QtyOrdered End Sub
This work fine if there is only one product, but if I have more than one it will only update the selected record, is there an ammendment I can do that means all the product records on that particular order will update.
I've searched the forum for the answers but can't find the answer, can anyone help, Thanks
hi, i've a form with 2 combo boxes (month and year) and 2 buttons "save" and "get". I'm trying to figure out how to use that button to perform 4 tasks.
task 1:
when i select the month and the year from the combo boxes and i click the button "get", the form should populate the number of checkboxes equavalent to the number of days of that month. e.g: if i select feb 2008, i should have 29 checkboxes (due to leap year) or i select apr 2005, i should have 30 checkboxes.
assuming that i've done the date validation function.. like jan, mar, may, jul, aug, oct and dec should return 31 days, leap year etc.
task 2:
then how should i assign the date value to the checkbox? say if i check on the 5 th box, it should return me a value of 05/09/2005 (assuming i select sept in my month combo box and 2005 in my year combo box earlier on)
task 3:
how should i code the label for my checkboxes? i wan to have 2 labels on top of the checkbox. the first label to show the name of the day (e.g: mon, tue etc) and the second label to show the day (e.g: 1, 2, 3)
task 4:
not sure if this task is a tall order.. but nonetheless, i need to consult the experts here. let's say when i check multiple checkboxes at one go, how to update into the table i want?
e,g: i check 9 boxes (e.g: 01/09/05, 03/09/2005, 04/09/2005... 23/09/2005) then when i click the button "save", i should have nine records in my table designated to save the data.
I have a table with a field called "description" another field in fame table called "length of cable" In description field it contains among other things, the length in it . It may be at various locations in the test data in the field. The length may be in the form xft or xxft xx foot xxfeet xx feet..
I have another field that I need to put the length in a standard form like xx feet (The field will have different units in it later not just feet.) I am using update query with criteria of Like "*3ft*" Or Like "*3 foot*" Or Like "*3 ft*" Or Like "*3 foot" with update to (of the length of cable field ) set to 3 Feet The data has many lengths 1ft 2ft 3ft 4ft 5ft 6ft 7ft 10ft 12ft 14ft 15ft 20ft 25ft and one....
I would like to automate it to one query and hopefully avoid the problem I have when doing it as is, *5ft* also finds 25ft and 15ft incorrectly for the goal of the update. same on 12ft 14ft
' and " are the symbols for inches and feet. Also some of the data is in the form xx' or xx" I hit a wall on this due to the ' character need to do the same to that as well.
Any suggestions ''' before I lose any more hair ...
I have an update sql statement that isn't quite working properly.
My where clause has 3 criteria. *print = -1 *stDocCriteria (project_num and client_id)
Below is what the code looks like.
Code: updateSQL = "update tblTimedTasks set printed =-1, invoice_date = Now() where print = -1 and " & stDocCriteria
Add watch: so you guys can see "stDocCriteria"
Code: updateSQL = "update tblTimedTasks set printed =-1, invoice_date = Now() where print =-1 and ([project_num]= '140012' And [client_id] = 87)"
I want to only updates records that meet all three criteria. The above sql not only updates all records that satisfy stDocCriteria regardless if print is -1(true) or 0(false).
How to save both RLP120001 with status "OK" because normal form only can have one form with one data. Then, I come out with filter form which can view PO number RLP120001 but how to update both PO Number using filter form. In my filter form, save button only save above row which pr 123456.
I know there are numerous threads regarding dsum() on the forum, but I wasn't able to find the exact answer to my problem.
The root of my problem is that I'm trying to update a field on a table using dsum, which references another query to update the table. Although I have all of the correct keys from the physical table joined to the query in the dsum function, the code/ms access seems to ignore the joins. As a result, all payees are having their "vol" field set instead of a select subset.
Query (GetTxnVolAmtTR"):
SELECT p.payee_id, sum(txn_volume) AS vol, t.market, t.period_id FROM ft_payees AS p, ft_txn_summary AS t WHERE p.payee_id=t.payee_id And p.market=t.market GROUP BY t.payee_id, t.period_id, t.market, p.payee_id;
Update statement (references the query above):
UPDATE tmp_ft_component AS rc SET rc.volume = Dsum("vol","GetTxnVolAmtTR","GetTxnVolAmtTR.payee_id= " & [rc.payee_id] And "GetTxnVolAmtTR.market= " & [rc.market] And "GetTxnVolAmtTR.period_id= " & [rc.period_id] & "") WHERE rc.component_name='Total Revenue';
as you can see, I have all of the fields I want joined, but the code seems to ignore this. I've tried looking at this site: http://www.mvps.org/access/general/gen0018.htm , but haven't found my answer. Any help would be much appreciated!
I have two forms, both of which have a field called JobID (Form A is the primary Key and Form B is the linked field - Relationship: One to Many).
Form A = FrmJobs Form B = FrmPurchaseOrders
I have put a command button on Form A. When I click the button I need it to open Form B and then automatically fill the field JobID in Form B with the same value in Form A from which it was opened. I have tried the following:
Private Sub Command214_Click() Me.Refresh On Error GoTo Err_Command214_Click Dim stDocName As String Dim stLinkCriteria As String
[code]...
The above code works on my old database but not on my new database. I get the following message "Object does not support this property or method".
I have 3 peices of data that I am working with: Group number, plans and benefit elections. On my form I have fields for the group number and plan and 2 queries. When I enter a group number the first query displays a list of plan descriptions for that group. From there I can enter the plan description into the form's plan field and that runs the second query to give me a list of valid elections for that plan. This all works fine but I want to make the plan selection a little less tedious. What I would like is to be able to double click the query field housing the plan description and have it copied to the form's plan field. I could use the ID instead of the plan description but I work in a production environment and very key stroke counts so I would really like to have a simple double click process.
Is this even possible? If so how would I set that up? I tried using the double click on event macro builder but it does not seem to have this kind of option.
My form contains fields for group number and Plan. The same form houses 2 querys, one that pulls plans based on the group number and the other to pull elections based on the plan field (not the plan query). Would like to double click a plan within the plan query and have that description populated into the form's plan field.
tblTreatment contains Cost for different AppointmentTypes.
The above two tables are linked as 1-M relationship (1 Treatment can have Many Appointments)
Entering data in the tblAppointment - AppointmentType and AppointmentCost are the fields in tblAppointment. AppointmentType a drop down box, so that a specific AppointmentType can be selected.
Problem: Now depending on the AppointmentType selected the AppointmentCost field should automatically display the correct Cost.
