Non MR's and Blanks to show

Good afternoon,

I have a table that shows the following text fields -

Date - Associate - Status - StatusCode - Method -
each of these fields are text fields (except for date is Date field)

I am trying to set up a query in design view to say the following:

For Date [parameter], group by associate, group by status (if the status does
not equal "ROUTED"), show only the statuscodes that are <>MR or BLANK.

My problem is the last statement. Basically what is happening when I say
"show me the non MR's" it is pulling all the non MR's but also disregarding
the blank fields. It is just pulling data that has something in it but i
don't want that.. i won't both... Non MR and BLANKS. The blank fields have no
data at all in em'. No Null, No 0, No space. 

I am not sure how to pursue this but it is killing me.. probably the easiest
thing. Does that make sense..

Thank you everyone. 

Thanks everyone.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200802/1

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

3 Replies
828 Views

Similar Articles

[PageSpeed] 49

Try this ---
SELECT instero911.Date, instero911.Associate, instero911.Status, 
instero911.StatusCode, instero911.Method
FROM instero911
WHERE (((instero911.Date)=[Enter parameter]) AND 
((instero911.Status)<>"ROUTED") AND ((instero911.StatusCode)<>"MR" And 
(instero911.StatusCode) Is Null)) OR (((instero911.Date)=[Enter parameter]) 
AND ((instero911.Status)="ROUTED"));

-- 
KARL DEWEY
Build a little - Test a little


"instereo911 via AccessMonster.com" wrote:

> Good afternoon,
> 
> I have a table that shows the following text fields -
> 
> Date - Associate - Status - StatusCode - Method -
> each of these fields are text fields (except for date is Date field)
> 
> I am trying to set up a query in design view to say the following:
> 
> For Date [parameter], group by associate, group by status (if the status does
> not equal "ROUTED"), show only the statuscodes that are <>MR or BLANK.
> 
> My problem is the last statement. Basically what is happening when I say
> "show me the non MR's" it is pulling all the non MR's but also disregarding
> the blank fields. It is just pulling data that has something in it but i
> don't want that.. i won't both... Non MR and BLANKS. The blank fields have no
> data at all in em'. No Null, No 0, No space. 
> 
> I am not sure how to pursue this but it is killing me.. probably the easiest
> thing. Does that make sense..
> 
> Thank you everyone. 
> 
> Thanks everyone.
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200802/1
> 
> 
0
Utf
2/6/2008 12:28:02 AM
On Wed, 06 Feb 2008 00:14:25 GMT, "instereo911 via AccessMonster.com"
<u27979@uwe> wrote:

>Good afternoon,
>
>I have a table that shows the following text fields -
>
>Date - Associate - Status - StatusCode - Method -
>each of these fields are text fields (except for date is Date field)
>
>I am trying to set up a query in design view to say the following:
>
>For Date [parameter], group by associate, group by status (if the status does
>not equal "ROUTED"), show only the statuscodes that are <>MR or BLANK.

Use a criterion on statuscode of

(<> "MR" OR IS NULL)


             John W. Vinson [MVP]
0
John
2/6/2008 5:26:11 PM
Thank you both.. It worked. I knew it was easy.. - Maybe I should go to beg
access school :)

Thanks again 

KARL DEWEY wrote:
>Try this ---
>SELECT instero911.Date, instero911.Associate, instero911.Status, 
>instero911.StatusCode, instero911.Method
>FROM instero911
>WHERE (((instero911.Date)=[Enter parameter]) AND 
>((instero911.Status)<>"ROUTED") AND ((instero911.StatusCode)<>"MR" And 
>(instero911.StatusCode) Is Null)) OR (((instero911.Date)=[Enter parameter]) 
>AND ((instero911.Status)="ROUTED"));
>
>> Good afternoon,
>> 
>[quoted text clipped - 20 lines]
>> 
>> Thanks everyone.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200802/1

0
instereo911
2/6/2008 5:50:27 PM
Reply:

Similar Artilces:

