Need Help dealing with blank fields

  • Thread starter Thread starter JH
  • Start date Start date
J

JH

I have a form that I'm using to dynamically generate reports.

The form has several sets of listboxes that list all the properties for each
field... like any "wizard" tool you've ever used in Microsoft (see my amazing
graphic below)

Box1 Box2
|------------- | [ > ] |------------- |
| a | [>>] | c |
| b | [ < ] | |
|_________ | [<<] |__________|


Obviously, when you click the right arrow, one property from Box1 goes to
Box2, Left arrow means Box2 to Box1, double arrows mean "all" instead of
one... etc.

Box1 is being populated using the form load() event to query the main table
with a "SELECT DISTINCT propertyA FROM tblMain".

My problem is that out of the 4 sets of these listboxes on the form,
propertyA is allowed to have a blank property field (so in the pretty graphic
above, pretend "a" is a blank).

The problem is that I was able to move the blank to Box2, but if I try to
move it back to Box1... it inserts, but then won't delete itself from Box2.
And I am no longer able to get it to delete from Box1 again (after that first
move) - So, Box1 -> Box2 = OK. Box2 -> Box1 = BAD. Box1 -> Box2 (again) =
BAD.

However, the "move all" in either direction does get the "blank" to move,
and I used a "DELETE * FROM" command to clear the table being moved from, so
those two buttons work properly.

