Query is making a nuts

Hi

Using Access 2007

I have a table and two of the fields (Status and OrigStatus) have, among 
other possible entries, the words "Member," "Customer" or "Request."

I am trying to create a query that only displays records which do NOT have 
"Member," "Customer" or "Request" in either field.

But it won't work!

I have tried putting each word in its own Criteria column in each field 
using syntax such as:

<>"Member"

with no success.

I have also tried

<>"Member" OR "Customer" OR "Request"

with no success.

What am I doing wrong? How should I do this?

Thanks! 

0
Craig
1/25/2008 12:14:12 AM
access.queries 6343 articles. 1 followers. Follow

6 Replies
567 Views

Similar Articles

[PageSpeed] 0

Making ME nuts; not "a nuts"

To clairfy:

When I say "with no success," I mean records with Member, Customer or 
Request in either field are still appearing.

Thanks,



"Craig" <msaccess@howardhowardfine.com> wrote in message 
news:O50B8cuXIHA.4808@TK2MSFTNGP05.phx.gbl...
> Hi
>
> Using Access 2007
>
> I have a table and two of the fields (Status and OrigStatus) have, among 
> other possible entries, the words "Member," "Customer" or "Request."
>
> I am trying to create a query that only displays records which do NOT have 
> "Member," "Customer" or "Request" in either field.
>
> But it won't work!
>
> I have tried putting each word in its own Criteria column in each field 
> using syntax such as:
>
> <>"Member"
>
> with no success.
>
> I have also tried
>
> <>"Member" OR "Customer" OR "Request"
>
> with no success.
>
> What am I doing wrong? How should I do this?
>
> Thanks! 

0
Craig
1/25/2008 12:19:12 AM
in SQL

(Status<>["Member","Customer","Request"]) or 
(OrigStatus<>["Member","Customer","Request"])

hth

"Craig" <msaccess@howardhowardfine.com> wrote in message 
news:uv%23wwfuXIHA.5348@TK2MSFTNGP03.phx.gbl...
> Making ME nuts; not "a nuts"
>
> To clairfy:
>
> When I say "with no success," I mean records with Member, Customer or 
> Request in either field are still appearing.
>
> Thanks,
>
>
>
> "Craig" <msaccess@howardhowardfine.com> wrote in message 
> news:O50B8cuXIHA.4808@TK2MSFTNGP05.phx.gbl...
>> Hi
>>
>> Using Access 2007
>>
>> I have a table and two of the fields (Status and OrigStatus) have, among 
>> other possible entries, the words "Member," "Customer" or "Request."
>>
>> I am trying to create a query that only displays records which do NOT 
>> have "Member," "Customer" or "Request" in either field.
>>
>> But it won't work!
>>
>> I have tried putting each word in its own Criteria column in each field 
>> using syntax such as:
>>
>> <>"Member"
>>
>> with no success.
>>
>> I have also tried
>>
>> <>"Member" OR "Customer" OR "Request"
>>
>> with no success.
>>
>> What am I doing wrong? How should I do this?
>>
>> Thanks!
> 


0
Haggis
1/25/2008 12:28:08 AM
Use

    Not in ("Member","Customer","Request")

as the criteria for both fields.

The criteria should be on the same line so that the criteria is ANDed 
together not Or'd.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


Craig wrote:
> Hi
> 
> Using Access 2007
> 
> I have a table and two of the fields (Status and OrigStatus) have, among 
> other possible entries, the words "Member," "Customer" or "Request."
> 
> I am trying to create a query that only displays records which do NOT 
> have "Member," "Customer" or "Request" in either field.
> 
> But it won't work!
> 
> I have tried putting each word in its own Criteria column in each field 
> using syntax such as:
> 
> <>"Member"
> 
> with no success.
> 
> I have also tried
> 
> <>"Member" OR "Customer" OR "Request"
> 
> with no success.
> 
> What am I doing wrong? How should I do this?
> 
> Thanks!
0
John
1/25/2008 12:44:47 AM
Hi

