Erratic results from query criteria

I am getting different results from running the same query with the same 
selections.  One moment it is all behaving as expected, the next it has gone 
haywire.  (I have done what appears to me to be EXACTLY what I have done in 
another database, where it works perfectly every single time.)

In a query I have, amongst others, the following fields:

Category
SubCategory
Company

I want to be able to select any OR ALL of the relevant fields.  I have the 
following criteria:

Like "*"&[Type Category otherwise leave blank for ALL]&"*"
Like "*"&[Type SubCategory otherwise leave blank for ALL]&"*"
Like "*"&[Type Company otherwise leave blank for ALL]&"*"

At one stage, I got it running perfectly with the Company one having Is Null 
in OR.

I have them all in a row.  I tried them stepped down from each other, but 
that didn't work either.  What I want is to be able to press OK or Enter on 
all of them for all records, or to type in a selection from one of the fields 
when I so wish.

What is puzzling me the most, is the erratic results.  

I even tried running Microsoft Diagnostics.  I can't remember if that 
helped, but if it did, it didn't for long.

Thank you for any help.


0
Utf
2/26/2010 12:58:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1496 Views

Similar Articles

[PageSpeed] 47

Try modifying like this --
Like "*"&[Type Category otherwise leave blank for ALL]&"*" OR Is Null
Like "*"&[Type SubCategory otherwise leave blank for ALL]&"*" OR Is Null
Like "*"&[Type Company otherwise leave blank for ALL]&"*" OR Is Null

-- 
Build a little, test a little.


"Owl" wrote:

> I am getting different results from running the same query with the same 
> selections.  One moment it is all behaving as expected, the next it has gone 
> haywire.  (I have done what appears to me to be EXACTLY what I have done in 
> another database, where it works perfectly every single time.)
> 
> In a query I have, amongst others, the following fields:
> 
> Category
> SubCategory
> Company
> 
> I want to be able to select any OR ALL of the relevant fields.  I have the 
> following criteria:
> 
> Like "*"&[Type Category otherwise leave blank for ALL]&"*"
> Like "*"&[Type SubCategory otherwise leave blank for ALL]&"*"
> Like "*"&[Type Company otherwise leave blank for ALL]&"*"
> 
> At one stage, I got it running perfectly with the Company one having Is Null 
> in OR.
> 
> I have them all in a row.  I tried them stepped down from each other, but 
> that didn't work either.  What I want is to be able to press OK or Enter on 
> all of them for all records, or to type in a selection from one of the fields 
> when I so wish.
> 
> What is puzzling me the most, is the erratic results.  
> 
> I even tried running Microsoft Diagnostics.  I can't remember if that 
> helped, but if it did, it didn't for long.
> 
> Thank you for any help.
> 
> 
0
Utf
2/26/2010 4:25:03 PM
Firstly, I don't know whether you click Notify Me Of Replies in all your 
posts or not so you may not have seen my very belated reply to a November 
2009 question I posted about Yes/No/Null criteria in a checkbox field in a 
query.  I forgot to click Notifly Me Of Replies and must have been very 
stressed and busy and forgotten to check if there were answers to my 
question.  Anyway, I saw it today and was mega-excited with your reply.  It 
worked and has made and will continue to make a huge and exciting difference 
to my life (as it did to somebody else as well who posted there too!).  Thank 
you.

Secondly, than you for this reply.  I DID try that, and as I mentioned in my 
post, I had success with Is Null in the Company field.  

However, I think she's got it (à la "My Fair Lady") - the rain in Spain - 
and all that!  
I have realised what the problem was.  I had amalgamated 3 tables and hadn't 
normalised the amalgamation, but I think the huge problem was that I had 3304 
records in it already, and the amalgamation was messy and time-consuming.  