Now, normally, I would just shrug and move on... except that I use the items
in the boxes to generate the recordsource for the report. My code loops
through the Box1 and adds a " AND NOT Status = """ & Me.Box1.ItemDate(x) line
to the record source... (I have not written the loop for this set of boxes
yet, hopefully dealing with the blank won't be too difficult to work around).
I can't have my report both "show" and "not show" blanks. :-P


*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Here's the Code for my "Add1" button (the one that works correctly):

Private Sub cmdStatusAdd1_Click()

Dim strSQL1 As String
Dim strSQL2 As String


strSQL1 = "INSERT INTO tblStatus2 (Status) " _
& "VALUES (""" & Me.Box1.Value & """)"

If IsNull(Me.Box1.Value) Then
strSQL2 = "DELETE FROM tblStatus WHERE " _
& "Status IS NULL"
Else
strSQL2 = "DELETE FROM tblStatus WHERE " _
& "Status = """ & Me.Box1.Value & """"
End If

CurrentDb.Execute strSQL1
CurrentDb.Execute strSQL2

Me.Box2.RowSource = "SELECT DISTINCT Status FROM tblStatus2"
Me.Box2.Requery

Me.Box1.RowSource = "SELECT DISTINCT Status FROM tblStatus"
Me.Box1.Requery
End Sub

*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Here's my code for the Minus1 button (the one failing me):

Private Sub cmdStatusMinus1_Click()

Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "INSERT INTO tblStatus (Status) " _
& "VALUES (""" & Me.Box2.Value & """)"

If IsNull(Me.Box2.Value) Then
strSQL2 = "DELETE FROM tblStatus2 WHERE " _
& "Status IS NULL"
Else
strSQL2 = "DELETE FROM tblStatus2 WHERE " _
& "Status = """ & Me.Box2.Value & """"
End If

CurrentDb.Execute strSQL1
CurrentDb.Execute strSQL2

Me.Box2.RowSource = "SELECT DISTINCT Status FROM tblStatus2"
Me.Box2.Requery

Me.Box1.RowSource = "SELECT DISTINCT Status FROM tblStatus"
Me.Box1.Requery
End Sub





Any advice?


Thanks!
 
Does the column contain a blank (a space)?, an empty string or a NULL? All
are different.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


JH said:
I have a form that I'm using to dynamically generate reports.

The form has several sets of listboxes that list all the properties for each
field... like any "wizard" tool you've ever used in Microsoft (see my amazing
graphic below)

Box1 Box2
|------------- | [ > ] |------------- |
| a | [>>] | c |
| b | [ < ] | |
|_________ | [<<] |__________|


Obviously, when you click the right arrow, one property from Box1 goes to
Box2, Left arrow means Box2 to Box1, double arrows mean "all" instead of
one... etc.

Box1 is being populated using the form load() event to query the main table
with a "SELECT DISTINCT propertyA FROM tblMain".

My problem is that out of the 4 sets of these listboxes on the form,
propertyA is allowed to have a blank property field (so in the pretty graphic
above, pretend "a" is a blank).

The problem is that I was able to move the blank to Box2, but if I try to
move it back to Box1... it inserts, but then won't delete itself from Box2.
And I am no longer able to get it to delete from Box1 again (after that first
move) - So, Box1 -> Box2 = OK. Box2 -> Box1 = BAD. Box1 -> Box2 (again) =
BAD.

However, the "move all" in either direction does get the "blank" to move,
and I used a "DELETE * FROM" command to clear the table being moved from, so
those two buttons work properly.

Now, normally, I would just shrug and move on... except that I use the items
in the boxes to generate the recordsource for the report. My code loops
through the Box1 and adds a " AND NOT Status = """ & Me.Box1.ItemDate(x) line
to the record source... (I have not written the loop for this set of boxes
yet, hopefully dealing with the blank won't be too difficult to work around).
I can't have my report both "show" and "not show" blanks. :-P


*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Here's the Code for my "Add1" button (the one that works correctly):

Private Sub cmdStatusAdd1_Click()

Dim strSQL1 As String
Dim strSQL2 As String


strSQL1 = "INSERT INTO tblStatus2 (Status) " _
& "VALUES (""" & Me.Box1.Value & """)"

If IsNull(Me.Box1.Value) Then
strSQL2 = "DELETE FROM tblStatus WHERE " _
& "Status IS NULL"
Else
strSQL2 = "DELETE FROM tblStatus WHERE " _
& "Status = """ & Me.Box1.Value & """"
End If

CurrentDb.Execute strSQL1
CurrentDb.Execute strSQL2

Me.Box2.RowSource = "SELECT DISTINCT Status FROM tblStatus2"
Me.Box2.Requery

Me.Box1.RowSource = "SELECT DISTINCT Status FROM tblStatus"
Me.Box1.Requery
End Sub

*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Here's my code for the Minus1 button (the one failing me):

Private Sub cmdStatusMinus1_Click()

Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "INSERT INTO tblStatus (Status) " _
& "VALUES (""" & Me.Box2.Value & """)"

If IsNull(Me.Box2.Value) Then
strSQL2 = "DELETE FROM tblStatus2 WHERE " _
& "Status IS NULL"
Else
strSQL2 = "DELETE FROM tblStatus2 WHERE " _
& "Status = """ & Me.Box2.Value & """"
End If

CurrentDb.Execute strSQL1
CurrentDb.Execute strSQL2

Me.Box2.RowSource = "SELECT DISTINCT Status FROM tblStatus2"
Me.Box2.Requery

Me.Box1.RowSource = "SELECT DISTINCT Status FROM tblStatus"
Me.Box1.Requery
End Sub





Any advice?


Thanks!
Click to expand...
 
I know a "space" is different... but an empty string = NULL, or so I thought.


Either way, I believe it is NULL. If you look at the code I posted, the
first time you run the "Add1" it works correctly. In that code, I treat it
like a NULL.

If an empty string and NULL are different, could it be adding an empty
string to Box2 intead of a NULL?

But, even if it WAS adding to Box2 as a string, the "else" part would still
be catching it, would it not?

:-(

Dorian said:
Does the column contain a blank (a space)?, an empty string or a NULL? All
are different.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


JH said:
I have a form that I'm using to dynamically generate reports.

The form has several sets of listboxes that list all the properties for each
field... like any "wizard" tool you've ever used in Microsoft (see my amazing
graphic below)

Box1 Box2
|------------- | [ > ] |------------- |
| a | [>>] | c |
| b | [ < ] | |
|_________ | [<<] |__________|


Obviously, when you click the right arrow, one property from Box1 goes to
Box2, Left arrow means Box2 to Box1, double arrows mean "all" instead of
one... etc.

Box1 is being populated using the form load() event to query the main table
with a "SELECT DISTINCT propertyA FROM tblMain".

My problem is that out of the 4 sets of these listboxes on the form,
propertyA is allowed to have a blank property field (so in the pretty graphic
above, pretend "a" is a blank).

The problem is that I was able to move the blank to Box2, but if I try to
move it back to Box1... it inserts, but then won't delete itself from Box2.
And I am no longer able to get it to delete from Box1 again (after that first
move) - So, Box1 -> Box2 = OK. Box2 -> Box1 = BAD. Box1 -> Box2 (again) =
BAD.

However, the "move all" in either direction does get the "blank" to move,
and I used a "DELETE * FROM" command to clear the table being moved from, so
those two buttons work properly.

Now, normally, I would just shrug and move on... except that I use the items
in the boxes to generate the recordsource for the report. My code loops
through the Box1 and adds a " AND NOT Status = """ & Me.Box1.ItemDate(x) line
to the record source... (I have not written the loop for this set of boxes
yet, hopefully dealing with the blank won't be too difficult to work around).
I can't have my report both "show" and "not show" blanks. :-P


*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Here's the Code for my "Add1" button (the one that works correctly):

Private Sub cmdStatusAdd1_Click()

Dim strSQL1 As String
Dim strSQL2 As String


strSQL1 = "INSERT INTO tblStatus2 (Status) " _
& "VALUES (""" & Me.Box1.Value & """)"

If IsNull(Me.Box1.Value) Then
strSQL2 = "DELETE FROM tblStatus WHERE " _
& "Status IS NULL"
Else
strSQL2 = "DELETE FROM tblStatus WHERE " _
& "Status = """ & Me.Box1.Value & """"
End If

CurrentDb.Execute strSQL1
CurrentDb.Execute strSQL2

Me.Box2.RowSource = "SELECT DISTINCT Status FROM tblStatus2"
Me.Box2.Requery

Me.Box1.RowSource = "SELECT DISTINCT Status FROM tblStatus"
Me.Box1.Requery
End Sub

*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Here's my code for the Minus1 button (the one failing me):

Private Sub cmdStatusMinus1_Click()

Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "INSERT INTO tblStatus (Status) " _
& "VALUES (""" & Me.Box2.Value & """)"

If IsNull(Me.Box2.Value) Then
strSQL2 = "DELETE FROM tblStatus2 WHERE " _
& "Status IS NULL"
Else
strSQL2 = "DELETE FROM tblStatus2 WHERE " _
& "Status = """ & Me.Box2.Value & """"
End If

CurrentDb.Execute strSQL1
CurrentDb.Execute strSQL2

Me.Box2.RowSource = "SELECT DISTINCT Status FROM tblStatus2"
Me.Box2.Requery

Me.Box1.RowSource = "SELECT DISTINCT Status FROM tblStatus"
Me.Box1.Requery
End Sub





Any advice?


Thanks!
Click to expand...
Click to expand...
 
This is a great summary description of how to handle nulls in MS Access:
http://allenbrowne.com/casu-12.html

This is good too:
http://www.techonthenet.com/access/functions/advanced/isnull.php

NZ Function:
http://www.techonthenet.com/access/functions/advanced/nz.php
http://articles.techrepublic.com.com/5100-10878_11-6125114.html


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


JH said:
I know a "space" is different... but an empty string = NULL, or so I thought.


Either way, I believe it is NULL. If you look at the code I posted, the
first time you run the "Add1" it works correctly. In that code, I treat it
like a NULL.

If an empty string and NULL are different, could it be adding an empty
string to Box2 intead of a NULL?

But, even if it WAS adding to Box2 as a string, the "else" part would still
be catching it, would it not?

:-(

Dorian said:
Does the column contain a blank (a space)?, an empty string or a NULL? All
are different.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


JH said:
I have a form that I'm using to dynamically generate reports.

The form has several sets of listboxes that list all the properties for each
field... like any "wizard" tool you've ever used in Microsoft (see my amazing
graphic below)

Box1 Box2
|------------- | [ > ] |------------- |
| a | [>>] | c |
| b | [ < ] | |
|_________ | [<<] |__________|


Obviously, when you click the right arrow, one property from Box1 goes to
Box2, Left arrow means Box2 to Box1, double arrows mean "all" instead of
one... etc.

Box1 is being populated using the form load() event to query the main table
with a "SELECT DISTINCT propertyA FROM tblMain".

My problem is that out of the 4 sets of these listboxes on the form,
propertyA is allowed to have a blank property field (so in the pretty graphic
above, pretend "a" is a blank).

The problem is that I was able to move the blank to Box2, but if I try to
move it back to Box1... it inserts, but then won't delete itself from Box2.
And I am no longer able to get it to delete from Box1 again (after that first
move) - So, Box1 -> Box2 = OK. Box2 -> Box1 = BAD. Box1 -> Box2 (again) =
BAD.

However, the "move all" in either direction does get the "blank" to move,
and I used a "DELETE * FROM" command to clear the table being moved from, so
those two buttons work properly.

Now, normally, I would just shrug and move on... except that I use the items
in the boxes to generate the recordsource for the report. My code loops
through the Box1 and adds a " AND NOT Status = """ & Me.Box1.ItemDate(x) line
to the record source... (I have not written the loop for this set of boxes
yet, hopefully dealing with the blank won't be too difficult to work around).
I can't have my report both "show" and "not show" blanks. :-P


*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Here's the Code for my "Add1" button (the one that works correctly):

Private Sub cmdStatusAdd1_Click()

Dim strSQL1 As String
Dim strSQL2 As String


strSQL1 = "INSERT INTO tblStatus2 (Status) " _
& "VALUES (""" & Me.Box1.Value & """)"

If IsNull(Me.Box1.Value) Then
strSQL2 = "DELETE FROM tblStatus WHERE " _
& "Status IS NULL"
Else
strSQL2 = "DELETE FROM tblStatus WHERE " _
& "Status = """ & Me.Box1.Value & """"
End If

CurrentDb.Execute strSQL1
CurrentDb.Execute strSQL2

Me.Box2.RowSource = "SELECT DISTINCT Status FROM tblStatus2"
Me.Box2.Requery

Me.Box1.RowSource = "SELECT DISTINCT Status FROM tblStatus"
Me.Box1.Requery
End Sub

*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Here's my code for the Minus1 button (the one failing me):

Private Sub cmdStatusMinus1_Click()

Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "INSERT INTO tblStatus (Status) " _
& "VALUES (""" & Me.Box2.Value & """)"

If IsNull(Me.Box2.Value) Then
strSQL2 = "DELETE FROM tblStatus2 WHERE " _
& "Status IS NULL"
Else
strSQL2 = "DELETE FROM tblStatus2 WHERE " _
& "Status = """ & Me.Box2.Value & """"
End If

CurrentDb.Execute strSQL1
CurrentDb.Execute strSQL2

Me.Box2.RowSource = "SELECT DISTINCT Status FROM tblStatus2"
Me.Box2.Requery

Me.Box1.RowSource = "SELECT DISTINCT Status FROM tblStatus"
Me.Box1.Requery
End Sub





Any advice?


Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top