[AccessD] Empty Combo Box not returning NULL

[Heenan, Lambert]

It's all very strange, but at the end of the day I would just test for  and empty string every time with 

if comboBox & "" = "" then

Lambert  


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson
Sent: Friday, September 15, 2017 6: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
https://urldefense.proofpoint.com/v2/url?u=http-3A__databaseadvisors.com_mailman_listinfo_accessd&d=DgIFAw&c=xCWjLLB1opnQ8cQ1kJMK6Z0FoTSuoAz5oSor6M3mWoM&r=3YBmj7Sj6Qj9KCAjmw0aH_Ojqk70ji_Giv5KILyYF6DlttubYyldIWPj2xFc_ojY&m=iMlrUNmRm1QtKrJOB-EU6GUgKxDVzTGGq50zWKTfu4s&s=4naBc0_DT5d1_qyZq980b4XTNywJ4y4j0emnjz5tWkw&e=
Website: https://urldefense.proofpoint.com/v2/url?u=http-3A__www.databaseadvisors.com&d=DgIFAw&c=xCWjLLB1opnQ8cQ1kJMK6Z0FoTSuoAz5oSor6M3mWoM&r=3YBmj7Sj6Qj9KCAjmw0aH_Ojqk70ji_Giv5KILyYF6DlttubYyldIWPj2xFc_ojY&m=iMlrUNmRm1QtKrJOB-EU6GUgKxDVzTGGq50zWKTfu4s&s=Psq4EMaHHwrlslw_RHw3HgtPLnPeYon46dOxnsR5PI8&e= 



--
AccessD mailing list
AccessD at databaseadvisors.com
https://urldefense.proofpoint.com/v2/url?u=http-3A__databaseadvisors.com_mailman_listinfo_accessd&d=DgIFAw&c=xCWjLLB1opnQ8cQ1kJMK6Z0FoTSuoAz5oSor6M3mWoM&r=3YBmj7Sj6Qj9KCAjmw0aH_Ojqk70ji_Giv5KILyYF6DlttubYyldIWPj2xFc_ojY&m=iMlrUNmRm1QtKrJOB-EU6GUgKxDVzTGGq50zWKTfu4s&s=4naBc0_DT5d1_qyZq980b4XTNywJ4y4j0emnjz5tWkw&e=
Website: https://urldefense.proofpoint.com/v2/url?u=http-3A__www.databaseadvisors.com&d=DgIFAw&c=xCWjLLB1opnQ8cQ1kJMK6Z0FoTSuoAz5oSor6M3mWoM&r=3YBmj7Sj6Qj9KCAjmw0aH_Ojqk70ji_Giv5KILyYF6DlttubYyldIWPj2xFc_ojY&m=iMlrUNmRm1QtKrJOB-EU6GUgKxDVzTGGq50zWKTfu4s&s=Psq4EMaHHwrlslw_RHw3HgtPLnPeYon46dOxnsR5PI8&e=