Need help with update sql plus filter

I have the following update sql (copied from the query design view)
UPDATE ListQry SET ListQry.ApprovalStatusID = 
[Forms]![OpeningForm]![Responsibility]
WHERE (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] 
And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID)>300)) OR 
(((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And 
(ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID) Is Null));

ApprovalStatusID is an integer
OtherStatusID is an integer

ListQry is the recordsource for my form. I would like to add the filter on 
the form to the Where statement. I can't get it to work. I think I'm having 
issues with the spaces and quotation marks and all that. Could I get help?



0
Utf
3/11/2010 9:21:01 PM
access 16762 articles. 3 followers. Follow

3 Replies
650 Views

Similar Articles

[PageSpeed] 49

The UPDATE statement is used to update data in the table, so I don't 
understand your objective.

"Clddleopard" wrote:

> I have the following update sql (copied from the query design view)
> UPDATE ListQry SET ListQry.ApprovalStatusID = 
> [Forms]![OpeningForm]![Responsibility]
> WHERE (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] 
> And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID)>300)) OR 
> (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And 
> (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID) Is Null));
> 
> ApprovalStatusID is an integer
> OtherStatusID is an integer
> 
> ListQry is the recordsource for my form. I would like to add the filter on 
> the form to the Where statement. I can't get it to work. I think I'm having 
> issues with the spaces and quotation marks and all that. Could I get help?
> 
> 
> 
0
Utf
3/12/2010 3:58:01 PM
>>I can't get it to work.
What is it not doing?  What is it doing it should not do?
Post examples of --
ListQry.ApprovalStatusID  
[Forms]![OpeningForm]![Responsibility]
ListQry.OtherStatusID
                                   --   where you say it does not work.

>>I think I'm having issues with the spaces and quotation marks and all that.
What 'spaces and quotation marks'?
 
-- 
Build a little, test a little.


"Clddleopard" wrote:

> I have the following update sql (copied from the query design view)
> UPDATE ListQry SET ListQry.ApprovalStatusID = 
> [Forms]![OpeningForm]![Responsibility]
> WHERE (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] 
> And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID)>300)) OR 
> (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And 
> (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID) Is Null));
> 
> ApprovalStatusID is an integer
> OtherStatusID is an integer
> 
> ListQry is the recordsource for my form. I would like to add the filter on 
> the form to the Where statement. I can't get it to work. I think I'm having 
> issues with the spaces and quotation marks and all that. Could I get help?
> 
> 
> 
0
Utf
3/12/2010 4:13:03 PM
"Clddleopard" <Clddleopard@discussions.microsoft.com> wrote in message 
news:C22F3797-5975-4914-9AE0-D41DC64F7A87@microsoft.com...
>I have the following update sql (copied from the query design view)
> UPDATE ListQry SET ListQry.ApprovalStatusID =
> [Forms]![OpeningForm]![Responsibility]
> WHERE (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility]
> And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID)>300)) OR
> (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And
> (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID) Is Null));
>
> ApprovalStatusID is an integer
> OtherStatusID is an integer
>
> ListQry is the recordsource for my form. I would like to add the filter on
> the form to the Where statement. I can't get it to work. I think I'm 
> having
> issues with the spaces and quotation marks and all that. Could I get help?
>
>
> 

0
De
3/13/2010 5:24:41 PM
Reply:

Similar Artilces:

Delete Query Please help
How do I delete the contents of column? I have a table where I need to empty the contents of a column(field) there are thousands of records. There are some records that have data in this field I need to make this field blank for all the records in the table. How do I this please? Thank you for any help. Update yourTableName set columnName = ""; "TotallyConfused" wrote: > How do I delete the contents of column? I have a table where I need to empty > the contents of a column(field) there are thousands of records. There are > some records that have da...

