[AccessD] Empty Combo Box not returning NULL

[David Emerson]

Hi Lambert,

I use the values of many combo boxes to pass as parameters to filter
records.  If the combo boxes do not have a selection I need to pass 0.  The
database has over 250 forms most of which use this.  For changing to trap
for "" as well as Null, and testing the changes there would be about 40
hours work :(

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

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_mai
lman_listinfo_accessd&d=DgIFAw&c=xCWjLLB1opnQ8cQ1kJMK6Z0FoTSuoAz5oSor6M3mWoM
&r=3YBmj7Sj6Qj9KCAjmw0aH_Ojqk70ji_Giv5KILyYF6DlttubYyldIWPj2xFc_ojY&m=iMlrUN
mRm1QtKrJOB-EU6GUgKxDVzTGGq50zWKTfu4s&s=4naBc0_DT5d1_qyZq980b4XTNywJ4y4j0emn
jz5tWkw&e=
Website:
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.databaseadvisors.com
&d=DgIFAw&c=xCWjLLB1opnQ8cQ1kJMK6Z0FoTSuoAz5oSor6M3mWoM&r=3YBmj7Sj6Qj9KCAjmw
0aH_Ojqk70ji_Giv5KILyYF6DlttubYyldIWPj2xFc_ojY&m=iMlrUNmRm1QtKrJOB-EU6GUgKxD
VzTGGq50zWKTfu4s&s=Psq4EMaHHwrlslw_RHw3HgtPLnPeYon46dOxnsR5PI8&e= 



--
AccessD mailing list
AccessD at databaseadvisors.com
https://urldefense.proofpoint.com/v2/url?u=http-3A__databaseadvisors.com_mai
lman_listinfo_accessd&d=DgIFAw&c=xCWjLLB1opnQ8cQ1kJMK6Z0FoTSuoAz5oSor6M3mWoM
&r=3YBmj7Sj6Qj9KCAjmw0aH_Ojqk70ji_Giv5KILyYF6DlttubYyldIWPj2xFc_ojY&m=iMlrUN
mRm1QtKrJOB-EU6GUgKxDVzTGGq50zWKTfu4s&s=4naBc0_DT5d1_qyZq980b4XTNywJ4y4j0emn
jz5tWkw&e=
Website:
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.databaseadvisors.com
&d=DgIFAw&c=xCWjLLB1opnQ8cQ1kJMK6Z0FoTSuoAz5oSor6M3mWoM&r=3YBmj7Sj6Qj9KCAjmw
0aH_Ojqk70ji_Giv5KILyYF6DlttubYyldIWPj2xFc_ojY&m=iMlrUNmRm1QtKrJOB-EU6GUgKxD
VzTGGq50zWKTfu4s&s=Psq4EMaHHwrlslw_RHw3HgtPLnPeYon46dOxnsR5PI8&e= 


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