Queries :: Updating Dates - Query Turned Read Only
Mar 28, 2014
I have a query that i use to update dates relating to jobs and at what stage of development they are in. It worked fine until I started to pull in some data (Prism) to update the user when the project is planned in to sample and when mass production starts (the field this is displayed in is not enabled). when I've incorporated this it I have no longer been able to update any field and my query has turned read only.
SELECT dbo_NPD_Container.PENumber, dbo_NPD_Container.JobNumber, dbo_NPD_Container.ProjectTitle, dbo_NPD_Container.Status, dbo_NPD_Sales.InitialQuery, dbo_NPD_Sales.InitialQueryDate, dbo_NPD_Sales.SpecificationIssued, dbo_NPD_Sales.ApprovalRecived, dbo_NPD_Sales.TuDrawIssued, dbo_NPD_Sales.TuDeliveryDate, dbo_NPD_Sales.TrialRequestIssued, dbo_NPD_Sales.SampleDate,
[Code] .....
View Replies
ADVERTISEMENT
Sep 24, 2007
I need to build a SQL statement (several actually) which will be appending/deleting/updating data.
I would prefer to turn off warnings so that I don't have to click on "Yes" 9 times per operation.
However, I don't want to just turn off warnings, as if a certain operation does anything but append/update/delete 1 record, I need to know. Is there a way to determine how many records are going to be appended/deleted/updated and if it's not 1, then run some other code?
View 1 Replies
View Related
Sep 13, 2007
I am new to Access, so bear with me.
I am using Access 2007.
I have 4 tables created, and they are breaking out information for recording files that are being created from a PBX.
The tables are created as thus:
tbl_1
Key:ext_id | extension
tbl_2
Key:loc_id | location
tbl_3
Key:f-type_id | filetype
tbl_4
Key: mass_id | filename | date | loc_id | ext_id | f-type_id
Now I have created the relationships between the tables for the various ID's. My issue is the following:
I will be automating this process, so when the data is imported to the various tables, I need the relevant keys from tbl_1 tbl_2 and tbl_3 to be added to tbl_4 when the new data is brought into the tables.
As to the process of importing the data, I was looking at using PHP to facilitate this. Any ideas, help or direction would be greatly appreciated. I have looked around the net for an idea of how to solve this, but, being so new, I am not exactly sure how to word the question.
Thanks!
View 2 Replies
View Related
Aug 25, 2013
I have a two-column list box where the user selects multiple Test Names and Test Measures. Through VBA, I loop through and create a string of the selected items and store into two seperate variables, one for each column. I concatenate with the "In" and some parenthesis to end up with the following:
In(ELA,MEAP,Star Math)
In(DRA, Math, PercentileRank)
I place each of the In statements into two seperate dummy text boxes on the form. Then I point the query criteria to these text boxes.
When I run the query, I get nothing. However, if I copy and paste the In statements above from the text boxes directly into the query criteria, I get the desired results.
I changed the code to create an "Or" statement (e.g. "ELA" OR "MEAP" OR "Star Math"), but still the same issue.
why the query will not read from the text boxes on the Form?
View 4 Replies
View Related
Jan 6, 2014
I have a table and a query both of which have the same [Resource ID], [Resource Name] fields.
I am trying to update the [CBL_1_Date], and the [CBL_1_kW] fields in the table with the [CBL_x_Date] and [CBL_x_kW] fields of the query. The problem is that when I try to run the query I keep getting "Operation Must Use and Updateable Query" error.
here is the SQL Code
UPDATE tCBLAggregation
INNER JOIN qBestof4CBLs_avg
ON (tCBLAggregation.[Event Date] = qBestof4CBLs_avg.[Event Start Date]) AND (tCBLAggregation.[Resource ID] = qBestof4CBLs_avg.[Resource ID])
SET tCBLAggregation.CBL_1_Date = [qBestof4CBLs_avg].[CBL_x_Date], tCBLAggregation.CBL_1_kW = [qBestof4CBLs_avg].[AvgOfCBL_x_kW];
View 1 Replies
View Related
Jun 23, 2015
I have a query based on a table that has a column called Calculated Card Expiration:
IIf([Card Type Issued]="PIV",[Issue Date]+1095,IIf([Card Type Issued]="Non-PIV",[Issue Date]+1095,IIf([Card Type Issued]="Flash",[Issue Date]+365,Null)))
It worked fine when I ran the query the first time, but now when a Card Type Issued and Issue Date is added or changed it doesn't work.
View 5 Replies
View Related
Sep 26, 2013
I have created a query which is based on 2 tables.I now want to use the query to update content as needed in one of the tablesWhen I try to do this it says, Operation must use an updateable query..I used the update feature but it still says must be created in updateable query.
View 4 Replies
View Related
Aug 9, 2014
I'm working on a project management database for my fathers small business. Data Entry goes as follows: Users enter a Work Order, and all the parts that must be created for that work order. The parts have different processes (tasks) done to them to be created. The database runs a simple system like this as we want it to work. I am now attempting to allow handling of more advanced work orders. Specifically, some parts require the completion of multiple other parts before they can be created.
Here is how the database should handle these events:
1) User clicks button named "requires other parts" when entering a new part.
2) On click a subform is opened that allows the user to enter which other parts need to be completed first.
3) Once all of the parts are completed the next part can begin.
This is my current solution:
1) A query calculates if a part is complete
2) Some form of logic looks up to see if all the required parts are completed. If the answer is yes, a field named ready changes to "Yes" and if the answer is no, a field named ready changes to "no"
View 9 Replies
View Related
Apr 7, 2005
Hello there
I have got a 2 problems:
1) i am creating a booking form with 'arrivaldate' , 'departuredate' , 'roomnum' and i need an error screen to come up if the roomnum has already been booked in between the arrival and departure dates which will be on the bookings table
2) i also need to work out how much this costs automatically in 'amountdue' i can get the date difference and times it by the cost but it does not store this into the table
Thank you for reading this and your help will be most greatful
Carl
P.S i am very new to access :o sorry :o
View 8 Replies
View Related
Jul 25, 2006
Hello,
I have a dropdown menu which has a lisrt of dates (mmm yyyy), and when one is selected it updates 2 boxes with 01/mm/yyyy and 30 or 31/mmm/yyyy
Private Sub cmbDate_AfterUpdate()
Me.frmData.Form.RecordSource = "SELECT * FROM qry_NatxReg WHERE [Company Name]='" & cmbCompanyName & "' AND [Time of Attempt 1] BETWEEN #" & txtDateFrom & "# AND #" & txtDateTo & "#"
Me.frmData.Form.Requery
End Sub
When i choose June, it works fine and gets all the june dates from [Time Attempt 1]
But when i Choose July, it includes June.
I only have data for June and July as it's still in testing.
I can't wrap my head round it!!
View 12 Replies
View Related
Jun 12, 2015
I have a table with tasks in which are allocated to specific staff members. The start date of the task is currently inputted by the user and then an estimated time frame is inputted which automatically enters the planned end date.
When a task is complete a Yes/No box is ticked so that these tasks don't show up on the current list anymore.
I wondered if it is possible, for the second task allocated to a certain person to have the start date automatically inputted depending on the planned end date of the precious task.
So if the planned end date was 12/06/2015 then the start date of the next would be 12/06/2015 and so on.
View 2 Replies
View Related
Jun 5, 2013
Only one table : Customer ID,Customer name , subscription period, subscription start date and subscription end date.
I need values where subscription start date is between date1 and date2 and subscription end date is between date 1 and date2.
Detail explanation:
customer1 subscribed from 2-feb 2012 to 2-feb 2013
customer2 subscribed from 5-aug-2012 to 5-aug-2013
customer3 subscribed from 1-Jan-2013 to 31-Dec-2013
when the user enters date 1 as 1-jan-2013 and date 2 as 30-Jun-2013
Even though the subscription start date is not within the range entered by the user.I need to get all three customers because the subscription enddate falls within the range.And customer 3 is also required as subscription start date is within the range.
View 2 Replies
View Related
Jul 3, 2013
I have a query that is based on three queries. 2 of those queries have the same fields but return data for different dates. The 3rd query returns the dates needed. In other words, I am trying to return all results from both queries. these are the results I want:
Report_ID Report_Date Contents Approvers
Standard ops 5/1/13 daily operations rpt tom tomlin
royer manuf 7/1/13 syndication rpt rob davis
etc.
These are for year-t0-date. there is a YTD query that returns all dates needed. The other two queries return records on different dates, on is for dates <5/5/13 and the other is for dates >5/4/13. I have listed the SQL below. It returns the correct records, but for the Record_ID it returns -1 in each field instead of the report_ID text. So, I need to get the other fields to show up correctly.
Code:
SELECT [Daily_rpts_YTD_due_before_5_5_2013]![Report_ID] Or [Daily_rpts_YTD_due_after_5_4_2013]![Report_ID] AS [Report ID], due_dates_Daily_YTD.Due_dates
FROM (due_dates_Daily_YTD LEFT JOIN Daily_rpts_YTD_due_before_5_5_2013 ON due_dates_Daily_YTD.Due_dates = Daily_rpts_YTD_due_before_5_5_2013.Due_dates) LEFT JOIN Daily_rpts_YTD_due_after_5_4_2013 ON due_dates_Daily_YTD.Due_dates = Daily_rpts_YTD_due_after_5_4_2013.Due_dates
WHERE (((due_dates_Daily_YTD.Due_dates)=[Daily_rpts_YTD_due_after_5_4_2013]![Due_dates] Or (due_dates_Daily_YTD.Due_dates)=[Daily_rpts_YTD_due_before_5_5_2013]![Due_dates]));
View 2 Replies
View Related
Aug 28, 2013
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View 1 Replies
View Related
Feb 19, 2015
I have a few queries which are used to create reminder email on training which is due for renewal.Some training required reminder 6 months before 2 year expiry. I use this in the criteria for the training date within query:
Between DateSerial(Year(Date()),Month(Date())-18,1) And DateSerial(Year(Date()),Month(Date())-17,0)
Some training required reminder 3 months before 1 year expiry. I use this in the criteria for the training date within query:
Between DateSerial(Year(Date()),Month(Date())-9,1) And DateSerial(Year(Date()),Month(Date())-8,0)
My problem is with training requiring reminder 6 months before 3 year expiry. Using this criteria:
Between DateSerial(Year(Date()),Month(Date())-30,1) And DateSerial(Year(Date()),Month(Date())-29,0)
Doesn't show any results (although there is training which was done 30months ago, expiring in 6 months time).
Changing the -30 (months) and -29 (months) in above down to -22 & -21 shows records as expected, but anything below -22/-21 doesn't show any records.
View 3 Replies
View Related
Jan 23, 2014
I'm trying to filter an append query by a year selected in a combo box [cboYear] for a field [ProgramDate]. The AfterUpdate on the cbo filters the append query based off of the selection (or selections-I'm using multiple combo boxes on the form). I then run a report based off of the appended table for a report.
I set my criteria for [ProgramDate] the query to be:
Code:
Year([ProgramDate])=[Forms]![frm_rpt_Programs]![cboYear]
The above code isn't working, even when I just try to run the query while the form is open. It's still returning all records.
View 2 Replies
View Related
Jun 23, 2014
I want to create a cosstab query with dates for the next 12 months accross the top as columns and employee names down the side as rows. I then want to populate with data showing what each person is programed to do under the dates. For instance trainer 1 is delivering training from the 23/06-30/06 then designing a course from 02/07-10/07. that type of thing.
I could represent an activity using a colour i.e. yellow for delivery, green for design etc.
Also do I need to create a table with all the dates? Is there a quick way of doing this?
View 7 Replies
View Related
Mar 4, 2015
I have a list of dates and I want 1 query which counts the dates between numerous criteria. for example, colum 1; dates between 1/1/14 and 16/02/2014. Colum2 between 17/02/2014 and 15/04/2014.
I have attached a screenshot.
View 4 Replies
View Related
Nov 4, 2013
I have generated a table using a crosstab query and it gives the information as below
12/10 12/11 12/12 13/01
Part 1 1 2
Part 2 4 4 4
Part 3 5
So basically part, qty used, year/month used - it works fine up to this point.
Now I want to either add more columns to the crosstab query to count number of times Part 1 was used in last 12 months & 24-13 months
Or
Create another query to do this.
But I want the count function to start counting from current month to last 12 months and from month 13 to month 24 (in another column).
The problem I am having is that this query will run every month and I don't want to update the column headers (in query property sheet) instead I want query to pick this up automatically.
I use the design view to generate queries.
View 1 Replies
View Related
Apr 17, 2015
I'm working on a report that highlights employees when they are leaving on travel and returning on travel and my problem is that the report is only run on the weekdays. It highlights all employees that returned yesterday so for instance on Monday's report it only shows people that returned Sunday and not Friday and Saturday.
My first thought was to make an update query convert those days to Sunday in a new column on my table which would then still cause the employee to be highlighted on monday. I made a table with all Fridays and Saturday's in the year and then in the second column is the Sunday Date to update the new field.
I haven't been able to get the update query to work correctly and was wondering if there would be a much easier solution so a person wouldn't continually extend the weekend table manually.
View 3 Replies
View Related
Aug 13, 2014
I have a query expression that calculates the number of years between 2 dates using DateDiff. Here is the equation as it is:
BudgetedHQPCalc: Round(DateDiff("yyyy",[DateHired],[DateTerminated]),1)
What I would like this to do, is if the difference is, for example, 1.4, I want the number rounded down, if its 1.6 I want it rounded up, and if its 1.5, i want to use alternate rounding (1.5 down 2, 2.5 up to 3).
Is there an easy way to do this?
View 3 Replies
View Related
Sep 1, 2014
I have a table which list a load of items, one field is date and one field is time.I have a form with two date boxes and two time boxes, the idea is for the user to search between the two inputted dates and the two inputted times.This then runs a query for a report to be produced. The problem I having is getting the query to runs both criteria it returns nothing.here is the Where part of the current SQL.
WHERE (((tblIncident.IncDate) Between [Forms]![FRM_SearchMulti]![txtrepdate]
And [Forms]![FRM_SearchMulti]![TxtrepDateB]) AND ((tblIncident.IncTime) Between [Forms]![FRM_SearchMulti]![txtreptimea]
And [Forms]![FRM_SearchMulti]![txtreptimeb]))ORDER BY tblIncident.IncDate, tblIncident.IncTime;
View 7 Replies
View Related
Oct 9, 2013
I have a query and I want to make a certain range of dates for the query to run. For example: from 10/6 to 10/7. What would be a parameter for this range of date? Should I put it on a query?
View 11 Replies
View Related
Mar 19, 2013
I have a table which is used to store info regarding medicines dispensed. I also have a corresponding Form to enter data. The fields in Table are
ID- number
Dispensed Date- date with dd/mm/yyyy format
Dispensed Type- text
Quantity- no. of days
Next Collection Date- date with dd/mm/yyyy format
The Form also contains same fields but it has a calculated field for ( Next Collection Date) where i calculate date using Dateadd function. Also the form has a Datasheet view. So records are added when I press Tab or Enter at last field.
Now the problem is the calculated dates arent getting updated in the table. And this is a huge problem as i have to run a query later where i will put a criteria on Next Collection Date.
View 3 Replies
View Related
Nov 6, 2013
So I currently have a table that tracks medication activity for patients called tblMedRanges. The Fields are the ID, StartMedication, EndMedication, Med ID/Medication Name.
In the End of Medication, some patients are not currently active so they have a set date/time field property. What I have for right now is the default value is set to
Code:
=date()
So that when staff adds a new medication it is set to the current date. These patients are supposed to be active, but it doesn't update the default value. I want it to continue to update to the current date every time the database is open.
I have three options that I know of(or think I know of) to update but can't commit to one in particular :
1)I want these individual records in the field to update the current time for today and was considering using some kind of data macro code using before update.
2)The other idea I had was splitting up the field into an EndOfMedication field that would display a value only if their inactive, and an Active field (text, value list active/inactive choices) that would be used in a query to generate the current date as med field. This would take some crafty query work with comparisons later on to generate list of drugs that they were on for each visit.
3) set to text field and convert the field to date field for comparison in queries.
View 2 Replies
View Related
Jan 17, 2014
I have a date field NxtAPayDate that I need to update to the same day in the next calendar quarter. I have an update query set up using DATEADD but it does not change the dates. The table name is AutoPay
I am using the Query Design Grid. This is the SQL code behind the query:
UPDATE AutoPay SET AutoPay.NxtAPayDate = DateAdd("q",1,[AutoPay]![NxtAPayDate]);
When I view the results the dates are the same - original dates, when I try to sort the results, I get a message box with the following: syntax error, incomplete query clause?
I cannot get the dates to change by using a specific date either
UPDATE AutoPay SET AutoPay.NxtAPayDate = #1/2/2014#;
View 1 Replies
View Related
