Blank fields in database

[Sue]

Hi,

I have a blank field in the database for Address. How do
I refer to that? For ex:

Set hrRS = Server.CreateObject("ADODB.Recordset")

hrQuery ="SELECT DISTINCT
Division,Description,Code,Address FROM tblEV3Location
ORDER BY Division,Description;"

hrRS.Open hrQuery,adoCon,2,2

hrDiv = hrRS(0) + "-"+ hrRS(1)

if hrRS(3)="" Then
hrCode = hrRS(2)
else
hrCode = hrRS(2)+"-"+hrRS(3)
End If

This unfortunately doesnt work.

Thanks.

[Aaron [SQL Server MVP]]

What does "blank" mean? Is it an empty string, or is the value NULL? How
did it get in there that way?

And what on earth does "doesnt work" mean???

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"Sue" <Sue@hotmail.com> wrote in message
news:0c1001c47b03$ccc11220$a601280a@phx.gbl...

> Hi,
>
> I have a blank field in the database for Address. How do
> I refer to that? For ex:
>
> Set hrRS = Server.CreateObject("ADODB.Recordset")
>
> hrQuery ="SELECT DISTINCT
> Division,Description,Code,Address FROM tblEV3Location
> ORDER BY Division,Description;"
>
> hrRS.Open hrQuery,adoCon,2,2
>
> hrDiv = hrRS(0) + "-"+ hrRS(1)
>
> if hrRS(3)="" Then
> hrCode = hrRS(2)
> else
> hrCode = hrRS(2)+"-"+hrRS(3)
> End If
>
> This unfortunately doesnt work.
>
> Thanks.

[sue]

blank as in there is no data in the field address

doesnt work as in the drop-down is not displayed with any
value if the address is blank in the database otherwise
the drop-down have values.

>-----Original Message-----
>What does "blank" mean? Is it an empty string, or is

the value NULL? How

>did it get in there that way?
>
>And what on earth does "doesnt work" mean???
>
>--
>[url]http://www.aspfaq.com/[/url]
>(Reverse address to reply.)
>
>
>
>
>"Sue" <Sue@hotmail.com> wrote in message
>news:0c1001c47b03$ccc11220$a601280a@phx.gbl...

>> Hi,
>>
>> I have a blank field in the database for Address. How

do

>> I refer to that? For ex:
>>
>> Set hrRS = Server.CreateObject("ADODB.Recordset")
>>
>> hrQuery ="SELECT DISTINCT
>> Division,Description,Code,Address FROM tblEV3Location
>> ORDER BY Division,Description;"
>>
>> hrRS.Open hrQuery,adoCon,2,2
>>
>> hrDiv = hrRS(0) + "-"+ hrRS(1)
>>
>> if hrRS(3)="" Then
>> hrCode = hrRS(2)
>> else
>> hrCode = hrRS(2)+"-"+hrRS(3)
>> End If
>>
>> This unfortunately doesnt work.
>>
>> Thanks.

>
>
>.
>

[Aaron [SQL Server MVP]]

I guess the problem is that you aren't clear on the difference between empty
string and null. Until then, change

if hrRS(3)="" Then

to

if cstr(hrRS(3)) & "" = "" Then

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Pawel Janowski]

1)
hrQuery ="SELECT DISTINCT
isnull(Division,'') as Division ,isnull(Description,'') as ... etc FROM
tblEV3Location
ORDER BY Division,Description;"

2)
SET CONCAT_NULL_YIELDS_NULL { ON | OFF }

--- see BOL (bookz online) -> SET CONCAT_NULL_YIELDS_NULL

--
-------
Pawel Janowski
[url]www.sunrise-tm.com[/url]
[url]www.koluszki.net[/url]

[McKirahan]

"Sue" <Sue@hotmail.com> wrote in message
news:0c1001c47b03$ccc11220$a601280a@phx.gbl...

> Hi,
>
> I have a blank field in the database for Address. How do
> I refer to that? For ex:
>
> Set hrRS = Server.CreateObject("ADODB.Recordset")
>
> hrQuery ="SELECT DISTINCT
> Division,Description,Code,Address FROM tblEV3Location
> ORDER BY Division,Description;"
>
> hrRS.Open hrQuery,adoCon,2,2
>
> hrDiv = hrRS(0) + "-"+ hrRS(1)
>
> if hrRS(3)="" Then
> hrCode = hrRS(2)
> else
> hrCode = hrRS(2)+"-"+hrRS(3)
> End If
>
> This unfortunately doesnt work.
>
> Thanks.

Perhaps just enhancing the SQL statement with:

WHERE Address <> ''

[Aaron [SQL Server MVP]]

That won't leave out rows where address is NULL, or address is ' '. I'm
also not convinced that the OP didn't want the rows at all ...

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"McKirahan" <News@McKirahan.com> wrote in message
news:uXIQc.235664$JR4.13258@attbi_s54...

> "Sue" <Sue@hotmail.com> wrote in message
> news:0c1001c47b03$ccc11220$a601280a@phx.gbl...

> > Hi,
> >
> > I have a blank field in the database for Address. How do
> > I refer to that? For ex:
> >
> > Set hrRS = Server.CreateObject("ADODB.Recordset")
> >
> > hrQuery ="SELECT DISTINCT
> > Division,Description,Code,Address FROM tblEV3Location
> > ORDER BY Division,Description;"
> >
> > hrRS.Open hrQuery,adoCon,2,2
> >
> > hrDiv = hrRS(0) + "-"+ hrRS(1)
> >
> > if hrRS(3)="" Then
> > hrCode = hrRS(2)
> > else
> > hrCode = hrRS(2)+"-"+hrRS(3)
> > End If
> >
> > This unfortunately doesnt work.
> >
> > Thanks.

>
> Perhaps just enhancing the SQL statement with:
>
> WHERE Address <> ''
>
>

[Steven Scaife]

I think she is wanting to do concatenation, to me it reads:
If hrRs(3) = "" then
get the value from column 2
else
hrCode = column 2 and column 3
end if

So she just wants hrCode = hrRS(2) & "-" & hrRS(3)

She was using ampersand as the join operator

> if hrRS(3)="" Then
> hrCode = hrRS(2)
> else
> hrCode = hrRS(2)+"-"+hrRS(3)
> End If
>
> This unfortunately doesnt work.
>
> Thanks.