How do I get rid of blank pages between each page of reports
I am having a blank page put in between each of the pages of my reports and it is throwing off the next page layout, how do I get rid of these blank pages? -- Allen Most likely the design width of your report slightly exceeds 8-1/2" minus the left margin minus the right margin you have set in Page Setup. This will cause a blank page between each of the pages of your report. Decrease the design width of your report or set your left and right margins smaller. Steve "Allen G." <AllenG@discussions.microsoft.com> wrote in message news:B98A069A-C871-4C6F...

My chart is not showing; it's all grayed out.
I created a very simple 3-D pie chart which used to work just fine. Recently, everytime I open the file, the chart is all grayed out. I deleted it and recreated it over and over again. It just won't show anymore. Just grayed out! Anyone has any idea why? Hi, Check the setting Tools > Options > View > Object: Show All is checked. Sounds like you currently have Show Placeholders set. Cheers Andy mmm wrote: > I created a very simple 3-D pie chart which used to work just fine. > Recently, everytime I open the file, the chart is all grayed out. I deleted > it a...

show with messaje box when it's time and date
HELLO FRIENDS NEED HELP I HAVE THE COLUMN (A) The time to write date and hour Next notice /Alarm, I need Warn me when it's time and date of notice/alarm start up show with messaje box Message with the message that I write in the same row of column ( B) in the same row EXAMPLE: A B 1 26/04/2010 10:00 AM payment LIGHT 2 03/05/2010 4:20 PM Remember payment Phone 3 06/04/2010 8:40 AM Appointment with Dr. Saludo KM**VE hi, KM ! ...

credits not showing in expense report?
i created a report off money 2004's spending by category. it's working fine so far until i realized that it doesn't include credits. what i man is it shows how much i paid for rent, but it doesn't show how much my wife gave me for rent. for example, it will show $1000 vs. $500 net off my account. now if i do a "find transaction" listing and create a report off that, it shows the credits. but i cannot save that report because it's driven from "find transaction" rather than from the reports section. any thoughts? does money 2006 fix this? In microsoft....

remove blanks from a string of chars within a cell?
trim -- Don Guillett SalesAid Software donaldb@281.com "rayhollidge" <rayhollidge@discussions.microsoft.com> wrote in message news:846DD838-1EA5-42F3-BD8B-D1AD481D181F@microsoft.com... > All the blanks? =SUBSTITUTE(A1," ","") Lots of cells and permanently - Select range and use Edit / Replace, replacing space with nothing. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to ...

How do I get my chart to show data across the new year?
I have a data chart of attendance. It covers every Sunday's attendance from June 2009 to June 2010. However, the line graph stops at 12/27/09. For some reason it won't show the graph past that. I have checked the source data and it covers all the way through June 2010. What am I missing? Look at the Source Data parameters in the chart. Chart menu/ Source Data ... -- David Biddulph Susan H. wrote: > I have a data chart of attendance. It covers every Sunday's > attendance from June 2009 to June 2010. However, the line graph > stops at 12/27/09. For...

detailed inventory does not work with non bar code tapes.
I would like to verify that the scheduled inventory still does NOT work with non barcoded tapes. We have a HP 1/8 tape inventory using 8 non bar coded tapes. The scheduled inventory runs every day for about 1 second and does NOT reveal what the tapes are. A manual detailed inventory does work. I wonder why MS would program it this way? Tim This is an issue which we have fixed in DPM 2010. The problem was that the scheduled inventory was not doing detailed inventory on newly added media. Can you install DPM 2010 Beta from https://connect.microsoft.com/Downloads/Downloa...

Shared calendar does not show text in appointments
I've had a request for help because a user on our network, who has access permission to her bosses calendar, has a problem. When the PA sets new appointments she can see botht he new item and all the other appointments, whether she set them up or her boss did. However, when the Boss looks in the calender for herself, the ones set up by the PA show as blank spaces, but the subject line does not show the text. The PA knows how wto do this and IS using the subject line properly to detail what the appointment is for. Honestly, I'm baffled - I've never heard of this before. Any ideas ...

