SQL syntax for Null?

[deko]

Is there a difference?

A) WHERE (IsNull(qry100. Cat_ID)=True))

--or--

B) WHERE qry100.Cat_ID Is Null

I'm thinking A is better because it is less ambiguous (?)

appreciate your thoughts on this... thx

[Allen Browne]

Re: SQL syntax for Null?

(B) should be more efficient.

The expression:
WHERE qry100.Cat_ID Is Null
is evaluated by JET, the query engine in Access, so it can take advantage of
any index on the field.

IsNull() if a VBA function. That means JET has to pass the expression to VBA
on each row and then compare the return value to True.

Avoiding VBA function where they are not needed also makes it easier to
upsize your app if you ever need to do that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <dje422@hotmail .com> wrote in message
news:JMkkb.2091 $eP3.1305@newss vr27.news.prodi gy.com...[color=blue]
> Is there a difference?
>
> A) WHERE (IsNull(qry100. Cat_ID)=True))
>
> --or--
>
> B) WHERE qry100.Cat_ID Is Null
>
> I'm thinking A is better because it is less ambiguous (?)
>
> appreciate your thoughts on this... thx[/color]

[Steve Jorgensen]

Re: SQL syntax for Null?

On Sun, 19 Oct 2003 02:39:12 GMT, "Allen Browne"
<allenbrowne@Se eSig.invalid> wrote:
[color=blue]
>(B) should be more efficient.
>
>The expression:
> WHERE qry100.Cat_ID Is Null
>is evaluated by JET, the query engine in Access, so it can take advantage of
>any index on the field.
>
>IsNull() if a VBA function. That means JET has to pass the expression to VBA
>on each row and then compare the return value to True.
>
>Avoiding VBA function where they are not needed also makes it easier to
>upsize your app if you ever need to do that.[/color]

Also, it is not necessary to compare the result of a function that returns
a boolean with True or False since it is already a boolean True or False.
Furthermore, in the cases where you -do- want to compare a value with True
or False (such as with a form control where not doing so is known to cause
problems), you should always compare with False, never with True. This is
because, technically, any non-zero value is True in VB or Jet SQL, but a
False is always zero. So, always say =False or <>False, never =True.

[deko]

Re: SQL syntax for Null?

great tip! thanks!

just curious... do I have to re-write all my queries if I upsize to MSDE?

I have a lot of queries that use data that comes form user input (combo box,
or whatever) on a form. For example:

SELECT SUM(Amount) AS TypeTotal, TxType_ID
FROM tblTxJournal
WHERE (Year([TxDate])=Forms!frmMain !frm3.Form!cbx3 Year) And
([TxType_ID]=Forms!frmMain! frm3.Form!cbx3T xType)
GROUP BY TxType_ID;

I'll be ready to go to MSDE shortly (mainly to accommodate more users), but
it will be a long time before this needs to go on SQL server...

"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
news:8kv3pvgq6s 1g09mc1vsfkklbk hjp1rgrc4@4ax.c om...[color=blue]
> On Sun, 19 Oct 2003 02:39:12 GMT, "Allen Browne"
> <allenbrowne@Se eSig.invalid> wrote:
>[color=green]
> >(B) should be more efficient.
> >
> >The expression:
> > WHERE qry100.Cat_ID Is Null
> >is evaluated by JET, the query engine in Access, so it can take advantage[/color][/color]
of[color=blue][color=green]
> >any index on the field.
> >
> >IsNull() if a VBA function. That means JET has to pass the expression to[/color][/color]
VBA[color=blue][color=green]
> >on each row and then compare the return value to True.
> >
> >Avoiding VBA function where they are not needed also makes it easier to
> >upsize your app if you ever need to do that.[/color]
>
> Also, it is not necessary to compare the result of a function that returns
> a boolean with True or False since it is already a boolean True or False.
> Furthermore, in the cases where you -do- want to compare a value with True
> or False (such as with a form control where not doing so is known to cause
> problems), you should always compare with False, never with True. This is
> because, technically, any non-zero value is True in VB or Jet SQL, but a
> False is always zero. So, always say =False or <>False, never =True.[/color]

