Update Query ctnd:

Hello,
I have tried the code from Arvin however I now get 2 paramater
prompts. The 1st is for "transactions.Datefield" and the 2nd  "Please
enter Waiting or Pending"

UPDATE transactions SET transactions.Status = "Cleared"
WHERE (((transactions.datefield)<Date()-10) And
((transactions.Status)=[Please enter Waiting or Pending]));

Can I run this procedure atomatically when the database opens without
the prompts & user input

Thanks

0
myxmaster
4/29/2007 11:22:26 PM
access 16762 articles. 3 followers. Follow

1 Replies
842 Views

Similar Articles

[PageSpeed] 21

The first prompt implies that datefield isn't the actual name of the field 
in your transactions table.

The second prompt is because you're telling it to prompt you: that's what 
"[Please enter Waiting or Pending]" is (an implicit parameter).

If you expect to find the literal string "[Please enter Waiting or Pending]" 
as the status (complete with the square brackets), you need to put quotes 
around that string.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


<myxmaster@hotmail.com> wrote in message 
news:1177888946.210718.180080@h2g2000hsg.googlegroups.com...
> Hello,
> I have tried the code from Arvin however I now get 2 paramater
> prompts. The 1st is for "transactions.Datefield" and the 2nd  "Please
> enter Waiting or Pending"
>
> UPDATE transactions SET transactions.Status = "Cleared"
> WHERE (((transactions.datefield)<Date()-10) And
> ((transactions.Status)=[Please enter Waiting or Pending]));
>
> Can I run this procedure atomatically when the database opens without
> the prompts & user input
>
> Thanks
> 


0
Douglas
4/29/2007 11:31:12 PM
Reply:

Similar Artilces:

Query
I have a transaction # for each record in my main form with a subform "Approvals" and "Checkouts". I created a query to show the sum of all Approvals and all Checkouts by Transaction # for each record. (one for Accruals and one for Checkouts). All approvals and checkouts come up in these queries. I want to create a report showing, per Cost Center, the $ amt of Approvals, the $ amt of Checkouts, and a calculated field to show the remaining value. The report comes up with all approvals and checkouts per cost center, however, if there is an approval that does not hav...

Tab control question about updates
Searched but couldn't see this discussed. I have a form with 5 tabs. Data can be loaded three ways, manually entered, imported from a text file, and imported from a Word template. On one of the tabs (the second) when the user enters information such as a social sec. number (SSN) that matches a file already in the system, a flag goes off and alerts the user. Works fine. On the import functions, the data is loaded and appears correctly, but when the user moves to the second tab where the SSN number appears, it does not set off the flag when the page opens. I can make it happen with a c...

Setting random number range based on query, not table
I have a "quiz" form that randomly pulls up questions from my table. It works fine, and I don't mind that it repeats questions. This allows me to sit and review for as long as I want. The problem is, that I want to use criteria in a query to limit the available questions. If I base this form on a query and limit the number of questions, the code I am using is still setting the maximum number as the number of records in my table. In the past, I had a similar database (which I have lost) that would open the form, set the selector to the last record available to the form,...

UPDATE using SUM
I have a stored procedure where I am trying to update a temp table in the flow of the sp. I have tried the code below but it gives me an error "Incorrect syntax near the keyword 'GROUP' and I understand the error but don't know how to get around it and still get sums. Can anyone help? Thanks. UPDATE #tempInventoryAnalysis SET [PTDSalesQty] = SUM(CASE WHEN MGB.dbo.tblArHistHeader.InvcDate BETWEEN @StartDate AND @EndDate THEN MGB.dbo.tblArHistDetail.QtyShipSell ELSE 0 END), [YTDSalesQty] = SUM(MGB.dbo.tblArHistDetail.Qt...

Update Download problem
I downloaded the latest update for Outlook 2000. I did NOT have the installation cd handy. Okay, usually no problem, the instal quits. However this time, it did quit, yet now I am unable to open excel. It launches the instal program. The download was for Outlook. It has me boggled. Outlook, Word and Access open with no known problems. I have tried to open excel different times/ways. It continues to try to load the update. Any reasoning on the subject??? Thanks* ...

TargetInvocationException in Update Invoice in Microsoft Dynamics
Hi, we get the follow error mssage when we try update the invoice object. But, in the same class, we update the salesorder object with sucess. Obs. Our code is a custom page. Any ideas what this could be caused by? Tks Clau >Crm Exception: Message: , ErrorCode: -2147197184, InnerException: System.Runtime.InteropServices.COMException (0x80045F00): <details></details> at Microsoft.Crm.Platform.ComProxy.CRMInvoiceClass.Update(CUserAuth& Caller, String InvoiceId, String InvoiceXml) [2006-10-10 12:16:47.4] Process: w3wp |Thread: 8708 |Category: Platform |User: d12c7...

