openarg problem when null

**Ken Snell** · Nov 13 '05, 08:42 AM

Re: openarg problem when null

Try this:

SELECT [Client Record].[Client RecordID], [Client Record].LastName,
[Client Record].FirstName, [Client Record].Company, [Client
Record].Address, [Client Record].City, [Client Record].[Zip Code],
[Client Record].State, [Client Record].Country, [Client
Record].WorkPhone, [Client Record].FaxNumber, [Client
Record].EmailAddress, [Client Record].Business, [Client Record].Origin,
[Client Record].[Date Of Entry], [Client Record].Interest, [Client
Record].Other, [Client Record].[2nd Interest], [Client Record].Notes
FROM [Client Record]
WHERE ((([Client Record].Origin)=Nz([Forms]![Client
Records].[OpenArgs],"")) OR [Forms]![Client
Records].[OpenArgs] Is Null);

--

Ken Snell
<MS ACCESS MVP>

<jannordgreen@g mail.com> wrote in message
news:1109352397 .944967.138640@ l41g2000cwc.goo glegroups.com.. .[color=blue]
> The form 'Client Records' has this record source:
>
> SELECT [Client Record].[Client RecordID], [Client Record].LastName,
> [Client Record].FirstName, [Client Record].Company, [Client
> Record].Address, [Client Record].City, [Client Record].[Zip Code],
> [Client Record].State, [Client Record].Country, [Client
> Record].WorkPhone, [Client Record].FaxNumber, [Client
> Record].EmailAddress, [Client Record].Business, [Client Record].Origin,
> [Client Record].[Date Of Entry], [Client Record].Interest, [Client
> Record].Other, [Client Record].[2nd Interest], [Client Record].Notes
> FROM [Client Record]
> WHERE ((([Client Record].Origin)=Nz([Forms]![Client
> Records].[OpenArgs],"")));
>
> When I call the form 'Client Records' from another form with
> DoCmd.OpenForm "Client Records", , , , , , "Miami 2004"
> I get the clients for Miami 2004 listedas I should.
>
> When I call it with
> DoCmd.OpenForm "Client Records"
> I get no records. I wanted to get all the records, but I don't.
>
> I use Winxp and Access 2003.
>
> Regards,
>
> Jan Nordgreen
>
> ---
>
> My sites:
>
> jan's diary (my diary) -
> http://simpler-solutions.net/pmachinefree/weblog.php
> think again! (seductive math problems for the modern mind) -
> http://simpler-solutions.net/pmachin...thinkagain.php
> mumnet (mathematical ulterior motives) -
> http://simpler-solutions.net/mumnet/index.htm
> sharealink (my favourite links) -
> http://simpler-solutions.net/shareal..._main.php?id=1
> my bloglines (favourite rss feeds):
> http://www.bloglines.com/public/damezumari
>
> my other sites: http://simpler-solutions.net/jansdiary/mysites.htm
>[/color]

**jannordgreen@gmail.com** · Nov 13 '05, 08:42 AM

Re: openarg problem when null

It worked!

Thanks for your help Ken!

I must admit I can't see why, though. The Nz() function should have
taken care of the is null possibility.

I got even this to work, not using nz():

SELECT [Client Record].[Client RecordID], [Client Record].LastName,
[Client Record].FirstName, [Client Record].Company, [Client
Record].Address, [Client Record].City, [Client Record].[Zip Code],
[Client Record].State, [Client Record].Country, [Client
Record].WorkPhone, [Client Record].FaxNumber, [Client
Record].EmailAddress, [Client Record].Business, [Client Record].Origin,
[Client Record].[Date Of Entry], [Client Record].Interest, [Client
Record].Other, [Client Record].[2nd Interest], [Client Record].Notes
FROM [Client Record]
WHERE ((([Client Record].Origin)=[Forms]![Client Records].[OpenArgs]))
OR ((([Forms]![Client Records].[OpenArgs]) Is Null));

Regards,