[TC]

Re: SQL syntax for Null?


"Allen Browne" <allenbrowne@Se eSig.invalid> wrote in message
news:kPmkb.1567 95$bo1.17100@ne ws-server.bigpond. net.au...[color=blue]
> (B) should be more efficient.
>
> The expression:
> WHERE qry100.Cat_ID Is Null
> is evaluated by JET, the query engine in Access, so it can take advantage[/color]
of[color=blue]
> any index on the field.[/color]

Not if nulls ain't indexed! I suspect (but am not certain) that this would
always do a full table scan.

TC

[Peter Doering]

Re: SQL syntax for Null?

> great tip! thanks![color=blue]
>
> just curious... do I have to re-write all my queries if I upsize to MSDE?[/color]

You've just proven Steve's example:

Access MSDE/SQL Server
True : -1 1
False: 0 0

If you link the tables through ODBC you shouldn't have compatibility
problems, but performance is likely to be worse. You will need to optimise
the code, e.g. pass-through queries, late binding.
[color=blue]
> I'll be ready to go to MSDE shortly (mainly to accommodate more users),[/color]

MSDE is the restricted edition of SQL Server and the restrictions are:
- Max. 5 concurrent users.
- Max. 2 GB DB size.

I've seen Access-DB's performing well with more than 5 concurrent users.

If the number of users is the reason for your migration you should re-think
the target platform. As mentioned, MSDE==SQL Server.

Peter

--
No mails please.

[Allen Browne]

Re: SQL syntax for Null?

TC, open a table in design view.
Open the Indexes dialog (View menu).

One of the properties in the lower pane of the dialog is, "Ignore Nulls".
Provided that setting is No, Access will be able to match the Nulls without
a table scan.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TC" <a@b.c.d> wrote in message news:1066550022 .148889@teuthos ...[color=blue]
>
> "Allen Browne" <allenbrowne@Se eSig.invalid> wrote in message
> news:kPmkb.1567 95$bo1.17100@ne ws-server.bigpond. net.au...[color=green]
> > (B) should be more efficient.
> >
> > The expression:
> > WHERE qry100.Cat_ID Is Null
> > is evaluated by JET, the query engine in Access, so it can take[/color][/color]
advantage[color=blue]
> of[color=green]
> > any index on the field.[/color]
>
> Not if nulls ain't indexed! I suspect (but am not certain) that this would
> always do a full table scan.[/color]

[Lyle Fairfield]

Re: SQL syntax for Null?

Peter Doering <nospam@doering .org> wrote in news:bmtj4i$qpp u8$1@ID-
204768.news.uni-berlin.de:
[color=blue]
> You've just proven Steve's example:
>
> Access MSDE/SQL Server
> True : -1 1
> False: 0 0[/color]


From the help file (XP):

******** start quote ********
Boolean variables are stored as 16-bit (2-byte) numbers, but they can only
be True or False. Boolean variables display as either True or False (when
Print is used) or #TRUE# or #FALSE# (when Write # is used). Use the
keywords True and False to assign one of the two states to Boolean
variables.
When other numeric types are converted to Boolean values, 0 becomes False
and all other values become True. When Boolean values are converted to
other data types, False becomes 0 and True becomes -1.
******** end quote ********

It seems, a Boolean True has all (16) bits up, while a Boolean False has
all bits down. So, in my opinion, it’s a bit of a stretch to equate -1 and
True. In arithmetic, VBA sees a two byte variable and when trying to do
something with it, seems to assume it’s a two byte number, that is, an
integer.

If we start from the assumption that False is Zero then Not False or True
ups all the down bits. That is <00000000 00000000> becomes <11111111
11111111>. And what is this as an integer? -1, Right? This seems to me to
more an incidental outcome of how numbers are stored.