When I use

Not in ("Member","Customer","Request")

as the criteria for both fields in one line, then no records display at all 
even though many of them do not contain the aforementioned words.

Using

Not in ("Member","Customer","Request")

in separate criteria lines doesn't work either as some records that 
shouldn't still leak through.

As I said, this is making me nuts!



"John Spencer" <spencer@chpdm.umbc> wrote in message 
news:eFm%23AuuXIHA.1164@TK2MSFTNGP02.phx.gbl...
> Use
>
>    Not in ("Member","Customer","Request")
>
> as the criteria for both fields.
>
> The criteria should be on the same line so that the criteria is ANDed 
> together not Or'd.
>
> '====================================================
>  John Spencer
>  Access MVP 2002-2005, 2007-2008
>  Center for Health Program Development and Management
>  University of Maryland Baltimore County
> '====================================================
>
>
> Craig wrote:
>> Hi
>>
>> Using Access 2007
>>
>> I have a table and two of the fields (Status and OrigStatus) have, among 
>> other possible entries, the words "Member," "Customer" or "Request."
>>
>> I am trying to create a query that only displays records which do NOT 
>> have "Member," "Customer" or "Request" in either field.
>>
>> But it won't work!
>>
>> I have tried putting each word in its own Criteria column in each field 
>> using syntax such as:
>>
>> <>"Member"
>>
>> with no success.
>>
>> I have also tried
>>
>> <>"Member" OR "Customer" OR "Request"
>>
>> with no success.
>>
>> What am I doing wrong? How should I do this?
>>
>> Thanks! 

0
Craig
1/25/2008 4:00:05 AM
Hi Craig,

Using a NOT criteria will also eliminate null records from the dataset.  So, 
if your table has an entry in one field which doesn't match, and a null in 
the other, it will also be excluded.  To cover this possibility, enter the 
following in a single criteria field for each field:

    Not in ("Member","Customer","Request") OR Is Null

HTH,

Rob

"Craig" <msaccess@howardhowardfine.com> wrote in message 
news:uT56ObwXIHA.1208@TK2MSFTNGP03.phx.gbl...
> Hi
>
> When I use
>
> Not in ("Member","Customer","Request")
>
> as the criteria for both fields in one line, then no records display at 
> all even though many of them do not contain the aforementioned words.
>
> Using
>
> Not in ("Member","Customer","Request")
>
> in separate criteria lines doesn't work either as some records that 
> shouldn't still leak through.
>
> As I said, this is making me nuts!
>
>
>
> "John Spencer" <spencer@chpdm.umbc> wrote in message 
> news:eFm%23AuuXIHA.1164@TK2MSFTNGP02.phx.gbl...
>> Use
>>
>>    Not in ("Member","Customer","Request")
>>
>> as the criteria for both fields.
>>
>> The criteria should be on the same line so that the criteria is ANDed 
>> together not Or'd.
>>
>> '====================================================
>>  John Spencer
>>  Access MVP 2002-2005, 2007-2008
>>  Center for Health Program Development and Management
>>  University of Maryland Baltimore County
>> '====================================================
>>
>>
>> Craig wrote:
>>> Hi
>>>
>>> Using Access 2007
>>>
>>> I have a table and two of the fields (Status and OrigStatus) have, among 
>>> other possible entries, the words "Member," "Customer" or "Request."
>>>
>>> I am trying to create a query that only displays records which do NOT 
>>> have "Member," "Customer" or "Request" in either field.
>>>
>>> But it won't work!
>>>
>>> I have tried putting each word in its own Criteria column in each field 
>>> using syntax such as:
>>>
>>> <>"Member"
>>>
>>> with no success.
>>>
>>> I have also tried
>>>
>>> <>"Member" OR "Customer" OR "Request"
>>>
>>> with no success.
>>>
>>> What am I doing wrong? How should I do this?
>>>
>>> Thanks!
> 


