Re: converting NULL values
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
Re: converting NULL values
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")