How do I set up formulas to update graphs dynamically
Hello, I have the following information I need to graph date Feb-08 Mar-08 Apr-08 May-08 Jun-08 Jul-08 burn 116 23 254 inventory 3035 3011 2757 The "date" is the x-axis label and the "burn" and "inventory" are the values on the y-axis. If the values for burn and inventory are blank for a particular date, I don't want the information plotted. As I add values for each date, I would like the graph to be updated automatically. I understand I need to define a named range and use it in the graph, however, I have not been successful in doing this. Also, even t...

Crosstab query totals
Need some help, I have a crosstab query that returns the following data see sql below, what I need is to total all gearbox types that are R and 37 and R and 27 and R and 47 etc. an example R and RF 27 total = 492 <> 1 2 4 SubTotals R 27 20 26 303 12 361 R 37 6 66 307 8 387 R 47 8 87 424 2 521 R47R 37 2 2 R57R 37 3 2 5 R67R 37 1 1 2 R77R 37 19 19 RF 27 4 17 109 1 131 RF 37 1 11 76 88 RF 47 1 25 39 65 TRANSFORM Sum(InspectionLog.Quantity) AS SumOfQuantity SELECT Sum(InspectionLog.Quantity) AS SubTotals, InspectionLog.GearboxType ...

location of web query
I have a file that refreshed 2006 data that I am now changing to 2007 but I cannot recall where a web query is on a certain worksheet, is there an easy way to find in which cell the query resides? ...

Access 2007 SQL Pass Through Queries
I have an Access 2003 mdb that I use to connect to a SQL 2005 database. When I open the application in Access 2007 it errors on using SQL pass through queries. I see that pass through queries (amongst other options) have been disabled under Access 2007. However it seems that they are enabled under certain circumstances so I've been trying to get them working by changing the security settings. The queries are still failing after setting the following: - Sandbox Mode to 0 (turn off sandbox mode for all apps) - Applied a recognised Digital Signature - Trusted the Publisher - Trusted the loc...

multiple fies updating one file
Hi, How would I setup an excel sheet that needs to collect numbers from other excel sheets. The rows and fields numbers will keep growing. I know how to reference a cell in a file but not sure how you can quickly create a reference to a range of cells and how about when the range keeps growing. Thank you Hi Vic; You can select and copy the range you want to link, then go to then go to the receiving sheet and click the top right cell. Click Edit / Paste Special and select Paste Link. This will link the entire range. If you happen to be running Office 2007 you can right click into the ...

Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook?
Wow! I tried this out, and it seems to work beautifully. It's odd, though, that I haven't seen this technique mentioned in any of the several Excel references that I've looked at. Basically, I have a workbook with several worksheets...one worksheet contains a large list with all the records. I wanted to set up the other worksheets to have certain functions...I wanted them to use only certain columns from the main list, and to contain only certain records from the main list that met specified conditions. This is the best solution I have found so far - i.e., creating database q...

pasting or moving formula cells without updating formulas
I have a flat spreadsheet with a results page at the end. The results page contains a set of formulae which refer to various cell locations within the body of the spreadsheet in order to return statistical results based on the values in said cells. Now I'd like to add more data to my spreadsheet, so i need to make it bigger; however, when I copy and paste, or select and drag the cells containing the formulae, Excel updates the formulae so that they refer to different cells which bear the same spatial relationship to the formulae as the original referees did before the formulae were ...

Task Update in PWA 2007
Hi, Is it possible to enter some actual in the My Tasks page of PWA 2007, for example to enter some percent complete, then to get the progress of that task in all the PWA views without any validation via Project Professional? If so, how? Thanks for your help! Xiao -- No, this is not possible. Even though the PM can create a Rule to automatically accept the task update, the system DOES NOT publish the update after accepting it. And if the update to the schedule isn't published, it is not visible anywhere in PWA. In conjunction with rules, a software developer...

Profits of IE 7.0 to 8.0 update if usage for adminstrative tasks only
OS: XP, Vista and 7 Case: IE used for administrative tasks only, never for web browsing What are the benefits of upgrading the IE from 7.x to 8.x in this case ? This FAQ answers common enterprise customer questions about Windows Internet Explorer 8. "Windows Internet Explorer 8 FAQ for Business" http://www.microsoft.com/downloads/details.aspx?FamilyID=e1da2f8c-f982-4cae-a83a-831188e57df9&displaylang=en --- Leonard Grey Errare humanum est kakii wrote: > > OS: XP, Vista and 7 > Case: IE used for administrative tasks only, never for web browsing &...

wrapping text in a query field
I set up the field in table to memo and tried entering a lot of information but when I open the report that field does not expand to show all of the entries. Can this be done in query and reports both. Thanking you in advance. Mary Lou On Dec 12, 12:06 pm, MaryLou <Mary...@discussions.microsoft.com> wrote: > I set up the field in table to memo and tried entering a lot of information > but when I open the report that field does not expand to show all of the > entries. Can this be done in query and reports both. > > Thanking you in advance. > Mary Lou Go to the p...

