Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-11-2019, 04:38 PM   #16
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,133
Thanks: 15
Thanked 1,570 Times in 1,492 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: strDocName Error

@Micron,
Null is equivalent to "Not True" so at it's simplest,
a<> b
when either a or b is null, the result is null which is treated as false when most people would want the result to be true because it is "true" that the two fields are not equal.

The upshot of this is --- be careful in how you construct your conditionals so you take into consideration the possibility that one or more of the arguments might be null
Attached Images
File Type: jpg testNull.JPG (32.3 KB, 67 views)

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-11-2019, 07:00 PM   #17
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 932
Thanks: 10
Thanked 189 Times in 179 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: strDocName Error

You're not telling me anything that I don't already know.
Maybe this is all just semantics, but I'm not going to agree that "Null is equivalent to "Not True"" because Null is not equal to anything. Equivalent means equal or corresponding to. Null is neither equivalent, corresponding or equal to anything. I'm sure I don't have to tell you that, so I mention it for the benefit of others. Your example is obvious as I've just alluded to: b is not equal to null, thus the expression evaluates to null. I think I already showed that with my immediate window post results so I don't get why you posted that.

Still would like to know what a "text of a null" is. Or let's just move on?
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 10-11-2019, 07:11 PM   #18
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,133
Thanks: 15
Thanked 1,570 Times in 1,492 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: strDocName Error

People who are new to development don't understand the implication of null. I'm sure you do understand it but I'm trying to get to a basic clarification of exactly what the impact of a null operand is. arnel suggested that a<>b would return false if one of the operands was null. I agree that statement is technically incorrect because it will in fact return null (which was the objective of the debug.print exercise) but in practice, the result is treated as false when used in a where clause or an If statement because null can never be true. So in a binary world where a result is true or not true, null ends up as false.

Select * from MyTable Where a <> b;

What happens when a = 10 and b is null? Is the record selected (true) or not (false)?

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-12-2019, 07:12 AM   #19
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 932
Thanks: 10
Thanked 189 Times in 179 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: strDocName Error

Quote:
the result is treated as false when used in a where clause
Do you have an example of that; specifically how you would use Null in a Where clause? In my experience, if you put =Null in a query design you will get nothing because as we agree, null is not equal to anything. If you put Null in the query, Access will convert that to Is Null, which is a function. If you try to do this in vba, you'll get an error.

So how would you use null in a where clause as you seem to be saying?
Micron is offline   Reply With Quote
Old 10-12-2019, 07:18 AM   #20
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,566
Thanks: 50
Thanked 1,047 Times in 1,028 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: strDocName Error

Quote:
Originally Posted by Micron View Post
Do you have an example of that; specifically how you would use Null in a Where clause? In my experience, if you put =Null in a query design you will get nothing because as we agree, null is not equal to anything. If you put Null in the query, Access will convert that to Is Null, which is a function. If you try to do this in vba, you'll get an error.

So how would you use null in a where clause as you seem to be saying?
Hi. Pardon me for jumping in, but here's what I just tried...

Code:
SELECT * FROM TableName WHERE 1 = TempVars!Test
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-12-2019, 08:43 AM   #21
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 932
Thanks: 10
Thanked 189 Times in 179 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: strDocName Error

Quote:
Originally Posted by theDBguy View Post
Hi. Pardon me for jumping in, but here's what I just tried...

Code:
SELECT * FROM TableName WHERE 1 = TempVars!Test
A little supporting info would help. What is the value of TempVars!Test? Null, empty string or some value? What was the result of that statement test?
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 10-12-2019, 08:54 AM   #22
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,566
Thanks: 50
Thanked 1,047 Times in 1,028 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: strDocName Error

Quote:
Originally Posted by Micron View Post
A little supporting info would help. What is the value of TempVars!Test? Null, empty string or some value? What was the result of that statement test?
Sorry. I knew I should have known better than to interrupt a good discussion. I'll go ahead and let Pat explain her side further, but here's what I was getting from the thread. If you enter the following SQL, you get all the records back:
Code:
SELECT * FROM TableName WHERE 1=1
And if you enter the following SQL, you get none back.
Code:
SELECT * FROM TableName WHERE 1=Null
However, as you have pointed out, the above will internally get changed to this.
Code:
SELECT * FROM TableName WHERE 1 Is Null
So, from what Pat was saying, I offered the following SQL.
Code:
SELECT * FROM TableName WHERE 1=TempVars!AnyNonExistingTempVar
If you try the above, you don't need to declare any TempVars first nor do you need to initiate it to anything at all. A call to a non-existing or non-initiated TempVar will return a Null value. So, using the above SQL, I can't say it's getting changed to an Is Null condition but maybe an actual 1=Null one. In either case, the resulting query shows no records, which is equivalent to having a False Where Condition.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-12-2019, 09:14 AM   #23
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,350
Thanks: 112
Thanked 2,841 Times in 2,591 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: strDocName Error