In SQL and JET, I believe, (but I’m not sure), Booleans refer to specific
bits of a byte; thus 8 Booleans can be stored in one byte. Of course, if
tri-state is allowed, these seem to use a second byte to keep track of
that. Since the management of which bit is which Boolean is internal, I
suspect it would be unwise to mess with these bits directly.

And, of course, Access itself can use these three technologies; I think it
seldom or never deals with Booleans itself.

If anyone wants to mess with VBA Booleans directly I offer this bit of code
as a beginning from which you might want to begin an exploration which
could waste a perfectly good Sunday.

Option Base 0
Option Explicit

Private Declare Sub CopyMemory _
Lib "kernel32" Alias "RtlMoveMem ory" _
(Destination As Any, Source As Any, ByVal Length As Long)

Sub temp()
Dim a() As Byte
Dim b As Boolean
Dim i As Integer
Dim l As Long
Dim m As Long
Dim s As String
Dim z As Long

b = True
m = LenB(b)
l = m - 1

ReDim a(l)

CopyMemory a(0), b, m
For z = 0 To l
s = s & CStr(a(z))
If z <> 1 Then s = s & "."
Next z

MsgBox s

CopyMemory i, b, m

MsgBox i
End Sub

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Peter Doering]

Re: SQL syntax for Null?

Lyle,
[color=blue][color=green]
>> You've just proven Steve's example:
>>
>> Access MSDE/SQL Server
>> True : -1 1
>> False: 0 0[/color]
>
> From the help file (XP):
> ....[/color]

I'm not planning to waste my perfect Sunday ;-). What I'm referring to is
following statement from A97 help (search for "True <keyword>"):

<quote>
The True keyword has a value equal to -1.
</quote>

Peter

--
No mails please.

[deko]

Re: SQL syntax for Null?

I checked my tables according to the procedure you described below. I
discovered that (in most all of my tables) some fields apparently had
duplicate indexes. For example:

Index Name -- FieldName
TxType_ID -- TxType_ID
PrimaryKey -- TxType_ID

Can I delete the TxType_ID index?
Is it okay to rename these indexes?


"Allen Browne" <allenbrowne@Se eSig.invalid> wrote in message
news:JEskb.1572 01$bo1.83594@ne ws-server.bigpond. net.au...[color=blue]
> TC, open a table in design view.
> Open the Indexes dialog (View menu).
>
> One of the properties in the lower pane of the dialog is, "Ignore Nulls".
> Provided that setting is No, Access will be able to match the Nulls[/color]
without[color=blue]
> a table scan.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "TC" <a@b.c.d> wrote in message news:1066550022 .148889@teuthos ...[color=green]
> >
> > "Allen Browne" <allenbrowne@Se eSig.invalid> wrote in message
> > news:kPmkb.1567 95$bo1.17100@ne ws-server.bigpond. net.au...[color=darkred]
> > > (B) should be more efficient.
> > >
> > > The expression:
> > > WHERE qry100.Cat_ID Is Null
> > > is evaluated by JET, the query engine in Access, so it can take[/color][/color]
> advantage[color=green]
> > of[color=darkred]
> > > any index on the field.[/color]
> >
> > Not if nulls ain't indexed! I suspect (but am not certain) that this[/color][/color]
would[color=blue][color=green]
> > always do a full table scan.[/color]
>
>[/color]

[Tom van Stiphout]

Re: SQL syntax for Null?

On Mon, 20 Oct 2003 00:11:32 GMT, "deko" <dje422@hotmail .com> wrote:

Yes.
And you may also want to clear out Tools / Options / Tables /
AutoIndex.

-Tom.
[color=blue]
>I checked my tables according to the procedure you described below. I
>discovered that (in most all of my tables) some fields apparently had
>duplicate indexes. For example:
>
>Index Name -- FieldName
>TxType_ID -- TxType_ID
>PrimaryKey -- TxType_ID
>
>Can I delete the TxType_ID index?
>Is it okay to rename these indexes?
>[/color]

[Allen Browne]

Re: SQL syntax for Null?