Can you help me with a troublesome query?
I'd like some help formulating a query for the following situation. The problem I actually have to solve is somewhat complicated, so I've extracted the essence of the problem into the simplified situation described below. While I know I could do this with an ugly cursor solution, I'm pretty sure that performance would be terrible as the table grows to a large size, which it will in the real problem I have to solve. So I'd like to hear your best ideas on how to solve this in a way that will scale to as high as a million records or more. Consider this table: ...

Updating Links in PowerPoint 2007
I have pasted in links from Excel 2007 to a PowerPoint 2007 file. Both files are on same drive. When I open the PowerPoint I get a message "Some linked files were unavailable and can't be updated." What is going on? How do I fix? In article <8D3689EE-EA7C-407F-B757-45CEDAE78976@microsoft.com>, Txm49 wrote: > I have pasted in links from Excel 2007 to a PowerPoint 2007 file. Both files > are on same drive. When I open the PowerPoint I get a message "Some linked > files were unavailable and can't be updated." What is going on? How do...

Using SqlDataAdapter for Insert but not Update?
I'm having trouble getting my SqlDataAdapter to NOT overwrite existing records in my SQL table. I'd like it to perform the Insert on rows that don't exist, but not update the record if it already exists. I have it set to ContinueUpdateOnError because I don't want it to throw an exception if a row already exists, I just want to ignore it. I'm clearly missing something... On Dec 11, 4:46=A0pm, Stu <stumor...@gmail.com> wrote: > I'm having trouble getting my SqlDataAdapter to NOT overwrite existing > records in my SQL table. =A0I'd like it to pe...

Query plan isn't doing an index seek
If you have the following: SELECT WorkID,VendorName = (SELECT VendorName FROM Vendor WHERE VendorID = ws.VendorID) FROM Work ws What I get is a Hash Match from 2 index scans. |--Compute Scalar(DEFINE:(dbo].[VENDOR].[VendorName]=[dbo].[VENDOR].[VendorName])) |--Hash Match(Right Outer Join, HASH:([dbo].[Z_VENDOR].[VendorID])=([ws].[VendorId]), RESIDUAL:([dbo].[VENDOR].[VendorID]=[dbo].[WORK].[VendorId] as [ws].[VendorId])) |--Clustered Index Scan(OBJECT:([dbo].[VENDOR].[PK_VENDOR])) |--Clustered Index Scan(OBJECT:([dbo].[WORK].[PK_Work] AS ...

Data/Values change when Query Analyzed in Excel
I have a query with 3 tables that represent master item list, count qty and as qty. When I look at the results in Query, all is well and accurate yet when I select Analyze with Excel, values change. For example, if a record shows zero qty in the as of field and zero quantity in the count field, I get a value in one or both of the fields that origianlly were zero. Other than linking the tables and grouping by master item list (to show all parts regardless of qty's), there are no formulas or expressions in this query. One of the tables is linked to a FoxPro table via ODBC driver. ...

Need help to structure simple (I think) query...
I think this is a simple query but I am not well-versed in SQL so I cannot figure this out. I have the following tables (I show only the relevant stuff): Documents PK_DocumentID Reviews PK_ReviewID FK_DocumentID FK_ReviewStatusID The relationship is one-to-many: One Document can have many Reviews. A Review has a Status: 1 = Not Started 2 = Open 3 = Closed For ALL the Reviews associated with a Document, there can be only ONE Review that has a non-closed status. Sometimes this condition does exist, which results in a data integrity problem, so I want to identify th...

Can 2 Union Queries Be used in Another UNION ??
I have created 2 separate Union Queries that work just fine. Can these 2 union queries be used in Another Union query (that will essentially combine the output of those 2)? Thanks very much, Kev -- Message posted via http://www.accessmonster.com Yes, as long as the two union queries are outputting data in the same structure and as long as the query engine doesn't decide that the combination of the union queries is too complex. Have you tried to do this? If so, did it fail? And if it failed were there any error messages? -- John Spencer Access MVP 2002-2005, 2007 Center for H...

FW: Try corrective update
--lkdjfwcphrqtozfp Content-Type: multipart/related; boundary="ygblvpmg"; type="multipart/alternative" --ygblvpmg Content-Type: multipart/alternative; boundary="lcxxrzqr" --lcxxrzqr Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Consumer this is the latest version of security update, the "November 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to help maintain the security of yo...

Local Software Update service for Office 2004?
Is it possible to run a local software update server for Microsoft Office on my LAN? With my Xserves, I am using Software Update Service for Apple updates, but can't figure out if there is a way to run a local software update service without using my Internet connection. I can get it from the central servers fine, but was wondering if I could run a local server to manage the Office Updates and monitor the update levels per station. Phillip Remaker wrote: > Is it possible to run a local software update server for Microsoft > Office on my LAN? > > With my Xserves, I am using...