converting NULL values

I am writing a databse program using an Access datbase in VB.

I am tryin to add the contents of a recordset to a combo box, however some of the values are NULL and so VB returns and error.

Therefore i am trying to convert the NULL to a blank string "" or 0 using an SQL statement

Code:

---

db.OpenRecordset("SELECT a, IIf(IsNull(a), "", a) " & _
                                    "FROM table")

---

This brings up a syntax error.... can NE one shed any light on this...

Ta:)

instead of doing it in the SQL statement, do it when you fill the combo

VB Code:

while not rs.eof
   cbo.AddItem rs("field") & ""
   rs.movenext
wend

I have now fixed the sql statement but it still brings up an error when it iterates to a record with a blank field it brings and error
'Invalid use of NULL':eek:

again ;)

cbo.AddItem rs("field") & ""

i still get the 'invalid use of null' error!!

The sql to replace the null with "" or 0 wasnt working NE way as i tested it using a msgbox to display the field... it still displayed blank.

Is there any other way to overcome this problem??

The error ocurs when im moving through the recordset. WHen it reaches a record with a blank field, the error occurs.

:rolleyes:

Quote:

---

Originally posted by boyracer38
I am writing a databse program using an Access datbase in VB.

I am tryin to add the contents of a recordset to a combo box, however some of the values are NULL and so VB returns and error.

Therefore i am trying to convert the NULL to a blank string "" or 0 using an SQL statement

VB Code:

db.OpenRecordset("SELECT a, [i]IIf(IsNull(a), "", a)[/i] " & _
                                    "FROM table")

This brings up a syntax error.... can NE one shed any light on this...

Ta:)

---

If you are using the bit in italics its because of the function.
IIF evaluates both outcomes ALWAYS.

Now, from the looks you aren't following Peets advice, have a go with that. All it means is the sql statement becomes the following :
SELECT a FROM table
The as you loop through filling your combo, you replace the null with a "" as applicable.


Vince

Quote:

---

Originally posted by boyracer38
I am writing a databse program using an Access datbase in VB.

I am tryin to add the contents of a recordset to a combo box, however some of the values are NULL and so VB returns and error.

Therefore i am trying to convert the NULL to a blank string "" or 0 using an SQL statement

Code:

---

db.OpenRecordset("SELECT a, IIf(IsNull(a), "", a) " & _
                                    "FROM table")

---

This brings up a syntax error.... can NE one shed any light on this...

Ta:)

---

Like i see you are selecting the same column twice the first time it is a the second time you didn't gave an alias to the column. This could be the problem.
try to change it to.

VB Code:

db.OpenRecordset("SELECT IIf(IsNull(a), '', a) as a " & _
                                    "FROM table")

In the SQL statemnt i have:

Code:

---

Set rs = db.OpenRecordset("SELECT a, IIf(IsNull(a), "", a) " & _
                                    "FROM table")

---

If i ad 'as a' to IIF statement, an error occurs.
When filling the combo box i have:

Code:

---

rs.movefirst
While Not rs.EOF
    MsgBox ("a: " & rs.Fields("a"))
      cmb.AddItem rs.Fields("a") & ""
      rs.MoveNext
    Wend

---

The IIF stament is still not workin tho bcause i have replaced the "" with text and it dosent show in the message box.
The relevant items ARE added to the combo box, but when moving through the recordset (rs.movenext), when a null field is encountered, i get the 'invalid use of NULL'.

I have also tried it without the IIF staement, and just the combo box bit '& "" ' but that dont work either.
:(

Code:

---

SELECT  CASE WHEN  a is null  THEN  '' else a  END from table

---

Quote:

---

Originally posted by boyracer38
The IIF stament is still not workin tho bcause i have replaced the "" with text and it dosent show in the message box.
The relevant items ARE added to the combo box, but when moving through the recordset (rs.movenext), when a null field is encountered, i get the 'invalid use of NULL'.

I have also tried it without the IIF staement, and just the combo box bit '& "" ' but that dont work either.
:(

---

The iif statement is working try to fill your combo with
cmb.AddItem rs.Fields(1) & ""

Else if you add as a you have to remove the first a from the sql string

Quote:

---

Originally posted by swatty

The iif statement is working try to fill your combo with

cmb.AddItem rs.Fields(1) & ""

Else if you add as a you have to remove the first a from the sql string

---

Where does the (1) come from? is that the index of the field or something like that?

The 1 is the number of the field you retrieved starting at 0

So if you select a, b, c from d then you can access the fields without giving the name of the column (a,b,c)

here you could use rs.fields(0), rs.fields(1), rs.fields(2)

Cause you wouldn't give an alias to the second column you cannot access it through its name cause you doesn't have a name.
Therefore i placed the 1 instead cause you didn't read my post well.
if you set the sql like ive mentioned

VB Code:

db.OpenRecordset("SELECT IIf(IsNull(a), '', a) as a " & _
                                    "FROM table")

You could access the field like rs.fields("a")

Why not just eliminate the nulls from your recordset using a where condition in your SQL statement:

VB Code:

db.OpenRecordset("SELECT  a " & _
                              "FROM table " & _
                              "Where a Is Not Null")

If i eliminate the nulls, the data will be out of sync with the data in rest of the table..... wont it??

Sorry to change tack, but you could use the following statement in Access. May not work with a vb front end tho..

Select NZ([a],"Unknown") As ThisField From Tablename


As to the 'out of sync' .... you only have one field being returned and therefore no ids or anything to pull any other data. So, no, in that respect.


Vince

Of if you keep getting invalid use of null.. stop using null. Try switching tracks to if len(field)>0.

Ummm.....

"SELECT a, IIf(IsNull(a), "", a) " & _
"FROM table"

Doesn't anyone else see anything wrong with this statement?

If I am reading it correctly, that is going to collapse to:

SELECT a, IIf(IsNull(a), ", a) FROM table

Instead of:

SELECT a, IIf(IsNull(a), "", a) FROM table

Which is what I think the author intended.

To make the second occur, use:

"SELECT a, IIf(IsNull(a), """", a) " & _
"FROM table"