Yes. DO delete the indexes where they are duplicated.

To prevent duplicate indexes in future, go to:
Tools | Options | Tables/Queries
and delete everything in the box:
AutoIndex on Import/Create

If you leave those entries there, Access automatically adds an index to any
field that has a name ending in ID, Num, Code etc. Since you also specified
TxType_ID as your primary key, you have a spurious index.

The same thing happens with foreign keys, though it is less obvious. If you
have a foreign key with a name ending in "ID", Access automatically indexes
it. Then if you create an index on the field, you have a duplicate. Then if
you create a relationship with referential integrity (RI) between this table
and the primary one, Access creates *another* hidden index to maintain the
RI, so you now have the field triple-indexed!

In general, don't let Access auto-index, and don't create indexes on your
foreign keys: use a relationship with RI instead.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <dje422@hotmail .com> wrote in message
news:UKFkb.88$f B6.48@newssvr14 .news.prodigy.c om...[color=blue]
> I checked my tables according to the procedure you described below. I
> discovered that (in most all of my tables) some fields apparently had
> duplicate indexes. For example:
>
> Index Name -- FieldName
> TxType_ID -- TxType_ID
> PrimaryKey -- TxType_ID
>
> Can I delete the TxType_ID index?
> Is it okay to rename these indexes?
>
>
> "Allen Browne" <allenbrowne@Se eSig.invalid> wrote in message
> news:JEskb.1572 01$bo1.83594@ne ws-server.bigpond. net.au...[color=green]
> > TC, open a table in design view.
> > Open the Indexes dialog (View menu).
> >
> > One of the properties in the lower pane of the dialog is, "Ignore[/color][/color]
Nulls".[color=blue][color=green]
> > Provided that setting is No, Access will be able to match the Nulls[/color]
> without[color=green]
> > a table scan.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "TC" <a@b.c.d> wrote in message news:1066550022 .148889@teuthos ...[color=darkred]
> > >
> > > "Allen Browne" <allenbrowne@Se eSig.invalid> wrote in message
> > > news:kPmkb.1567 95$bo1.17100@ne ws-server.bigpond. net.au...
> > > > (B) should be more efficient.
> > > >
> > > > The expression:
> > > > WHERE qry100.Cat_ID Is Null
> > > > is evaluated by JET, the query engine in Access, so it can take[/color]
> > advantage[color=darkred]
> > > of
> > > > any index on the field.
> > >
> > > Not if nulls ain't indexed! I suspect (but am not certain) that this[/color][/color]
> would[color=green][color=darkred]
> > > always do a full table scan.[/color][/color][/color]

[deko]

Re: SQL syntax for Null?

super. thanks for the tip.