I'm not sure what point you were making. Of course, anything like this gives the same result

Code:
SELECT * FROM TableName WHERE 1=0;
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 10-12-2019 at 09:27 AM.
isladogs is offline   Reply With Quote
Old 10-12-2019, 09:29 AM   #24
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,566
Thanks: 50
Thanked 1,047 Times in 1,028 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: strDocName Error

Quote:
Originally Posted by isladogs View Post
I'm not sure what point you were making. Of course, anything like this gives the same result

Code:
SELECT * FROM TableName WHERE 1=0;
Hi Colin. Was that directed at me? If so, I was just offering a response to Micron trying to support one of Pat's earlier point, which was 1=0 is the same (equivalent) to 1=Null when it's used in a WHERE clause. Again, I better let Pat explain herself further from now on. Cheers!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-12-2019, 10:21 AM   #25
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,350
Thanks: 112
Thanked 2,841 Times in 2,591 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: strDocName Error

I think there is a risk this is just turning into semantics and I'm guilty of that below as well.

Micron is of course absolutely correct that a null isn't equal to anything else, not even another null.
As far as any WHERE condition is concerned, the outcome is obviously either True or False. Including Null could perhaps best be described as Not True rather than False but the effect is the same as being False. However as DBG just stated Access will change =Null (meaningless) to Is Null (meaningful).

Has the above helped at all? I doubt it...
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-12-2019, 11:39 AM   #26
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 932
Thanks: 10
Thanked 189 Times in 179 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: strDocName Error

I think this might boil down to one's concept of what the Where clause is and what it does - at least it has become that IMHO. By definition, it specifies criteria that field values must meet for the records that contain the values to be included in the query results. If an expression evaluates to true or false and true or false is what the field contains, it ought to work if you pass the result as criteria regardless of whether or not it returns the expected records. If the expression evaluates to true or false and the field does not contain true or false values, I'd expect it to not return records and perhaps raise a data type mismatch error. If the expression returns Null and you pass =Null to the criteria clause, you will raise the error I already mentioned. If you pass 'Null' (without the quotes) Access query def will convert it to Is Null as I said. I don't know if it will do that in the case of vba generated sql but I'm not inclined to test it as it would just be a factoid about something that we should never be dealing with anyway. By that I mean if one has an inherent understanding of the basics of Null in terms of sql, you ought to be doing everything to prevent it or convert it unless you are specifically looking for records where the field has no values and does not hold an empty string.

So I do agree with Isladogs re semantics, assuming we agree on the basics about Null.
Micron is offline   Reply With Quote
Old 10-12-2019, 10:02 PM   #27
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,133
Thanks: 15
Thanked 1,570 Times in 1,492 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: strDocName Error

Let me try again.

Select * from yourtable where somefield = null ---- will NEVER return any rows regardless of how many instances of somefield are actually null.

Select * from yourtable where somefield Is Null ---- WILL return any rows where somefield is null

The point I was trying to make was apparently too subtle.

Select * from yourtable where somefield = someotherfield ---- will not return any rows when either or both of the two fields are null. Logically we might think that when BOTH are null the result of the condition should be true but the database engine and VBA don't see things that way.

I think everyone is clear that null is not equal to something else and although the result of that test is technically null rather than false, we can agree that both null and false fall into the "not true" category. BUT when the test is a negative one, it gets more confusing so somefield <> someotherfield will NOT result in true even though the two fields are not equal when one of the fields is null. To the human mind 1 is not equal to null so to our pea brains, the test should result in true but because of the way nulls are treated, the result is null which falls into the "not true" category. THIS is what I was trying to explain.

I'm not sure how we drifted into this but it is a really important concept so since we're here we may as well beat it to death.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
theDBguy (10-13-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question strSQL = Run Time Error 3075 Syntax Error Missing Operator in qry Expr maxsun08 General 4 07-27-2012 06:39 AM
Error "Run-time error '1004': General ODBC Error D4WNO General 1 07-19-2012 07:42 AM
Error 3734 (share mode error) appearing in error guy- Modules & VBA 2 09-28-2011 02:02 AM
A question about the error message "Run-time error '3061' Too few parameters, Expecte dariyoosh Modules & VBA 7 03-12-2010 06:26 AM
Run time error 3075 - syntax error (missing operator) in query expression puskardas Modules & VBA 8 06-30-2008 07:35 PM




All times are GMT -8. The time now is 10:56 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World