I have since re-created the database from scratch and only added records 
after I had normalised it, and checked that all the relationships are nice 
and neat.  Now the criteria are working, but I only have a few sample records 
in it, but will keep checking as I add a few more (à your very useful motto 
of "Build a little, test a little", which I have always loved every time I 
have read it in your responses to me and to others.  

Once I have the database skeleton complete and I am sure that it is working 
perfectly, I will try to split the previous one in the same way that I have 
done this one, but I think I will have to clean up some of the data first, 
and then hopefully it will work.  

Thank you very much for your time, and an especial thank you for the 
Yes/No/Null solution.  

Owl

"KARL DEWEY" wrote:

> Try modifying like this --
> Like "*"&[Type Category otherwise leave blank for ALL]&"*" OR Is Null
> Like "*"&[Type SubCategory otherwise leave blank for ALL]&"*" OR Is Null
> Like "*"&[Type Company otherwise leave blank for ALL]&"*" OR Is Null
> 
> -- 
> Build a little, test a little.
> 
> 
> "Owl" wrote:
> 
> > I am getting different results from running the same query with the same 
> > selections.  One moment it is all behaving as expected, the next it has gone 
> > haywire.  (I have done what appears to me to be EXACTLY what I have done in 
> > another database, where it works perfectly every single time.)
> > 
> > In a query I have, amongst others, the following fields:
> > 
> > Category
> > SubCategory
> > Company
> > 
> > I want to be able to select any OR ALL of the relevant fields.  I have the 
> > following criteria:
> > 
> > Like "*"&[Type Category otherwise leave blank for ALL]&"*"
> > Like "*"&[Type SubCategory otherwise leave blank for ALL]&"*"
> > Like "*"&[Type Company otherwise leave blank for ALL]&"*"
> > 
> > At one stage, I got it running perfectly with the Company one having Is Null 
> > in OR.
> > 
> > I have them all in a row.  I tried them stepped down from each other, but 
> > that didn't work either.  What I want is to be able to press OK or Enter on 
> > all of them for all records, or to type in a selection from one of the fields 
> > when I so wish.
> > 
> > What is puzzling me the most, is the erratic results.  
> > 
> > I even tried running Microsoft Diagnostics.  I can't remember if that 
> > helped, but if it did, it didn't for long.
> > 
> > Thank you for any help.
> > 
> > 
0
Utf
2/26/2010 10:31:01 PM
On Fri, 26 Feb 2010 14:31:01 -0800, Owl <Owl@discussions.microsoft.com> wrote:

> I don't know whether you click Notify Me Of Replies 

Doesn't matter: that feature has been broken for months, and no notifications
are being sent.
-- 

             John W. Vinson [MVP]
0
John
2/27/2010 12:18:21 AM
Reply:

Similar Artilces:

Result of one combo box, affecting results of another...
Just out of curiousity... Is there a way to have what the user selects in one combo box on a form, affect what is available for the user on another box!? For instance... Box A has 8 different addresses.. Within those addresses there are 12 different building ID locations... Instead of having all 12 building locations for each address always appear in the combo box, If the user picks building 4, is there a way to just have the building ID options for building 4 showing in combo box B? Charles! Use your favorite search engine. Use "Cascading Combo boxes" for search terms. -- Reg...

Print the results of a search
After I do a search via the Tools menu, I want to print the results of the search. How do I do that? Edit|find???? There's nothing built into excel that does this: But you could use a macro... Option Explicit Sub testme01() Dim curWkbk As Workbook Dim wks As Worksheet Dim RptWks As Worksheet Dim oRow As Long Dim FoundCell As Range Dim FirstAddress As String Dim FindWhat As String FindWhat = InputBox(Prompt:="Find What?") If FindWhat = "" Then Exit Sub End If Set curWkbk = ActiveWorkbook Set Rp...

Merge Web Query
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C46836.1DF42A00 Content-Type: text/plain; charset="iso-8859-9" Content-Transfer-Encoding: quoted-printable Hi, Is there a way to merge two web queries (.iqy files) editing with = notepad. I dont want to use Excel. I couldn't find any info about the issue. Thanks in advance Atacan ------=_NextPart_000_0008_01C46836.1DF42A00 Content-Type: text/html; charset="iso-8859-9" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">...

Workflow is erratic
I have a simple workflow which works and sometimes does not. The same user is logged in, but for no apparent reason, the same data which should fire off the workflow does not. Then it does, then it does not...... Any ideas? -- Paul Doyon CRM Consultant Which version of CRM are you using? 3.0 or 4.0? If 3.0, do you have Update Rollup 2 applied? -- Matt Wittemann, CRM MVP http://icu-mscrm.blogspot.com "Paul" wrote: > I have a simple workflow which works and sometimes does not. The same user > is logged in, but for no apparent reason, the same data which should fir...

Count records in a query
Hello! I have a query that runs pretty slow, and I'd like to alert the user before running the query. So I have a source query for my slow query, and I'd like to count the records in my source query and estimate the time the query will take to run. How can I do this in VBA? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201005/1 DCount("*","Your Source Query Name") This will give you the number of records in the Query "AtleDreier via AccessMonster.com" wrote: > Hello! >...

#VALUE! Occuring in formula result
I have a formula in cell A1: =IF(ISNA(VLOOKUP(B9,ALL!A6:E1091,5,FALSE)=TRUE),"",VLOOKUP(B9,ALL!A6:E1091,5,FALSE)) I used this formula to eliminate the #N/A that would appear if no value is found in the vlookup. The result in A1 was #N/A, but now it is blank due to ISNA formula. However, now if I add cell A1 with others cells. =SUM(A1:A10), it returns #VALUE!. The blank cell as a result of the ISNA formula is causing this cell not to add. Please help.....Thanks, Richard --- Message posted from http://www.ExcelForum.com/ Use a zero instead of "". -- Kind Reg...

how to call a program and read it's results
I want to run a console program in a sub process and read it's outputs for next operate is there any way to do this sample codes is appreciated "longying" <longying@myinfo.net.cn> wrote in message news:uLI%23tOJ6DHA.3896@TK2MSFTNGP11.phx.gbl... > I want to run a console program in a sub process and read it's outputs for > next operate > is there any way to do this > sample codes is appreciated > Redirect the output of the program to a file. DoStuff > out.txt Tom. ...

How do you make the results of a concatenate permanent text?
I have two columns of text which I have concatenated but I need the results to be text not just the results. I have tried copying the column to another column - that doesn't work so.................. Did you try PasteSpecial Values? "Susan" <Susan@discussions.microsoft.com> wrote in message news:343DF1BB-6AA7-4107-9BF6-FCFBA02F29C8@microsoft.com... > I have two columns of text which I have concatenated but I need the results > to be text not just the results. I have tried copying the column to another > column - that doesn't work so...............

Excel Array Formula: Multiple Criteria Sum IF Challenge
Currently, I have the following Excel Worksheet Invc No Code Status Charges RejCode 291 CH no pay 50 291 CH no pay 50 291 PY no pay ded 152 CH no pay 50 152 CH no pay 25 152 PY no pay dat 206 CH no pay 50 206 CH no pay 50 206 PY no pay 507 CH no pay 50 507 CH no pay 45 507 PY no pay ded 600 CH overpaid 25 600 CH overpaid 25 600 PY overpaid ded I would like to obtain the following results, Total Charges by Rejecte "no pay" invoices and the specific "no pay" invoices with rejections a displayed below: Total Charges by Rejected "No pay"...

Pointer is erratic
Hi, Mouse Pointer is erratic ever since I hadn't used my computer for several months. It was fine prior to long lapse of unuse; now it is difficult to control; seems to have a mind of it's own. I mean it's ok when it stops. But always jumps once when I move it. I reviewed all my settings and did a Restore but the problem still exists. Would appreciate if someone could help me. -- Thank you, Jean On 10 Aug 2010, =?Utf-8?B?Ymllcm1hbm9za2k=?= <biermanoski@discussions.microsoft.com> wrote in microsoft.public.windowsxp.general: > Mouse Pointer is e...

max out results of formula...
I have the following formula that calculates the weight of a performance based on a weight scale. H2=GOAL G3 = PERFORMANCE G25 = WEIGHT (30 POINTS) =(H$2/(G3))*G$25 How do I get the results to max out at 30 if the goal is met? =MIN(30,(H$2/(G3))*G$25) Gord Dibben MS Excel MVP On Mon, 29 Oct 2007 12:23:18 -0700, "J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote: >I have the following formula that calculates the weight of a >performance based on a weight scale. > >H2=GOAL >G3 = PERFORMANCE >G25 = WEIGHT (30 POINTS) > > >=(H$2/(G3))*G$25 &...

Using A Find Result in a Range
Hello, I'm trying to search for a word on a spreadsheet, in this case the word "Label." I then want to copy everything from A1 to the row above that word "Label" and paste it to a new spreadsheet. I've got everything worked out except how to use the Find Result address in a new range. I'm sure it's simple, so simple I can't find it in any tutorial. Currently I have to look in each spreadsheet for the last row of the range I want to copy and type the copy range manually into the code for each of the multiple spec sheet spreadsheets I'm att...

SQL Query Problem #2
Hi there, Ive created a sql query that will be returned to an microsoft excel spreadsheet. The problem im experiencing is that the schema names are not presented in the right columns on the microsoft excel spreadsheet. Is it possible if anyone could tell me what is causing this problem? below is the SQL query SELECT quote.owneridname, quote.customeridname, quote.new_agency,QuoteCreatedOn = Convert(varchar(10), Quote.CreatedOn, 101), quote.name, quotedetail.priceperunit, quotedetail.new_loading, quotedetail.new_subtotal, quotedetail.new_mandiscount, quotedetail.new_subtotal1, quotedetai...

CRM 3 E-Mail router erratic
Hi there I have got CRM routing email from Exchange to a support queue. Exchange uses the POP3 connector to pull mail from the ISP. The problem I am having is that some mail gets routed to CRM and others do not. It seems to be a completely random selection of mail that gets put into CRM. I have set up the support rule to forward all mail through to CRM. I have gone through everything I can think of including reinstalling the CRM router, removing the user and recreating it, removing and redeploying the forwarding rule. Any help with this would be appreciated! Ok, since the mail is routing ...

MS Query #6
Hi, Just for curiosity. MS Query has a status bar with seven boxes on the right. I only get 4, 5 and 6 working: [Caps], [Num] and [Scrl]. Do the other boxes have a function? And if so: what function? Thanks for a reply, Frans ...

Data Fields the same, but not giving any results
I have a query that I have built and in the query there are 2 tables. I have a "Team Leader" data field in each table. I have made the relationship between the 2 tables by using the "Team Leader" data field. If I run the 2 tables seperately, it give me information. When I run the query, it does not give me any information. The "Team Leader" field is a text field and it has a name in the field. Any idea why the relationship will not work? Post the SQL statement of the query that you're trying to run. -- Ken Snell <MS ACCESS MVP> &...

Formula shows in cell instead of results (text reference)
I have a file with 1,000's of forumlas in it. One tab alone has over 3,400 formulas. There are 20 tabs that pull a job category name from the "rates" tab. For year 1, the formulas start in A9 and go to A63. I have multiple years that deal with the same job categories and the information for Years 2 - 5 starts on row 106 and continue down. If I put a formula in A106 that says + or = A9, the cell displays the forumla and not the job category name. Cell A106 is formatted as text which it should be. I have tried the edit format and change A106 to text then hit...

Query Results Where Qty Shipped is not Divisable by 4
How would I construct a query to return all invoices where the qty shipped for a specific item is not divisable by 4? Really its the divisable by 4 part need assistance with. Thanks! SELECT * FROM YourTableName WHERE YourFieldName Mod 4 <> 0; -- Ken Snell http://www.accessmvp.com/KDSnell/ "meangene" <meangene@discussions.microsoft.com> wrote in message news:2684394D-463B-4804-970F-79205F41C039@microsoft.com... > How would I construct a query to return all invoices where the qty shipped > for a specific item is not divisable by 4? Rea...

Erratic Mouse
My PS/2 mechanical mouse suddenly developed a mind of it's own so I purchased an optical mouse. One week later it is acting the same as the mechanical mouse. Drag and drop more often drops rather than drags and other times it will suddenly stick and then move around the screen without any input from myself. Has anyone any ideas please? "DeeTee" <djthorne@ntlworld.com> wrote in message news:%23p7CqSW6KHA.5464@TK2MSFTNGP05.phx.gbl... > My PS/2 mechanical mouse suddenly developed a mind of it's own so I > purchased an optical mouse. One week l...

Printing results on different pc and different printer
Hi, regarding printing functions in windows i am a newcomer. Thats why i dont understand some phenomenon : I am working with MFC. My report uses Times New Roman and Arial. Report was developed under PC A with Epson Printer. PC B using Epson produces the same output. PC C with Lexmark produces a report with tiny capitals, that are barely readable. PC A with lexmark Printer produces a readable result, but different to Epson Printer. What did i forget to implement in my program? How can i produce a result, that is independable from the printer used? Thanks in advance, Matt Are escape seque...

Joining 2 mailing lists in query
I have a master mailing list in a table consisting of about 40,000 records for about 50 different sales representatives. When a new sales person is hired, they bring their own mailing lists in with them which cannot be added to our master list. I would like, however, to have each individual list in the database and be able to, when they want to do an individual mailing, pull records from both their current and their old lists in one query. It seems like it should be rather easy but I cannot figure out how to do it. Hi Ratat, Have you tried a make table query? Join the two tables ...

Omit data based on criteria in reports
I have a report which gives an employee's productivity rating, based on a set standard. This standard depends on what product the employee is running at the time. However, some times the employees run products that do not have standards associated with them (trial products for instance). A daily report tells: Employee Product How much was produced How much was expected (standard) % Expected (How much was produced/How much was expected) When a trial product is run on a given day, the % Expected number is Null (=IIf([How much was expected] = 0, Nul...

use expression as field name in query
I'm trying to use an expression to create a field name in a query. I have an "Employee" file. that has fields "Sun", "Mon", "Tue" ... "Sat" that contain their normal number of hours scheduled. I have a function DayName(schdate) that uses the Weekdayname(date) to get the day of the week, based on a date field, that returns the string "Mon" or "Tue" etc. I want to use the resulting DayName as the fieldname to get the normal #hours. I have a function that returns the string "Mon" or "Tue" correc...

Cascaded queries
I need to produce a list of club members that had renewed their membership by a certain date and exclude those who joined after this date. OK easy so far, but I need to then say how many are family heads and how many are spouses, cadets, etc. requiring further queries based on the first query. Is there some way the first query can form a temporary table so that I don't have to type in the date criteria several times to get the numbers I require. Colin Create a form not linked to any table (AKA unbound form) named frmSearch. On this form put a text box named txtMembershipDate. A...

Search results
Search in this forum keeps saying no results. Do i need to do something. Thank -- proje ----------------------------------------------------------------------- projem's Profile: http://www.msusenet.com/member.php?userid=7 View this thread: http://www.msusenet.com/t-131404 Yes. --� 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 furious head scratching, projem asked: | Search in this forum keeps saying no results....