UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Query Confusion, Access 2016    
 
   
Jdk412
post Nov 5 2019, 12:26 PM
Post#1



Posts: 97
Joined: 16-July 18
From: Detroit, MI


Hey guys,

I am creating a query for one of my tables I want to remove the SalesOrdersStatus of Delivered

When I add the criteria to salesorderstatus <> "Delivered" it removes a bunch of the blank lines...

any idea why it would remove the blank lines?

Before Criteria
CODE
SELECT SalesOrders.*, [BalanceDue]*[Price] AS Credit, [Item List].Price
FROM [Item List] INNER JOIN SalesOrders ON [Item List].PartNumber = SalesOrders.PartNumber
WHERE (((SalesOrders.PurchaseOrder)<>"STOCK" And (SalesOrders.PurchaseOrder) Not Like '*ASM*') AND ((SalesOrders.[Job Status])<>"Closed" And (SalesOrders.[Job Status])<>"Canceled"));



After Criteria
CODE
SELECT SalesOrders.*, [BalanceDue]*[Price] AS Credit, [Item List].Price
FROM [Item List] INNER JOIN SalesOrders ON [Item List].PartNumber = SalesOrders.PartNumber
WHERE (((SalesOrders.PurchaseOrder)<>"STOCK" And (SalesOrders.PurchaseOrder) Not Like '*ASM*') AND ((SalesOrders.[Job Status])<>"Closed" And (SalesOrders.[Job Status])<>"Canceled") AND ((SalesOrders.SalesOrderStatus)<>"Delivered"));


Attached File  Query.JPG ( 109.65K )Number of downloads: 5

Attached File  QueryAfterCriteria.JPG ( 36.17K )Number of downloads: 0

Go to the top of the page
 
DanielPineault
post Nov 5 2019, 12:39 PM
Post#2


UtterAccess VIP
Posts: 7,000
Joined: 30-June 11



What about
CODE
SELECT SalesOrders.*, [BalanceDue]*[Price] AS Credit, [Item List].Price
FROM [Item List] INNER JOIN SalesOrders ON [Item List].PartNumber = SalesOrders.PartNumber
WHERE (((SalesOrders.PurchaseOrder)<>"STOCK" And (SalesOrders.PurchaseOrder) Not Like '*ASM*') AND ((SalesOrders.[Job Status])<>"Closed" And (SalesOrders.[Job Status])<>"Canceled") AND ((SalesOrders.SalesOrderStatus<>"Delivered") OR (SalesOrders.SalesOrderStatus Is Null));

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Jdk412
post Nov 5 2019, 12:52 PM
Post#3



Posts: 97
Joined: 16-July 18
From: Detroit, MI


This seem to negate my other criterias?
Go to the top of the page
 
DanielPineault
post Nov 5 2019, 01:03 PM
Post#4


UtterAccess VIP
Posts: 7,000
Joined: 30-June 11



It's just a question of the bracketing. I've update my previous post, but it's just a question of adding

((SalesOrders.SalesOrderStatus<>"Delivered") OR (SalesOrders.SalesOrderStatus Is Null))


--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Jdk412
post Nov 5 2019, 02:13 PM
Post#5



Posts: 97
Joined: 16-July 18
From: Detroit, MI


Awesome! You were only missing one bracket at the end

Thanks for the quick help, I appreciate it.

((SalesOrders.SalesOrderStatus<>"Delivered") OR (SalesOrders.SalesOrderStatus Is Null)) *)*

Go to the top of the page
 
nvogel
post Nov 5 2019, 10:28 PM
Post#6



Posts: 1,038
Joined: 26-January 14
From: London, UK


The reason why your original query excluded the rows without SalesOrderStatus is that SalesOrderStatus contains NULLs on those rows. It's usually a good idea to avoid or minimise the use of nulls in your database. You can prevent nulls by setting the column to be Required=Yes. I mention this because the fact that you didn't realise what was happening here suggests that maybe you didn't intend the nulls to be there. Unfortunately it's perhaps a bit too easy to forget about the Required option and Required=No is the default if you don't specify otherwise.

Hope this helps.
Go to the top of the page
 
dmhzx
post Nov 6 2019, 10:59 AM
Post#7



Posts: 7,115
Joined: 22-December 10
From: England


To expand a bit on nvogels comment

Access doesn't actually know what Null is.
So if you have say, 10 records, and run a query where a certain field = 4 you might find 3 records.
If you -re-run that with <> 4 you might get only two records. The two missing ones being null.

Accounting for 6 of your ten records.
Since access regards null as unknown, it doesn't know if null = 4, and it doesn't know if null <> 4 either.

In your case another solution would be to change the 'field' part to
[Field] & "" <> "Delivered".

This adds an empty string to whatever is in the field, so Nulls become an empty string.

Hope that helps a bit
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2019 - 10:33 AM