[AccessD] Empty Combo Box not returning NULL

David Emerson newsgrps at dalyn.co.nz
Sat Sep 16 15:37:19 CDT 2017 

Hi Anders, It is the same copy of the database that is being used on all computers.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Anders Ebro (TheSmileyCoder)
Sent: Sunday, 17 September 2017 3:12 a.m.
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Empty Combo Box not returning NULL

What is the format of the combo to?

Sent from my mobile device, and thus kept brief.

On 15 Sep 2017, at 22.49, David Emerson <newsgrps at dalyn.co.nz> wrote:

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



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

More information about the AccessD mailing list