Recordset query returns 'null' values.

[batman321]

Hi there,

I'm having some problems with my SQL queries. When I query a row in my Access database, the values are 'null' value. Sometimes it returns the contents of the row field, other times 'null'. I don't want null values.

------------------------------------------------------------------------

partnercomparestring = "Select * From customers where customerid = " & Edit_Customers.Text24.Text & ""
rs3.Open partnercomparestring, adoConnection, adOpenForwardOnly, adLockOptimistic

If Edit_Customers.Text17.Text = rs3.Fields(15) Then 'partner title
partnerredflag = partnerredflag + 1
End If

'--------------------------------------------------------------------

In this case, rs3.fields(15) will return 'null' value. It should return "" if it has an empty value. My other queries run fine and I have tries adopting the same structure but to no avail.

Thanks in advance

Simon

[dee-u]

You could append a nullstring to it like

If Edit_Customers.Text17.Text = (rs3.Fields(15) & vbNullString) Then 'partner title

[Hack]

If you do SELECT * (which no one will ever be able to convince me is ever necessary) you will get all fields in the record. If some of them are NULL, that is what will be returned.

If you don't want NULL values, don't select the fields that have NULL values.

[mathy2007]

Hack is correct as * is the wild card for all queries where as if you want certain fileds enter it as "Select CustomerFName, CustomerLName, CustomerTeleNo" seperating them with a comma. Although VB returns empty strings as "", sql aint the same language and therefore return it as "null".

[dee-u]

Although VB returns empty strings as "", sql aint the same language and therefore return it as "null".

SQL returns null for null but returns an empty string if it is what is saved in the database.

[mathy2007]

what he didnt say!

returns null for null and null if in database.