[AccessD] Empty Combo Box not returning NULL

Sun Sep 17 15:32:47 CDT 2017

Yes, but the format property of the combobox, what is that set to?

How are you clearing the comboboxes?

Is the combobox restricted to only allow values in list?

Best regards
Anders Ebro // TheSmileyCoder <http://www.thesmileycoder.com>
Access MVP 2014-2018
<http://mvp.microsoft.com/en-us/mvp/Anders%20Ebro-5000469>

On Sat, Sep 16, 2017 at 10:37 PM, David Emerson <newsgrps at dalyn.co.nz>
wrote:

> 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
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>