0
Rob
1/25/2008 4:26:02 AM
It did help!

That's the solution. Funny things is, I was just consider the "Null factor" 
so to speak -- good timing with your post!

Thanks,

Craig



"Rob Parker" <NOSPAMrobpparker@optusnet.com.au.REMOVETHIS> wrote in message 
news:OwgwspwXIHA.1208@TK2MSFTNGP05.phx.gbl...
> Hi Craig,
>
> Using a NOT criteria will also eliminate null records from the dataset. 
> So, if your table has an entry in one field which doesn't match, and a 
> null in the other, it will also be excluded.  To cover this possibility, 
> enter the following in a single criteria field for each field:
>
>    Not in ("Member","Customer","Request") OR Is Null
>
> HTH,
>
> Rob
>
> "Craig" <msaccess@howardhowardfine.com> wrote in message 
> news:uT56ObwXIHA.1208@TK2MSFTNGP03.phx.gbl...
>> Hi
>>
>> When I use
>>
>> Not in ("Member","Customer","Request")
>>
>> as the criteria for both fields in one line, then no records display at 
>> all even though many of them do not contain the aforementioned words.
>>
>> Using
>>
>> Not in ("Member","Customer","Request")
>>
>> in separate criteria lines doesn't work either as some records that 
>> shouldn't still leak through.
>>
>> As I said, this is making me nuts!
>>
>>
>>
>> "John Spencer" <spencer@chpdm.umbc> wrote in message 
>> news:eFm%23AuuXIHA.1164@TK2MSFTNGP02.phx.gbl...
>>> Use
>>>
>>>    Not in ("Member","Customer","Request")
>>>
>>> as the criteria for both fields.
>>>
>>> The criteria should be on the same line so that the criteria is ANDed 
>>> together not Or'd.
>>>
>>> '====================================================
>>>  John Spencer
>>>  Access MVP 2002-2005, 2007-2008
>>>  Center for Health Program Development and Management
>>>  University of Maryland Baltimore County
>>> '====================================================
>>>
>>>
>>> Craig wrote:
>>>> Hi
>>>>
>>>> Using Access 2007
>>>>
>>>> I have a table and two of the fields (Status and OrigStatus) have, 
>>>> among other possible entries, the words "Member," "Customer" or 
>>>> "Request."
>>>>
>>>> I am trying to create a query that only displays records which do NOT 
>>>> have "Member," "Customer" or "Request" in either field.
>>>>
>>>> But it won't work!
>>>>
>>>> I have tried putting each word in its own Criteria column in each field 
>>>> using syntax such as:
>>>>
>>>> <>"Member"
>>>>
>>>> with no success.
>>>>
>>>> I have also tried
>>>>
>>>> <>"Member" OR "Customer" OR "Request"
>>>>
>>>> with no success.
>>>>
>>>> What am I doing wrong? How should I do this?
>>>>
>>>> Thanks!
>>
>
> 

0
Craig
1/25/2008 4:33:00 AM
Reply:

Similar Artilces:

How do I make a graph ignore zero's in Excel 98
Hi, I set up a chart that graphs changes in quantity over time. It's source data comes from multiple different places in my spreadsheet, so, to make it easy to graph, I use formulas to set it out nicely.. namely, two rows, the top with date and the bottom with quantity. Problem is, that everyday I have to copy the quantity grabbing formula over to the next column. If I leave the formula pre-copied right across, it skews my trend line and makes my graph look terrible because it dives down to zero. If I don't copy the formula, I miss the latest set of data. Essentially, I just wan...

Making New Macros
I am trying to add a macro that will effectivly roll over old statistical information, from one worksheet to the next. As this will be a yearly button, I need it to be able to add this information into the next colum after each use????? iCol = Worksheets("Sheet2").Cells(1,Columns.Count).End(xlToLeft).Column+1 Columns(1).Copy Destination:=Worksheets("Sheet2").Columns(iCol) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "David" <david.jukes@act.gov.au> wrote...