"Allen Browne" <allenbrowne@Se eSig.invalid> wrote in message
news:YbGkb.1579 52$bo1.113900@n ews-server.bigpond. net.au...[color=blue]
> Yes. DO delete the indexes where they are duplicated.
>
> To prevent duplicate indexes in future, go to:
> Tools | Options | Tables/Queries
> and delete everything in the box:
> AutoIndex on Import/Create
>
> If you leave those entries there, Access automatically adds an index to[/color]
any[color=blue]
> field that has a name ending in ID, Num, Code etc. Since you also[/color]
specified[color=blue]
> TxType_ID as your primary key, you have a spurious index.
>
> The same thing happens with foreign keys, though it is less obvious. If[/color]
you[color=blue]
> have a foreign key with a name ending in "ID", Access automatically[/color]
indexes[color=blue]
> it. Then if you create an index on the field, you have a duplicate. Then[/color]
if[color=blue]
> you create a relationship with referential integrity (RI) between this[/color]
table[color=blue]
> and the primary one, Access creates *another* hidden index to maintain the
> RI, so you now have the field triple-indexed!
>
> In general, don't let Access auto-index, and don't create indexes on your
> foreign keys: use a relationship with RI instead.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "deko" <dje422@hotmail .com> wrote in message
> news:UKFkb.88$f B6.48@newssvr14 .news.prodigy.c om...[color=green]
> > I checked my tables according to the procedure you described below. I
> > discovered that (in most all of my tables) some fields apparently had
> > duplicate indexes. For example:
> >
> > Index Name -- FieldName
> > TxType_ID -- TxType_ID
> > PrimaryKey -- TxType_ID
> >
> > Can I delete the TxType_ID index?
> > Is it okay to rename these indexes?
> >
> >
> > "Allen Browne" <allenbrowne@Se eSig.invalid> wrote in message
> > news:JEskb.1572 01$bo1.83594@ne ws-server.bigpond. net.au...[color=darkred]
> > > TC, open a table in design view.
> > > Open the Indexes dialog (View menu).
> > >
> > > One of the properties in the lower pane of the dialog is, "Ignore[/color][/color]
> Nulls".[color=green][color=darkred]
> > > Provided that setting is No, Access will be able to match the Nulls[/color]
> > without[color=darkred]
> > > a table scan.
> > >
> > > --
> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > > Tips for Access users - http://allenbrowne.com/tips.html
> > > Reply to group, rather than allenbrowne at mvps dot org.
> > >
> > > "TC" <a@b.c.d> wrote in message news:1066550022 .148889@teuthos ...
> > > >
> > > > "Allen Browne" <allenbrowne@Se eSig.invalid> wrote in message
> > > > news:kPmkb.1567 95$bo1.17100@ne ws-server.bigpond. net.au...
> > > > > (B) should be more efficient.
> > > > >
> > > > > The expression:
> > > > > WHERE qry100.Cat_ID Is Null
> > > > > is evaluated by JET, the query engine in Access, so it can take
> > > advantage
> > > > of
> > > > > any index on the field.
> > > >
> > > > Not if nulls ain't indexed! I suspect (but am not certain) that this[/color]
> > would[color=darkred]
> > > > always do a full table scan.[/color][/color]
>
>[/color]

[TC]

Re: SQL syntax for Null?


"Lyle Fairfield" <MissingAddress @Invalid.Com> wrote in message
news:Xns94194BD D37E0FFFDBA@130 .133.1.4...

(snip)
[color=blue]
> In ... JET, I believe, (but I'm not sure), Booleans refer to specific
> bits of a byte; thus 8 Booleans can be stored in one byte. Of course, if
> tri-state is allowed, these seem to use a second byte to keep track of
> that.[/color]

Jet allocates a null/notnull bit for each field - seperate from the bits
that store the actual value.

But for booleans, they made the (ahem) brain-dead decision to use the
null/notnull bit to store the actual boolean value!

So in Jet, boolean fields can never be Null :-(

TC

[TC]

Re: SQL syntax for Null?

Ok, forgot that!

TC


"Allen Browne" <allenbrowne@Se eSig.invalid> wrote in message
news:JEskb.1572 01$bo1.83594@ne ws-server.bigpond. net.au...[color=blue]
> TC, open a table in design view.
> Open the Indexes dialog (View menu).
>
> One of the properties in the lower pane of the dialog is, "Ignore Nulls".
> Provided that setting is No, Access will be able to match the Nulls[/color]
without[color=blue]
> a table scan.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "TC" <a@b.c.d> wrote in message news:1066550022 .148889@teuthos ...[color=green]
> >
> > "Allen Browne" <allenbrowne@Se eSig.invalid> wrote in message
> > news:kPmkb.1567 95$bo1.17100@ne ws-server.bigpond. net.au...[color=darkred]
> > > (B) should be more efficient.
> > >
> > > The expression:
> > > WHERE qry100.Cat_ID Is Null
> > > is evaluated by JET, the query engine in Access, so it can take[/color][/color]
> advantage[color=green]
> > of[color=darkred]
> > > any index on the field.[/color]
> >
> > Not if nulls ain't indexed! I suspect (but am not certain) that this[/color][/color]
would[color=blue][color=green]
> > always do a full table scan.[/color]
>
>[/color]