Clicking on "Help" in Word 2007 results in a blank help window.
Instead of the online help displaying as it should, the content of the Help window is completely blank. The user reports this happened quite a while back, so doing a System Restore would be shooting in the dark. Is there some registry change to make to get this working as it should? ...

appointment details show up when others request meeting
When I use the meeting request feature (Ctrl-Shift-Q), I see a graphic display of my calendar and the calendars of other attendees. For some attendees, I see only blue bars for their appointments... for other attendees, I apparently have permission to see the subject of the appointment as well, written on each blue bar. At the moment, this is apparently turned on for all who view *MY* calendar in this manner. How can I turn this off? Thanks! Mark ...

Home page "Choose a Task" is all blanks!
Recently upgraded from Money 2002 to 2004. My "Custom" home page is now a blank page; the "Choose a Task" and "Choose a View" pull-down menus are blank, too. If I pull-down the (blank) menus, Money crashes with the typical "Send Error Report" window. I've tried reinstalling Money... didn't help. Anybody know what's happening, here? Which file saves my custom configuration - should I delete it? Thx&Rgds...mvm In microsoft.public.money, mmiller wrote: > >Anybody know what's happening, here? >Which file saves my custom c...

Tabs accross of bottom of sheet are not showing
I have done the following (outlined below) per the excell help wizard and the sheet tabs still do NOT show across the bottom. Also applies to the horizonal scrollbar Sheet tabs aren't listed across the bottom of the screen. Display or hide all sheet tabs On the Tools menu, click Options, and then click the View tab. Select or clear the Sheet tabs check box. When displaying sheet tabs, you may also need to expand the tab scrolling area. Hi have you run detect & repair from the help menu? -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm worki...

Non Standard Font Usage
How can I use non standard fonts in my app using MFC? Say, if I have a font file "abc.ttf", how can I use it in my app (in dialogs, CViews and probably whole GUI itself). How do I ship it with the application, etc. If simply having it in Windows>Fonts folder is enough, how can I have it installed it there? I have never installed a Font programatically. warm regards, D Rathore "divya_rathore_@gmail.com" <divyarathore@gmail.com> wrote in message news:3c51e93f-0ead-4d4d-b9b3-22b4edef9546@m44g2000hsc.googlegroups.com... > How can I use non standard fonts in my ...

Money not showing 401k account balance properly
Hi, I have MS Money 2005 Standard OEM Edition. It does not show my 401k account balances properly. It subtracts the cash I contributed to 401k account from the balance and shows the remaining as my current balance. What am I doing wrong? I want to see total value of account and I can't seem to find an easy way to do that. Please advice. Thanks! In microsoft.public.money, flash0777@gmail.com wrote: > >I have MS Money 2005 Standard OEM Edition. It does not show my 401k >account balances properly. It subtracts the cash I contributed to 401k >account from the balance and shows...

Sum of all numbers before a blank row
I have an excel file which i have sorted by column 'Ordered Item'. Next to this column is a 'Price' column. The 'Ordered Item' Column may have duplicates. What I need to do is put a subtotal beside each group of 'Ordered Items' (even if they are single items). How would I sum the Prices every time there is a change in the data in column 'Ordered Items'? Ordered Items Price 241 50 241 50 340 25 600 100 600 100 So if the sheet looks like this, how ...

Inventory transfers
When I do a transfer from one site to another eg. North to Warehouse, then I restrict the Item Transaction Enquiry screen to only show 'Warehouse' site... the transfer does not show on the screen. It only shows on site transferred FROM. The transfer quantity also shows as a positive number, it should show as negative because you are transferring it out of the site 'Warehouse'. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in t...

In one section of my Word doc, when I type nothing shows up on do
In my Word document, there is one section where the words do not appear when I type them. When I go to "show", there is a wriggly red line where I had wanted the words to appear. Seems as if the text has been formatted as hidden. Select the text and clear the "Hidden" attribute in the Font dialog box. -- Stefan Blom Microsoft Word MVP "packbakr" <packbakr@discussions.microsoft.com> wrote in message news:28C78FA7-95BE-4988-ABA1-63E3A1893409@microsoft.com... > In my Word document, there is one section where the words do not appear ...