Query to external data not refreshing
I have queries set up in an excel workbook to pull data from queries in an Access database. There has never been a problem with this workbook before, but for some reason when I refresh data the Excel file is not showing the latest changes in Access, rather it is just displaying what was shown when I last updated the data. What could possibly be causing this? -- R Ormerod I am having the same problem. Did you receive an answer to your question? "R Ormerod" wrote: > I have queries set up in an excel workbook to pull data from queries in an > Access database. > >...

Make Recipient list private in sent e-mail
When sending e-mail to multiple Recipients, how do you make others on the To: list private to individual Recipients? You can use the Bcc to field: ie Blind Carbon Copy or create a Distribution list Named Recipient List using your email as the address and use that in the to field. "Daniel" <djkohio@hotmail.com> wrote in message news:2e36b01c46b1b$c4b9daa0$a501280a@phx.gbl... > When sending e-mail to multiple Recipients, how do you > make others on the To: list private to individual > Recipients? Just to clarify, if you're using the Bcc field you need to ad...

Make new replies start at the top of the old message not the bottom
well just as obvious as it sounds, currently when I click the reply button it takes me to the bottom of the old message, it should take me to the top instead. why people have to scroll all the way to the bottom for new message? Thanks This is a multi-part message in MIME format. ------=_NextPart_000_0190_01CB10A3.DE268090 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable one of the options causes thjis. check them over carefully. mikeyshd "Ryu" <mazenko89@gmail.com> wrote in message = news...