RMS updates
I purchased RMS this past January and have been running since February. Are there any upgrades...and if so, how do I get them? Also, how to I increase the size of my SQL DB? It seems it's at 94% and losing the DB would be a disaster. Thanks I believe you are suppose to be notified by email or possibly snail mail when updates come out. They get sent right to you. Of course I have yet to see them either. As for the database it should be set to grow automatically. "zoostation" <zoostation@discussions.microsoft.com> wrote in message news:55BD57F7-B22B-45E1-9BEF-EF0...

outlook 2007 updates #2
version of my outllok 2007 is (12.0.6327.5000) sp1 MSO (12.0.6213.1000). I would like to update it from (12.0.6213.1000) to (12.0.6320.5000). any ideas which update would do that? thanks ...

Help!! sort address field by street, then number??
Is there a way to accomplish this feat?? cell data "7004 Brookville Rd" need the file by street name , then by number. All suggestions deeply appreciated. Keef If you're lucky you could use Data|Text to columns. To separate pieces into individual columns. But I've never been lucky enough to get 100% -- lots of manual effort may be required. Keefer wrote: > > Is there a way to accomplish this feat?? > cell data > "7004 Brookville Rd" > need the file by street name , then by number. > > All suggestions deeply appreciated. > > Keef...

DST update tool for Outlook/exchange...
Hi all, How do i check if the patch for outlook software applied and work? After applied all the patch for server, exchange, and Outlook/exchange update tool, will the appointment shift by 1 hour or it will stays at the same time as of NOW before 3/11? THanks for your time. Only the appointments in the affected DST period will be impacted - which occurs 3 weeks earlier and ends 1 week later than earlier years. The Outlook Timezone Update Tool in interactive, allows selecting/deselecting appointments to be changed and outputs a log that is displayed at the end of the session. When you...