Page number shows "#" instead of number
Using Office 2007, I am viewing a Powerpoint presentation created by someone else. No matter what view (master, normal or printed out) the page number in the footer is expressed as "#" instead of the actual number. Two other people viewing the same presentation see the page numbers instead of the "#", so this must have to do with my own computer set-up. Any suggestions? What happends if you go to master view and in EACH of the layouts slide number area delete the<#> and then Insert > SlideNumber? -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, ...

Inbox showing messages count but nothing is showing on right pane
In Outlook 2002 after deleting all messages in the inbox, the inbox message count will show that you have two messages in the left pane, but the messages pane on the right is empty. Any idea why this is happening. Hope I explained my question clearly Conduct an advanced find on the inbox and include any type of Outlook file - what shows up in the results box? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searchi...

Function or formula to show currently active workbook name in cell
Hi all. Excel 2007 / XP Pro. Is there a function or formula that will display the currently active workbook name (without the path or filename extention) in a cell on the currently open sheet in that workbook? There will be other open workbooks. Appologies if this is a stupid question. One way =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1) Or see: http://www.ozgrid.com/VBA/WorkbookPath.htm -- Regards Dave Hawley www.ozgrid.com ...

Removing blank spaces from the ends of email fields?
Hi, I have a list of emails in a column but many of them have a space at the end of the email address. I need to be able to remove the space and then run an advanced filter on them to remove any duplications. An example of the data that has a space on the end which I want to remove would be: alcssystems@hotmail.co.uk I have already tried using =trim(A1) and =SUBSTITUTE(A1," ",""), neither of which work when I copy the values back into my original column to run the advanced filter. Please help me. Many thanks in advance -- nozzaworld You ...

need to make cell blank if no data in that row
I have a data in columns, I want data to be shown only if there is data in that row if no data then leave it blank (here is what I am asking for) Stock trade Gain/Loss Gain/Loss all trades abc 4.18% 4.18% def 3.77% 7.95% [no trade] N/A 7.95% [no trade] N/A 7.95% i want trade column to appear blank in cell of gain/loss for all trade instead of showing 7.95%. if no trade made If there is no data but the formula is in column, third line shows 7.95%, 4th line shows 7.95%, 5th line 7.95...

How do I only show only 5 Weeks?
Ok, if I have the Week to start on SUNDAY the Calender shows 6 Weeks If I have the Week to start on MONDAY the calender shows 5 Weeks. The 6 week version cuts off my afternoon appointments so I sometimes miss them. -- How do I set it to show 5 Weeks starting on a Sunday?-- I tried all the versions of Years starts on... and other settings, it seems to be controlled by the Sunday/Monday choice... -- Bob Sisson www.inspectionsbybob.com What version of outlook? Did you try this method: http://slipstick.me/2wk -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www...

Chart showing percent change
Any suggestions on how to do this? I need to graph a chart that depicts "percent change" in 30 stocks. B61:B90 contain the stock symbol C61:C90 contain the current price D61:D90 contain the change E61:D90 contain the percent change Not sure of the best way to present this. -- jermsalerms ------------------------------------------------------------------------ jermsalerms's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30167 View this thread: http://www.excelforum.com/showthread.php?threadid=543965 Select B61:B90, hold CTRL and select E61:E90 (so...

Odd Characters showing up in e-mails sent to contacts
When we send out e-mail from CRM sometimes our customers complain that odd characters show up in the e-mails. The characters usually look like this “, this ” or this †Any ideas on how to prevent this? Sometimes this occurs if you compose the email in another application and paste it into a CRM email template or email form. Try re-typing the email directly in the CRM template or form. -- Matt Wittemann, CRM MVP http://icu-mscrm.blogspot.com "Steve Bambauer" wrote: > When we send out e-mail from CRM sometimes our customers complain that odd > ch...