#### Exclude data from field, when another field equals a certain numbe

Hello,

I have 2 fields.  I have Job # and GL #.

I need to exclude from the GL field, values that are 1234, but ONLY if the
Job # = 999999.

I have tried using this formula in the criteria of the GL# field in my query:

IIf([Job #]=999999,<>1234,[GL #])

and instead of 999999 still showing up in the query and excluding GL# 1234,
999999 doesn't show up at all!

What am I doing wrong??

Thank you!!!!!!!!

MN

 0
Utf
4/28/2010 2:24:06 PM
access.queries 6343 articles. 1 followers.

2 Replies
1186 Views

Similar Articles

[PageSpeed] 26

IIf([Job #]<>999999, [GL #], IIf([GL #]=1234, Null, [GL #]))

I'm assuming by exclude that you mean Null.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"MacNut2004" wrote:

> Hello,
>
> I have 2 fields.  I have Job # and GL #.
>
> I need to exclude from the GL field, values that are 1234, but ONLY if the
> Job # = 999999.
>
> I have tried using this formula in the criteria of the GL# field in my query:
>
> IIf([Job #]=999999,<>1234,[GL #])
>
> and instead of 999999 still showing up in the query and excluding GL# 1234,
> 999999 doesn't show up at all!
>
> What am I doing wrong??
>
> Thank you!!!!!!!!
>
> MN

 0
Utf
4/28/2010 3:24:01 PM
On Wed, 28 Apr 2010 07:24:06 -0700, MacNut2004
<MacNut2004@discussions.microsoft.com> wrote:

>Hello,
>
>I have 2 fields.  I have Job # and GL #.
>
>I need to exclude from the GL field, values that are 1234, but ONLY if the
>Job # = 999999.
>
>I have tried using this formula in the criteria of the GL# field in my query:
>
>IIf([Job #]=999999,<>1234,[GL #])
>
>and instead of 999999 still showing up in the query and excluding GL# 1234,
>999999 doesn't show up at all!
>
>What am I doing wrong??
>
>Thank you!!!!!!!!
>
>MN

You can't pass an operator such as <> in this way. Could you post the entire
SQL of the query? I would guess you will need a clause such as

([Job #] = 999999 AND [GL #] <> 1234) OR ([Job #] <> 999999)
--

John W. Vinson [MVP]

 0
John
4/28/2010 5:57:53 PM

Similar Artilces:

gci and -include and -exclude
I am trying get a limited set of files from a directory tree. The following is what i want but it doesn't work. I get everything. get-childitem -LiteralPath \rm5_0_code_h\source -include *.frm,*.ctl,*.bas,*.cls,*.cs,*.cpp,*.hpp,*.sql,*.java -Recurse I have even tried to exclude what i don't want but it doesn't seem to matter. get-childitem -LiteralPath $path -include *.frm,*.ctl,*.bas,*.cls,*.cs,*.cpp,*.hpp,*.sql,*.java -recurse -exclude *alter*.sql,*.dll,*.pdb,*.ncb,*.tlb,*.scc,*.bmp,*.ico i still get them all. what am i doing wrong? dan you have to a... Datedif exclude weekends Hello newsgroup gurus and users. Your kind assistance please. This question has probably been asked a millions times but I cant find it. Does any one have a solution to this. I want the date difference between two dates but exclude weekends. Many thanks Paul Paul, The NETWORKDAYS function will return the number of days, excluding weekend and optionally holidays, between two dates. See help for NETWORKDAYS for more information. This function is part of the Analysis Tool Pack add-in so you must have that add-in loaded; otherwise, you'll get a #NAME error. -- Cordially, Chip Pe... Requesting Data from Old Server Name We recently switched servers in a company with over 300 workstations. Everyone can get the mail off of the new server. Many outlook clients are requesting data from the old server for some unknown reason. They get that annoying box that pops up in the right corner of the screen. So why are some peoples outlooks requesting data from the old server? how do i fix this? ... exporting data Is it possible to export account information to another user of a Money file and preserve all the reconciliation and category information? Use the file->export option and export as Loose QIF. The export file contains the reconciled status and the category. You can then import this into another Money file. -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or email mnyukwsh@microsoft.com espe... Large Quantity of Data, Graphed in Time Intervals I have a very large set of data (over 20,000 points) listed in minute intervals. I'd like to be able to create charts using different time intervals such as 5 minutes, 15 minutes etc. by using a pull down menu to select the interval and have the graph make itself. Is this possible? You can use a pivot table to group times by such intervals. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Kirsten" <Kirsten@discussions.microsoft.com> wrote in message news:811D6E3E-C457-4D36-BA47-1C835D6D670D@microsoft.com.... How to update a certain record with a checkbox I'm pretty much new to all this, so if this question appears to have been written by a complete rookie at writing VB code, well...that's because it is. I am trying to figure how to update a field of a certain record in a table by checking a box in a form. I don't know how to break this down in Access or VB programming terms, so I'll just lay it out. I track the status of 21 aircraft. The AIRCRAFT table has 21 records. One of the fields in the AIRCRAFT table is "Last Fly Date". I have another form called DAILY RECAP INPUT, along with it's associated table of... Exclude a cell Excel 2002 Any one know how I can sum all the cell values in a column except the value of the last cell to be entered which could be say half-way down the column? Is there a function to exclude a particular cell from "sum" ? I know a lot of ppl are far more advanced than I am but we all start somewhere lol Thanks for your help Dale To exclude cell A16: =SUM(A:A)-A16 Not very elegant, but effcient! Cheers, -- AP "DF2507" <nospamthx@blueyonder.co.uk> a �crit dans le message de news: xB6sg.51172$181.12831@fe3.news.blueyonder.co.uk... > Excel 2002 > &...

Error on new Data Base Query
I am tring to acces an excel Data base Query but the following error mesage appears : "UNEXPECTED ERROR FROM EXTERNAL DATABASE DRIVER (22)" This is after i selected the File to be used, Database Type Excel Files * HELP!!! I would try searching the MS Knowledge Base (www.support.microsoft.com) and/or perhaps repairing or reinstalling Excel. On Wed, 9 Feb 2005 10:05:05 -0800, "MESTRELLA29" <MESTRELLA29@discussions.microsoft.com> wrote: >I am tring to acces an excel Data base Query but the following error mesage >appears : > >"UNEXPECTED ERROR F...

Extender Field
I created an extender field type "LIST" and entered about 300 records. When I try to add more records it not saving for somehow. Is there a limitation on the number of records you can add on field type "LIST"? regards, What version of Great Plains are you using? Are you getting any error messages? On Apr 23, 5:04 pm, davidv <dav...@discussions.microsoft.com> wrote: > I created an extender field type "LIST" and entered about 300 records. > When I try to add more records it not saving for somehow. > Is there a limitation on the number of recor...

Data sort popup box
I am using the data/sort functionality in Excel 2007. Every time i click on the Sort icon, the pop up box is bigger. If I maek as small as possible, and reopen, it gets bigger each time. Any idea how to keep this as small as possible? ...

Data sort, > 3 columns
Hi Excel has the Data sort option. Data>> Sort This allows you to sort 3 columns Is it possible to sort more than 3 columns?? I would appreciate any advice. Thanks Clive This link explains how to do it: http://www.contextures.com/xlSort01.html#Sort04 Basically, you sort on the least important fields first, and then sort on the more important fields. Alternatively, you can combine some fields together in a helper column. Hope this helps. On Jun 4, 11:04=A0am, Clive_S <clives...@yahoo.co.uk> wrote: > Hi > > Excel has the Data sort option. > > Data>> S...

Import Data from the Web to a SpreadSheet

How to gather data from different excel workbooks into 1?
Suppose we have several different workbooks (not shared) containing the same fields on a server location.Each user update his own workbook daily.I need (the controler) to gather the data in another workbook and report the daily updates for all the users in a table,but needs it to be automatic, and showing these results in detail.I have a perfect background but don't know how to connect the points.Please help,thank you. Ron de Bruin has lots of code at: http://www.rondebruin.nl/copy3.htm Maybe you could steal some of it to merge the worksheets into one giant worksheet and process you...

Outlook Rules Bug
When using Outlook 2003 Rules and specify to search for words in the Senders address it does not search for words in the Sender field outside of the email address itself. For example.... Bill Gates <billg @ microsoft.com> Outlook can not apply a rule looking for "Bill Gates" in the sender email address. Is this how it should work, I wouldn't think so? Shouldn't it search for any text in the Sender field? ...

How to handle lots of data
Hi people, My problem is I have a graph with lots of data on it, so much that it is hard to spot useful inofrmation in it. Short of simply expanding the graph width, or making multiple graphs of different sections of data, how else can I sort this out? I have found a scroll bar object that one can add to charts that looks promising but can only get it to add more data to the graph, so the graph displays point1, if you move the scroll bar one unit the graph displays point 1 and point 2, then point 1,2,3 and so on. how can you st it up to show a max of say 10 points, such that when you move t...

Pulling data from yyyyMMMMdd.xls
My file names are as follows yyyymmmmdd.xls - 2005August21.xls,2005August22.xls Presently the data (E5:E33) then (H5:H49) is copied then pasted (Value+Transposed) into '2005 Daily Production.xls' August! H25 then AL25 (Row 25 is the row which falls on August22 date line that's why the data is pasted Transposed.) Is there a function that can pull the data based on a entered date into the '2005 Daily Production.xls' worksheet? ...

Problem Changing Data Source In Pivot Table
Hello all. I have a workbook with multiple pivot tables pulling from a odbc datasource. The database has been moved to a new server and I a finding that even when I update the SQL with the new location in an one query, it tells me it cannot graphically represent the SQL an cannot pull the table. I am unsure as to whether or not I am mis-typin something (although not really sure how i could do that) or if i a missing some other place i need to update the link. Also, is there way to do it for all of the pivot tables at once? I have Excel 2000. Thank you!!! An example of the sql as it is (scr...

HR data import
I recently upgraded from Dynamics 7.5 SQL with Human Resources to 8.0 SQL. Apparently, a large swath of HR data did not come across in the upgrade (for example: past review dates, applicants, attendance, time codes, pay codes linked to positions, etc). Our Payroll staff waited several business days to alert IT of this problem (!!!). As such, "Restore from your backup and try again" is not an option for me. Oh, how I wish it were... I have restored the "just before upgrade" company and Dynamics backups to a new location. I can examine tables and see the data in my...

Format for % in a concatenated field
I have a calculated field in a report that includes a percentage. ="30 =" & (30/(Count([surname]))*100) & "%" How do I format the percentage to two decimal places? Thanks, Robin Chapple One way: ="30 =" & TEXT(30/Count([surname]), "0.00%") In article <srhtg0tc4si4vedrdcjgdnmhen7io9vhof@4ax.com>, Robin Chapple <robinsky@westnet.com.au> wrote: > I have a calculated field in a report that includes a percentage. > > ="30 =" & (30/(Count([surname]))*100) & "%" > > How do I forma...

Back-Up Data & Delete Data
I am curious, is there a way to back up my current data and then go into RMS Admin, so that I can delete it and start fresh? Would I be able to recover my database once it's been deleted? "Vince" <Vince@discussions.microsoft.com> ha scritto nel messaggio news:7D52BA8C-FF21-4947-94EE-6E9732146984@microsoft.com... >I am curious, is there a way to back up my current data and then go into >RMS > Admin, so that I can delete it and start fresh? Would I be able to > recover > my database once it's been deleted? No... You could backup your current da...

Data Validation #31
That was exactly what I needed. I was even able to use the [alt] enter key to get the formatting that I wanted on the form. I appreciate it very much :) -- rick2701 ------------------------------------------------------------------------ rick2701's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15852 View this thread: http://www.excelforum.com/showthread.php?threadid=273439 ...

Macro to compare two columns of data
Hello All Excel Guru's: I was wondering if it was possible to have a macro to compare two columns of data and in the third column only show the ones that are in Column B and not in Column A and vice versa, In Column A but not in Column B in Column D Examle: Column A Column B Column C Column D Apple Apple Bananas Bananas Strawberry Orange Pear Pear Grapes Orange Cherry Strawberry Grapes Cherry Plums Plums I have a formula that does this, but I have to copy this formula down Column C. The formula that I have is listed below: formula located in Column C =IF(B3:B101="...

data type mismatch
I am trying to run the following code and keep getting a data type mismatch. Can somebody please have a look and let me know where I'm going wrong. Private Sub Command7_Click() Dim ctl As Access.Control Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim varItem As Variant Dim strCriteria As String Dim strSQL As String Dim Val As Currency Set db = CurrentDb() Set qdf = db.QueryDefs("qry_payrolledit") For Each varItem In Me!LstPayroll.ItemsSelected strCriteria = strCriteria & "," & Me!LstPayroll.ItemData(va...

3 issues
I have 3 issues: 1. I want to change the location of the data file. I tried moving the data file to where I want it and then openned Outlook and it said it couldn't find the file but did not give me the option to browse to its new location.How do I change the location of the PST data file? 2. Two identical "Personal Folders - Bill" show up at the ;eft side of the screen under All Mail folders - How can I delete one? 3. When I open the address book when sending an email it opens to a contacts folder which has nothing in it - I keep going and under Personal Address book...

Scatter Chart
Hi, I am new to charts and must create a chart where the data points are sales, hours, and the store number. For example: Store Hours Sales 1 200 100,000 2 300 200,000 3 350 350,000 I can create the scatter chart showing the intersection of the hours and sales by selecting just that data, but cannot get the store number to appear in the data label. When I select the store number as part of the data, that gives me an incorrect data intersection. Is there any way to get the store number as a data label without editing each data labe...