[AccessD] Empty Combo Box not returning NULL

Fri Sep 15 15:49:27 CDT 2017

Hi Jim,

Here are the properties that I think are relevant:

SQL Server:
Collation: Latin1_General_CI_AS
Compatibility Level: SQL Server 2008 (100)
ANSI NULL Default: False
ANSI NULLS Enabled: False
ANSI Padding Enabled: False
ANSI Warnings Enabled: False
Concatenate Null Yields Null: False

There are identical on both servers.  Since the problem shows itself in the
database even when the combo box only has a Value List for the row source, I
am not sure that it is SQL Server setting.

MS Access:  The combo boxes work on these versions
Development: Microsoft Office Professional 2010 Version 14.0.7188.5002 (32
bit)
Client Current: Microsoft Office Professional Plus 2010 Version
14.0.7128.5000 (32 bit)

Client New Server: Microsoft Office Professional Plus 2010 Version
14.0.4760.1000 (32 bit)
Client Second Version: Microsoft Office Professional Plus 2013 Version
15.0.4420.1017 (32 bit)

The SQL Server Compatibility Syntax (ANSI 92) options are cleared in all
versions.

One Option would be to see if they can get their version of Access 2010 to a
later 2010 version but not sure if that will help.

Any other thoughts anyone?

Regards

David

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim
Dettman
Sent: Saturday, 16 September 2017 12:21 a.m.
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Empty Combo Box not returning NULL

David,

  In some way, shape, or form, I think you are tripping over ANSI settings
in SQL Server and possibly the ANSI 92 setting in Access.

  I don't clearly remember all the in's and out's with this, but
ANSI_PADDING and ANSI_NULLS both have an impact on what is returned in a
recordset.   ANSI_PADDING is even worse in that there are variations between
char and varchar fields and the way data is actually stored in a table.

 As for the ANSI 92 setting in Access, I have steered clear of it.   All I
remember is that when I first tried it, I had lots of problems and never
went back.

<< Another solution would be to check for both Null and Empty Strings when
using the combo box as a source for other code but again this is a large
task.>>

 I think as gustav said, this is your long term answer and it may not be as
bad as you think.  Find and Replace should help you clean it up quickly.   I
always do NZ(<reference>,"")="" as a check, so I've avoided the issue.

Short term, I would go back and look at the SQL Server settings and settings
for the database and make sure they match for anything old/new.  Also if
your using a DSN/ODBC, settings can be specified there as well  depending on
the driver.   

 That may still not give you a good result though.   Those settings can
impact how data is actually stored.   If I remember correctly, ANSI_PADDING
is for the life of a table once created, so potentially, you can have a mix
of things between tables even within the same server/database.

Jim.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
David Emerson
Sent: Friday, September 15, 2017 06:36 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Empty Combo Box not returning NULL

Hi All,

I have changed the subject to better reflect the problem.

Further testing has tracked down the anomaly.

All tests are done on a copy of the database that works on my development
machine.

On the client's instance of Access 2010:
- if I clear a combo box on an existing form and check for the value I get
an empty string (which is NOT the result expected for combo boxes).
- If I create a new combo box on an existing form, select an item in the
box, clear the box,  and check for the value I get an empty string.
- if I create a new form within the database with a combo box, select an
item in the box, clear the box, and check for the value of the combo box I
get Null (which is the result expected for combo boxes)
- if I copy the combo box from the old form to the new form within the
database, select an item in the box, clear the box, and check for the value
of the combo box I get Null (which is the result expected for combo boxes)

Therefore I conclude that there is something about the existing forms that
causes the empty combo boxes to return an Empty String instead of Null.

I have tried compacting and replacing, decompiling, importing the objects
into a new database but nothing has helped.

One solution would be to copy all the fields from the old forms to new one,
however this database has 250 forms and I don't relish the idea of spending
my weekend doing that exercise.

Another solution would be to check for both Null and Empty Strings when
using the combo box as a source for other code but again this is a large
task.

Does anyone have any other thoughts on how to solve this one?

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Gustav Brock
Sent: Tuesday, 12 September 2017 6:13 p.m.
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Recordset method Failed

Hi David

Have in mind please, that an unbound combobox always returns Null for no
selection or a string for any selection.
Thus, you should use:

    If Nz(Me!cboFilter, "") = "" Then MsgBox "Empty string"

or, as the default return value for Nz is an empty string, simply:

    If Nz(Me!cboFilter) = "" Then MsgBox "Empty string"

or, to make clear that you pull the value of the combobox:

    If Nz(Me!cboFilter.Value) = "" Then MsgBox "Empty string"

If you wish to use zero, it should either read:

    If Nz(Me!cboFilter, 0) = 0 Then MsgBox "Empty string"

or rather:

    If Nz(Me!cboFilter, "0") = "0" Then MsgBox "Empty string"

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD [mailto:accessd-bounces at databaseadvisors.com] På vegne af David
Emerson
Sendt: 12. september 2017 06:37
Til: 'Access Developers discussion and problem solving'
<accessd at databaseadvisors.com>
Emne: Re: [AccessD] Recordset method Failed

I have been doing more investigating and have come up with something
unusual.

The IT people have set up a separate box with SQL 2008R2 installed cleanly
and Access 2010 installed cleanly.  Some of the problems have gone away with
setting the record sets of forms and combo boxes.  I have found that I am
now getting an unusual thing happening when I use some of the combo boxes
for filters.

The combo boxes are basically 2 columns with the first column being an ID
and the second column being a text description.

When I select an item from the list the combo box has the value of the ID
for that item.

If I clear the combobox and use nz to get the value of the box (for example
nz(me!cboFilter,0)) I get 0 on my development machine but the new server
returns an empty string.  I know this because I had this on the After Update
event:

MsgBox "(" & Nz(Me!cboFilter, 0) & ")"
If Nz(Me!cboFilter, 0) = "" Then MsgBox "Empty string"

On My machine I get:
(0)

On the new server I get
()
Empty String

Why the different behaviour?  Is this a setting, or something with NZ I am
not aware of?

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com