strDocName Error

[Deleted member 147267]

Hi. Pardon me for jumping in, but here's what I just tried...

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?

 

[theDBguy]

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:

SELECT * FROM TableName WHERE 1=1

And if you enter the following SQL, you get none back.

SELECT * FROM TableName WHERE 1=Null

However, as you have pointed out, the above will internally get changed to this.

SELECT * FROM TableName WHERE 1 Is Null

So, from what Pat was saying, I offered the following SQL.

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.

 

[isladogs]

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

SELECT * FROM TableName WHERE 1=0;

 

[theDBguy]

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

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!

 

[isladogs]

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...

 

[Deleted member 147267]

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.

 

[Pat Hartman]

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.