How do make a TextBox not display in an Access report when it is .
I have a report with two TextBoxes that I use to display information from a table. Often in the report, one or the other (or both) of the controls are emplty (i.e., no data is returned from the Table. I would like the report not to display the Label of the TextBox in those situations. Change the label to a text box (right-click it.) Put an expression in the Control Source of this text box so it is null when the main one is null. For example, if you want it to show "City:" only when the City text box has a value, use: =IIf([City] Is Null, Null, "City:") -- All...

How do I query multiple data values in same field?
I need to create a query that returns first record of every group. So far, I can write sql statement to pull all occurance of defined groups (i.e. Peach, Apple). But can't pull first record of any group. I'm new to query! How do I accomplish this? Apple (500-record occurances) Orange (400-records occurances) Peach (50-record occurances) banana (20-record occurances) Results: first record of each group:\apple\orange\peach\banana dcMcK wrote: >I need to create a query that returns first record of every group. So far, I >can write sql statement to ...

crosstab queries --> YTD calculations
I am creating a crosstab query where the heading is a date value (i have the format in MMM), the row is a text and the values are numbers. I am hvaing trouble creating a YTD column which differs from a total. If i set my criteria at a month, the total column gives me totals for that one month and not YTD ...

Update query won't work
I want to update data from one table to another, however the destination table contains duplicates of the field i'm using to match against the source table. I only want to update the last instance of the duplicate, not the ones before it. Every update query i've tried to make has not worked because using the Last formula in the query to decide which record should be updated makes the record uneditable and I get an 'Operation must use an updateable query' error. How can I do this update without using a locked query? Thansks! -- Adam Thwaites Access Database Designer ...

Avoid append queries too many times a day
Hello, I created several append queries to append to add previous day data every morning. But, how can I build validation to the query so that I or anyone can't append the queries more than one time a day (which will create double the data)? Thanks Why do you need to add previous day data every morning? How is your table structured? Seem like it would be easier to simply include a date field in your table. If you don't want to do that, you could probably write some code to update a field in the originating table so that after the records were exported, the export date would...

Query problem on table named 'resource'
I can't execute query which is 100% right. It is accepted in different programs but not in excel (Microsoft Query) The Query is: select * from PUB.Resource I think that the problem is because table name is Resource, and this is reserved word either in SQL '92 or Progress Database. Any hint how to get data from this table? Any special signs as [ ], ( ), " ", ' ', ? On 26 Wrz, 13:45, phoenix_me <tomojm...@yahoo.com> wrote: > I can't execute query which is 100% right. It is accepted in different > programs but not in excel (Microsoft Query) > The Q...

Dynamic SQL: Obtaining the Query Plans through DMV's?
Is there a way to construct and run a Dynamic SQL query, and then, immediately afterwards, use DMV's to obtain its Execution Plan from the procedure cache (in other words, without running a trace? If so, are both the estimated and actual query plans available, or just one of them? Behzad Behzad Sadeghi (behzad.sadeghi@gmail.com) writes: > Is there a way to construct and run a Dynamic SQL query, and then, > immediately afterwards, use DMV's to obtain its Execution Plan from > the procedure cache (in other words, without running a trace? It should be. You would ...

MS Query loading problem
Hello all, new to this forum, hope someone can help me. Have used query within Excel for a long time to extract data fro various SQL databases and tables, usually very effectively. However, having been given a new laptop recently (running XP pro an Office 97) I have had intermittent problems getting Query to work. Basically, regardless of whether i try to create a new query or refres one in a saved workbook, Excel just hangs until I terminate it wit ctrl-alt-del. Have tried wiping office and reinstalling, searched MS knowledge bas etc, all to no avail. Anybody else encountered this before...

Subreport based on query: Display/print ID number or Full name onl
I have designed a summary report that shows average ratings respondents give for seminar sessions. I have several subreports based on different queries that give me a Respondents ID number, full name, and any verbatim comments the respondent made. Clients and instructors now only want to see the respondents ID number only if there is no name. I just started learning VB so, I'm way out of my comfort zone here! TextBox FullName's control source is =[RspFname]&" "&[RspLname] RspID (PK) visible property is set to true Private Sub Detail_Format(Cancel As Integer...

query with alot of tables
Hi all,I have one query consist with alot of table,this query is very heavy in the matter of activating it.is there any solution that the query will be lighter? Maybe yes, Maybe no.You need to give more information like the design of the requiredtables and their relationships, as well as expected output of thequeries. Also, if you're dealing with a lot of data then you willwant to make sure that you have appropriate indexes.Cheers,Jason LepackOn Mar 22, 1:09 pm, "thread" <yaniv...@gmail.com> wrote:> Hi all,> I have one query consist with alot of table,> this query...

Best way to make report sections optional ?
I have a report that has several sections of data that are optional. The user indicates this by checkboxes on the form that runs the report. What is the best way to do this, make them subreports even though the data is all in the same table row? mscertified wrote: >I have a report that has several sections of data that are optional. >The user indicates this by checkboxes on the form that runs the report. >What is the best way to do this, make them subreports even though the data >is all in the same table row? Insufficient information ;-) What do you mean by "sections...

Query!
Hi,there! Can I view newsgroup by Outlook 2003? I view them by Outlook Express now! Thanks a lot! Outlook is not a news reader. Keep using OE. -- Russ Valentine [MVP-Outlook] "KejiaLi" <lkj5210@hotmail.com> wrote in message news:e%236Kjar0FHA.3256@TK2MSFTNGP09.phx.gbl... > Hi,there! > Can I view newsgroup by Outlook 2003? I view them by Outlook Express now! > Thanks a lot! > Several: http://www.slipstick.com/addins/misc.htm -- Russ Valentine [MVP-Outlook] "KejiaLi" <lkj5210@hotmail.com> wrote in message news:%23ns7E0r0FHA.2072@TK2MSFTNGP...

Mobile Clip Query
Hi gang, I've been using my mobile phone to capture a few nice moments with my new guide dog Gale. When I replayed a recent clip of Gale and Kola - my retired guide dog - I noticed that as the clip had been recorded in portrait rather than landscape view the video clip is on its side! Anyone know if there is a program available that could rotate the clip so that it can be watched in the normal way. Happy New Year Many thanks Chris -- GodZilla ...

update query with DMax?
Hi All I have to tables and in the first one I have a key (SzemelyId) and in the other table Salary: SzemelyId, Salary, Date, Active) is Szemely Id is foreign key. Each SzemelyId has one or more "Salary" and I would like to make an Update query which select every person last (max) salary and make the "Active" checkbox is TRUE. Just for the max salary. Thanks for your help Janos Remove the Active field! It sounds like it is completely dependent on the foreign key + Date field, i.e. if it were True for a record that was not the latest date, that would be an error. It th...

Visual Studio do make Deployment on Windows 7
HI All, I have tried to use the Visual studio 2005 deployment project to deploy my C# program, and the deployment has custom action in the Installation, but it will fail to install in a windows 7 64 bits OS and the error code is 2869, and i have read the information from the Internet and it seems to be the UAC problem, but i have tried to use ORCA to change the custom action type to 3073 and tried to disable the UAC function in the windows 7, and also tried to use an empty Install method: public override void Install(System.Collections.IDictionary stateSaver) { ...

XPath Query
Hi, I have a web service that returns some XML based on a query made by the client. The order of this data is important and as such I use the following code to achieve this: XPathNavigator xpnNavigator; xpnNavigator = xpdEventDocument.CreateNavigator(); xpnNavigator.MoveToRoot(); XPathExpression xpeEventExpression = xpnNavigator.Compile("event_service/event"); xpeEventExpression.AddSort("@type", XmlSortOrder.Descending, XmlCaseOrder.None, "", XmlDataType.Text); XPathNodeIterator xpiEventIterator = xpnNavigator.Select(xpeEventExpression); The problem is that ...

How can I make my programe running ahead?
Hello,All: I am now getting in problem with a question that I cannot show my CDialog object with function DoModal(), how come did I call the function failed? I traced the function DoModal(), and found it failed at a call-CreateDialogIndirect() function. Is there anybody can tell me how I can deal with it? Thanks in advance! Set a breakpoint at the top of the dialog's DoDataExchange method, and make sure that none of the calls in there throw exceptions. This usually happens when you are referencing a control ID that's not in the dialog. AliR. "Carl" <carl8421@126.c...

problem trying to make a "FitAll" function to a ScrollView
Hi, I'm developing an application where the user is applying filters to an image. I have to display on the left side the original image and on the right side, the modified image. I have an MDI application with a ScrollView derived view. The image are draw in the view like that : BBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBOOOOOOOOOOOBBBFFFFFFFFFFF BBBOOOOOOOOOOOBBBFFFFFFFFFFF BBBOOOOOOOOOOOBBBFFFFFFFFFFF BBBOOOOOOOOOOOBBBFFFFFFFFFFF BBBOOOOOOOOOOOBBBFFFFFFFFFFF BBBOOOOOOOOOOOBBBFFFFFFFFFFF BBBOOOOOOOOOOOBBBFFFFFFFFFFF where B is a border (set to 5 ) OOO : original image FFF : filtered image As membe...

I must make certain I have a specific range of numbers present.
Florida has 67 counties. When I export data from an Acess database into an Excel database, depending on the monthly report, not all 67 counties are present. In order to work with the data in other reports, I need to make sure that column A (the county name/number) has all 67 numeric values. I made a "clean-up" macro that cleans up all unnecessary info, formats, and inserts summation formulae where needed. My problem is I want it to also enter missing county numbers (i.e. make certain 1 - 67 exist) and 0 in the associated columns. I'm including the macro here: ...

Updating a field with an aggregate query
I have a form where people input invoice data. Within that form is a subform where details of the invoice are entered, such as product and amount. The main form is linked to the Invoice table. The subform is linked to the Invoice Detail table. The two tables are joined by the invoice ID. I am trying to update the invoice table with the subtotal of the invoice detail amount. So basically, I want to take the subtotal of the all of the products and update it to a field in the Invoice table called Subtotal. Is there anyway to do that? TKI, it is *really* important not to store the ...