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
611 Views

Similar Articles

[PageSpeed] 51

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:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

SQL in Excel data
Hi all, Is there a possibility/way to run an SQL query in an excel data sheet? I have quite some data like the sample below, now i would like to have the sum of spending for each person. Like it is possible in Access. A1 B1 Field1 Field2 Chuck 12,89 Mike 23,09 Jean 9,34 Chuck 30,00 Mike 3,80 Chuck 22,00 Mike 7,23 Jean 10,55 Jean 10,75 Jean 31,45 Chuck 19,99 Result Field1 SumOfField2 Chuck 84,88 Jean 62,09 Mike 34,12 Advice would be appriciated. Cheers, Ludovic Hi You could use a formula like this ...

Update Manager
Everytime I boot up my PC a small window pops up entitled "UPDATE MANAGER". It shows two progress bars, but nothing ever happens. Why is this window popping up every tiem I turn on the computer and how do I get it to stop? It never used to do this, it just statred lately. Thankyou. tony Because you installed something which features an update manager "Omaha Tony" <OmahaTony@discussions.microsoft.com> wrote in message news:A08659C0-1560-4E8C-8A1C-76A5B1DBB1F8@microsoft.com... > Everytime I boot up my PC a small window pops up entitled "UP...

Please help..with a formula. I don't know code.
I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

Outlook 2000 doesn't respond after downloading MS update!!
Luckily, my Outlook Express seems to work, but my Outlook 2000 will open, but if I click on a message or do Anything, it FREEZES. HELP!! I rely on calendar functions, other things. HOW could MS allow an update that screws up their own programs? I did restore - to no avail. I lent CD to someone, so can't do "repair." (Trying to get a hold of friend) Please help! I'm in process of job hunt & really need Outlook to function... Thanks, Sherry Did you recently apply SP3 for Office/Outlook 2000 and is Outlook 2000 configured in Internet Mail Only mode? If yes, you...

Need a default email account for all users, need help.
I have a tablet PC running WinXP Tablet with Outlook 2003. This tablet will connect to our exchange server via VPN. How can I set it up so that everyone that logs onto their account can access one (the same) email account. The problem is that I dont know at this point all of the users however anyone using the tablet will use one generic email account. So how can I set Outlook to default to this account so that no matter who logs on they will use this account? Thanks! Shane ...

Repair SQL Corrupt database
Hi, I have a corrupt MS SQL Server 2005 database which I am trying to repair using: DBCC CHECKDB ('MYDB', REPAIR_ALLOW_DATA_LOSS) Unfortunately this does not seem to fix anything. After running the repair multiple times, I ran DBCC CHECKDB ('MYDB') WITH NO_INFOMSGS to see if it fixed the corruption, I noticed that is was returning a random amount of errors on each run. Does any know if It is the case that this database is beyond repair? If so will the best approach be to revert back to a non corrupt database backup and then roll forward using Transaction Lo...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

does vista installed on virtual machine 2007 get wsus updates ?
It is searching for updates but it is not finding anything and saying that Windows is up to date. I have set the updates to install from the wsus server and assigned the updates to the same Vista virtual machine .. Thank you -- aconti ------------------------------------------------------------------------ aconti's Profile: http://forums.techarena.in/members/73272.htm View this thread: http://forums.techarena.in/active-directory/1290161.htm http://forums.techarena.in Hello aconti, If the machine is getting the correct GPO for the WSUS settings, check with rsop...

DEADLINE... PLEASE HELP! Stacked Bar chart?
I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Office 2010 Buying Question Assistance Needed
I've been looking through the MS Office 2010 web site to try to determine what my new small company would require, but I can't find the information I need. We for sure would need Office Pro Plus, but other than that I'm not sure. We want to run it on our own server. We will initially have 3-5 people using it and perhaps more later on. Would we need to purchase site licensing? Unfortunately, our programmers are MS haters (I'm not) and I can't get any assistance from them on this, but I have power of the pen. I would appreciate any assistance I can get. Th...

Update Query
Access 2003 XP SP2 I am having a problem with an update query. Table is in a one-to-one relationship, referentail integrity and cascading data are checked. (The fileds I want to update are not in both tables) Table name= payForward Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc Oct-Sep fields are yes/no type I want to "select" a field (Oct-Sep) via a query parameter and repalce "yes" with "no". Here is my query: UPDATE payForward SET [Enter month]=No The messages I get is 'operation must use an updateable query' Wh...

How do I lock a chart so it will not update?
That's the question. I have my data in Excel and the chart in Excel but not all the data cells are used. Everytimg I open the chart it wants to update and I want it to stay the same. Any ideas on how to lock the chart? Hi Just a few ideas: You could lock the cells that are shown in the chart. Or you could copy the cells and paste as values (assuming formulas were used that update when other cells change). -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Locking a Chart" wrote: > That's the question. I have my data in Excel and the chart in ...

Microsoft Update 'left behinds'
Every time I download and install the latest Microsoft Update 'fixes' to XP-Pro, the file space taken up by XP-Pro grows and grows. While this will be an elementary question to many, is some of this XP-Pro file space 'growth' being caused by stuff that the 'fixes' leave behind and files that XP-Pro no longer needs? If so, can these 'left behind' files be gotten rid of so as to reclaim some HD space? Are there any pros and cons to deleting these 'left behind' files? Assuming that these 'left behind' files can be deleted, what i...