Jan Nordgreen

---

My sites:

jan's diary (my diary) -

http://simpler-solutions.net/pmachinefree/weblog.php

think again! (seductive math problems for the modern mind) -

http://simpler-solutions.net/pmachinefree/thinkagain/thinkagain.php

mumnet (mathematical ulterior motives) -

http://simpler-solutions.net/mumnet/index.htm

sharealink (my favourite links) -

http://simpler-solutions.net/sharealink/sl_main.php?id=1

my bloglines (favourite rss feeds):

404 - Page Not Found

http://www.bloglines.com/public/damezumari

my other sites: http://simpler-solutions.net/jansdiary/mysites.htm

**Ken Snell** · Nov 13 '05, 08:42 AM

Re: openarg problem when null

What the Nz function did was replace a Null value with an empty string. Then
your query was looking for records where Client Record.Origin was an empty
string, which is not the same thing as a Null.

--

Ken Snell
<MS ACCESS MVP>

<jannordgreen@g mail.com> wrote in message
news:1109358991 .696101.172770@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> It worked!
>
> Thanks for your help Ken!
>
> I must admit I can't see why, though. The Nz() function should have
> taken care of the is null possibility.
>
> I got even this to work, not using nz():
>
> SELECT [Client Record].[Client RecordID], [Client Record].LastName,
> [Client Record].FirstName, [Client Record].Company, [Client
> Record].Address, [Client Record].City, [Client Record].[Zip Code],
> [Client Record].State, [Client Record].Country, [Client
> Record].WorkPhone, [Client Record].FaxNumber, [Client
> Record].EmailAddress, [Client Record].Business, [Client Record].Origin,
> [Client Record].[Date Of Entry], [Client Record].Interest, [Client
> Record].Other, [Client Record].[2nd Interest], [Client Record].Notes
> FROM [Client Record]
> WHERE ((([Client Record].Origin)=[Forms]![Client Records].[OpenArgs]))
> OR ((([Forms]![Client Records].[OpenArgs]) Is Null));
>
> Regards,
>
> Jan Nordgreen
>
> ---
>
>
> My sites:
>
>
> jan's diary (my diary) -
> http://simpler-solutions.net/pmachinefree/weblog.php
> think again! (seductive math problems for the modern mind) -
> http://simpler-solutions.net/pmachin...thinkagain.php
> mumnet (mathematical ulterior motives) -
> http://simpler-solutions.net/mumnet/index.htm
> sharealink (my favourite links) -
> http://simpler-solutions.net/shareal..._main.php?id=1
> my bloglines (favourite rss feeds):
> http://www.bloglines.com/public/damezumari
>
>
> my other sites: http://simpler-solutions.net/jansdiary/mysites.htm
>[/color]

**Martha** · Nov 13 '05, 08:42 AM

Re: openarg problem when null

> I must admit I can't see why, though. The Nz() function should have[color=blue]
> taken care of the is null possibility.[/color]

Well, no. Here's a paraphrase of your WHERE clause:

WHERE Origin=Nz(OpenA rgs,"")

This condition will be true in the following two cases: (1) Origin
equals OpenArgs, and (2) Origin equals "" . Can you see why it wasn't
returning records?

Ken's suggestion rewrites this to

WHERE (Origin=Nz(Open Args,"")) OR (OpenArgs Is Null)

This condition will be true in the following three cases: (1) Origin
equals OpenArgs, (2) Origin equals "", and (3) OpenArgs is blank. Since
we don't really care if Origin is a zero-length string, we can remove
(2), as you did:

WHERE (Origin = OpenArgs) OR (OpenArgs Is Null)

Clear as mud?

--
HTH,
Martha Palotay
don't google to email

**jannordgreen@gmail.com** · Nov 13 '05, 08:43 AM

Re: openarg problem when null

Yes, clearer than mud. I understood. :)

Thanks!

openarg problem when null

openarg problem when null

Comment

Comment

Comment

Comment

Comment