Query Help 03-28-07
I have 1 question regarding the following query. I need to know why when I run the query it is prompting me for the date 2 times. SELECT qry_AMUWork_Rcd_Date.DateRcd, tblCorrWork.Frequency, tblCorrWork.RptName, tblCorrWork.MISNumber FROM qry_AMUWork_Rcd_Date LEFT JOIN tblCorrWork ON qry_AMUWork_Rcd_Date.MISNumber = tblCorrWork.MISNumber WHERE (((([tblCorrWork].[MISNumber])=85 Or ([tblCorrWork].[MISNumber])=69 Or ([tblCorrWork].[MISNumber])=71 Or ([tblCorrWork].[MISNumber])=74 Or ([tblCorrWork].[MISNumber])=80 Or ([tblCorrWork].[MISNumber])=81 Or ([tblCorrWork].[MISNumber])=92 Or ([tb...

Newbie struggling
Hi For coursework I have to create an excel workbook that takes totals from invoices and adds them all up - pretty simple for some but not me! I have created the invoice template and the user enters the necessary data to complete and print it out - what I need is a way of taking four or five sets of data from that invoice sheet (e.g. invoice number, customer name etc etc) and writing them to another sheet so that I can add them up. The problem I am having is how to get that data into the next blank row in the second sheet - currently it only overwrites the first set - how do I get the pointe...

Background image becomes dark after adding watermark, need help.
I want to add text on bitmap,the following is the implementation. After adding text watermark, I find text watermark is added on the bitmap, while the bitmap is complete dark.The bitmap I used is not a dark image. That is to say,generating bitmap only contain watermark,not including original bitmap information.I still don't find the bug to the function and do hope someone could give me some clues,thanks in advance. //pBitmap: the original bitmap //BitmapNew: the bitmap created after processed the original bitmap //iSize: text font size //strText: text stuff //color: text color void AddTex...

Outlook 2003
Hi, I have recently upgrated from MS Outlook Express to MS Outlook 2003 on WinXP. Using rule based filters I want to filter my mails in different catogaries. Now the situation is I want all the mails arriving from one domain to be moved in a single floder. eg. if the mail arriave from ..yahoo.com domain it should me moved in to local yahoo folder. Is there a way to specify wildcard like *.yahoo.com or any other tweak so that I can move all the mail arriving from one domain rather then using single mail id. Regards, Ketan. You can use the condition; With specific words in the sender's ...

I need a template for avery 6572
I am trying to format a lable 6572 (avery). I can't find it in publisher 2003 - and I can use word, although the lable doesn't look as good, because I have to save it as a jpg. Any ideas? Often the same Avery label will be found under different numbers. They change because of ink jet or laser, color, and/or quantity. It could be that the label you need is called something else. I'd suggest checking Avery's website and see if this is the case. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "helpmerhon...

help with code on event call
Hi I am unsure how to code the following the table I am working on is Baan I am selecting the record using the orderNo field but depending if the field IcyDate empty or not I will open different forms the code I have in the event call at the moment is below and where I have put the ***** is where I need an if statement to see if the field IcyDate is empty or not Private Sub But_SelONo_Click() On Error GoTo Err_But_SelONo_Click Dim stDocName As String Dim stLinkCriteria As String Dim intcount As Integer intcount = DLookup("[Count-O-N]", "[Qu-cmm-countor...

Need help with format
I have 2,000 players in football pool game. What would be a format I'd use. How will I be able to find the player with the least amount of mistakes? Thanks, R2ri =Index(A:A,Match(min(B:B),B:B,0),1) Assume names are in column A, count of mistakes in column B if there is a tie for least amount, the player in the lowest numbered row with the least amount will be shown. Also look in Excel Help for the RANK function. -- Regards, Tom Ogilvy R2ri <ron_2ri2ri@yahoo.comn> wrote in message news:339b01c37a44$ea707830$a601280a@phx.gbl... > I have 2,000 players in football pool g...

Please help me with a couple of VBA related Word questions... than
1. If a group of users have a common template installed on their machine, and they are all sent a document created with that template, is the appropriate corresponding template identified by its filename, or by its fully-qualified-folder-path-filename (which could be in different locations on different client machines), or something else? 2. Can VBA code be separated from the document and distributed separately from the document? 3. What's the best way of distributing changes to a VBA macro? Thank you for your help I do not have the facilities to the check a...

Export Excel file to SQL
I'm currently doing a project where it require client to export excel spreadsheets to SQL. the application that is using the excel spreadsheet exported is a web-based application. Is it possible to do that? Any suggestion or recommendation is greatly appreciated. thank you Go to Enterprise Manager. Browse to your database in the left pane (object browser). Right click your database. All Tasks --> Import Data. Next. Set your datasource to Microsoft Excel 97-2000 or whichever Excel version you have. Click the ... button next to the File Name text box and browse to your Excel sheet. M...

Updating Rank
I have a spreadsheet with column A ranking the info 1-100 for priority. How can I change a row to move to the top and re-rank the rest? The best example I have seen of this is your netflix queue where you ask it to move a selection to the top or re-rank numbers by changing where you want them in the queue. I hope this makes sense. Thanks!! 'Right click on sheet tab, view code, paste this in: '===================================== Private Sub Worksheet_Change(ByVal Target As Range) 'Define Range that will be changed If Intersect(Target, Range("A1:A100")...

Filtered Sort freezing up
I was working with a filtered sort. When I clicked on the blue arrow for that column, nothing happens. I tried to remove the filtered sort in the Data drop down but the spreadsheet will not allow me to end the sort. Also, my spreadsheet name now has [Group] after the name (ie. Spreadsheet [Group]). What can I do to resolve this problem? You have multiple sheets selected. You can rightclick on any of the selected/grouped sheet tabs and select Ungroup sheets or you could just select another sheet. Then come back to the sheet you want and try the autofilter stuff. FrankG w...

Rounding issues after 2010 updates installed
I am having issues with the system rounding for AP invoices in "shipment/invoice" receivings, allocated to WIP accounts. Before, we used to have 4 decimal places to round and if something wouldn't match up, we would enter the extended cost of an item, the unit cost would adjust (even if we didn't see anything) and everything would be fine. Now we only have 2 decimals places (for some crazy reason) and are having issues when a vendor rounds to .0000 and computer only goes 2 decimals. We get an error message that WIP does not equal AP amount and there is no wa...

Big Problem-Please help!
I wanted to remove a server from a policy and instead of clicking remove from policy, I accidently click delete and it deleted the server. Now the server no longer functions. I tried to reinstall Exchange but nothing. Good thing this is just a connector server but I need it back up. Please HELP!! ...

before update
Hi everyone. I am using the following code: Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.Combo60 = True And (Nz(Me.Personal_Data_Inaccuracies, 0)) + (Nz(Me.Improper_Combine, 0)) = 0 Then MsgBox "You selected yes to the Maintenance Issue question, therefore please select the number of errors in that section", vbCritical Cancel = False ' Stop the process End If End Sub If you try and save it prompts for the msgbox one time. How can i make it so that the message comes up until they enter the required data or change the field to false? Thanks. Cancel = False...

Legacy driver conversion needs means to identify process sending I
I am still converting a massive and old WDM driver. We found where the old driver copies the Irp->Tail.Overlay.OriginalFileObject in order to identify which process sent the WDFREQUEST. While inspecting MSDN's docs on the IRP struct, we noted that the OriginalFileObject member of Tail.Overlay is not recommended for driver usage. So here is my question: What is the ideal way for a KMDF driver to determine which instance of our DLL (which user mode app) sent the WDFREQUEST? What our driver must do is enforce that one and only one app open certain items like channels o...

Went from GP_8 to GP_9 and SQL2000 to SQL2005 and SQL freezing
We have been live for 2 days after the conversion. Monday we had the CPU peg at 80% on the SQL Server process and had to reboot. Same thing happened 2x today. Cannot determine a pattern with users doing anything special to cause problem. Have run KB 898982 and 850996 without issue. Server has 4 Xeon's with 4gb of memory. Memory use at 1gb with GP client running on the server and one other user. Startup a third user with their Home Page and memory use goes to 4gb. CPU at 1%-4%. I just know sometime tomorrow something will cause the hang. Runn GP 9.00.0281 and W20003 64-bit SP1 on s...

Help with Tab
Hi, In word we set Tab for moving to fixed distance. Is it possible do same in excel? If possible please let me know how to set up Tab. Thanks. The closest excel has is just using the space bar multiple times. If you wanted to indent the value in the cell, you could use: format|cells|alignment tab and click that Indent spinner. newbie wrote: > > Hi, > In word we set Tab for moving to fixed distance. > Is it possible do same in excel? > If possible please let me know how to set up Tab. > Thanks. -- Dave Peterson ...

Excel Charting (help)
Hi everyone, Can anyone help? I am having difficutly chart what I thought would be easy..:-) I have 500 rows of dates in one column. (there is lots of other information-data on the sheet). I want to plot a line graph with the Dates on the X and NUMBER OF DATES (ie counta) on the Y axis... The only way I know how to do this is to place a number 1 to 500 in the respective cell next to the date... and then plot it.... is there a better way?? Excel 97 & NT :-( Hi Steve, From your message it appears the data in the column of interest has many repeat values. Do you know the unique ...

Could someone please help me with using Columns?
I am working on a Word document, and I need to set up 4 columns in several parts of the document. In one example, in the first column I need 2 rows of text, in the second column I need 4 rows of text, in the third column I need 4 rows of text, and in the fourth column I need 3 rows of text. I sketched out an example of what I need it to look like in the following image: http://img202.imageshack.us/img202/6574/wordexamplelayout.png I need to be able to specify the number of rows in each column and the text shows up in each column. Can I do this using columns in Word? Or ...

*Help* Senders Getting NDR Message Size Too Big--But Are Not
Hello-- We are running Exchange Server 2003 Standard on a Windows Server 2003 Standard platform. About a week or two ago our members starting getting NDRs that the message size exceeded the limit. We had it set to the default, and we did tests of sending ourselves messages from home with 4MB attachments, small mesages, etc. and would still get the NDR. After that, I set the message size (not attachment size) to unlimited, and some of our members are still getting NDRs that the message size is too big. Can anyone help me and tell me what this could possibley be? Everything else on our...