Hyperlink File Help
I am needing some major help. I have a file with hyperlinks in column F that link to a file on our server. I am needing to test to see if the file exists and if it does, copy the file to a folder in my documents called (CapturedFiles) and if it doesn't format the cell color to red. Can VBA do this and if so how? Any help would be greatly appreciated. Thanks in advance. Fileserver or webserver ? Tim On Nov 23, 7:20=A0am, Aaron <Aa...@discussions.microsoft.com> wrote: > I am needing some major help. =A0I have a file with hyperlinks in column = F that > l...

Help please user not showing in 5.5 GAL but is in exchange 2003 GA
Up until today I have been bable to add users fine and their address would appear in both the 5.5 GAL and the exchange 2003 GAL. Is a single site with 2 5.5 servers and 1 exchange 2003 server. When I add a new user now through users and computers and put the mailbox on the new exchange 2003 server the user gets his email addresses and appears in the GAL on the 2003 server but people connected the the old 5.5 servers cannot see it. When I open the 5.5 exchange admin tool again if connected to one of the old 5.5 server I cannot see the person I just created but when connected the the 20...

VLookup #VALUE! error help needed to resolve
The following is the funcation I have: =VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0) I have all the columns formatted the same; as in the column that the function is using to lookup is text and so is the column for this figure in order to pull back the appropriate answer. I have keyed the data instead of having links. I have replaced the final '0' with TRUE & FALSE then put it back. I have formatted the columns for text and for numbers. But I am getting the #VALUE! error in SOME of the cells NOT all of the cells. I don't know what else to d...

help with a sub
Hi, can anybody tell me why the following code fails at FormatConditions.Add Private Sub CommandButton1_Click() Dim Sh As Worksheet Dim lngLastRow As Long Set Sh = ActiveWorkbook.ActiveSheet lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row Range("A4:E" & lngLastRow).Activate Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(MOD(ROW(),2)=0" Selection.FormatConditions(1).Interior.ColorIndex = 24 End Sub Thanks -- Traa Dy Liooar Jock You have an extra open paren just before MOD: &qu...

cdrom.sys corrupt in Win7
Yesterday Win7 decided to no longer show my 2 LiteOn DVDRW drives. I've tried to re-install/repair the driver (6.1.7600.16385) and everytime I get the same response = my current driver is good. BUT, then when I check with Device Manager, it shows that the drives are not working. Can anyone help me get a new cdrom.sys installed into the system32/drivers folder? Booting up with the Win7 DVD will work. But I can't find the cdrom.sys on the disk. No other repair options are there to get this fixed. Help would certainly be appreciated. I don't want to have to start all o...

Need to have more Columns available in advanced view
I know how to add columns in advanced view but i can't add all the columns I'd like to add. I can choose more fields (attributs) as search attributes than as result columns. Is there somewhere a switch to turn a field (attribute) into not only beeing searchable but selectable as a column in advanced search? Example: "Invoice Product": Is there a way to make an advanced search or view which delivers field (attributes) of "Invoice Product" as a result? Marko ...

Exchange 2003 SMTP QUIT
= = = = = = = = = = = = = = = = = = = = = = = = = = = PROBLEM: Problem is that OUR SERVER is sending QUIT-, instead of sending MAIL FROM: MY Server open a SMTP connection REMOTE Server says 220 .. MY Server says EHLO to REMOTE Server REMOTE Server says 250 ... MY Server then say QUIT ! (instead of MAIL FROM ....) We have::Exchange 2003 , cu SP1, pe Windows 2003.. Exchange has also IMF (spam filter from Microsoft) and Symantec Mail Security for Exchange 4.5. all PTR is installed and working OK. The SMTP Server is workin OK a while, then it start opening a lot of connections (7-10 /sec) t...

Exchange 2007 Mailbox Folder information not updating
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange I am using Entourage 2008 Web Services Edition in an Exchange 2007 SP1 UR4 environment. I have only been using the Web Services Edition for about a week and prior to that I was using the regular Entourage 2008 client for four months. In any case, the problem I am about to describe occurs in both versions of Entourage 2008. <br><br>Recently my employer's executive group decided to implement Managed Folder Policies to assist in controlling mailbox size. I was the first to...

filter records in a form
I've created a form with its subform to enter tasks of employees. however I need filter records only for active employees The form has as a source, the table CARD_EMP (employee card). It has a field ST_EMP for the employee status (Active , Pasive) In the Event Form_Current I wrote DoCmd.ApplyFilter ST_EMP='A' but it doesn't work Thanks for advance Carlos -- Message posted via http://www.accessmonster.com Hi mamumi Your advice was the solution Thanks a lot Carlos -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscodin...

Service Packs & Updates
I have used the trial software from Microsofts site of SBS 2008 which is SP1 I have done all updates but it hasnt gone to SP2 should I do this manually?? Also as its a trial I was just going to purchase Lic and add key is this OK or should I pruchase Media and resetup again? Any Advice would be appreciated Thanks - Mark I've had problems installing SP2 in the past too. You've really got to do all the Updates and restart and then check again ......and again.. It'll only offer SP2 when *all* the other Updates have been installed. Have you switched to get...