If then with 3 options...

[anessa05]

Hello,

Newbie question...

I'm trying to control the display from a sql database to only show a comma between city and state when the city and state fields are not Null. I just cannot get the query right. Here's what I have...

<%
if isNull(rs("city")) = false then
Response.Write rs("city") & ", " & rs("state")
else
if isNull(rs("city")) & (rs("state")) = true then
display nothing
else
Response.Write rs("state")
End If
End If
%>

Any assistance would be greatly appreciated.

Thanks,
Anessa

[nikkiH]

Try this instead.


if rs.Fields("city") <> "" And rs.Fields("state") <> "" then
Response.Write rs.Fields("city") & ", " & rs.Fields("state")
else
Response.Write rs.Fields("city") & " " & rs.Fields("state")
End If

[Freon22]

You know that if city has something in it but state is null then your going to get city, blank

<%
if isNull(rs("city")) = false then
Response.Write rs("city") & ", " & rs("state")
elseif isNull(rs("city")) = true And isNull(rs("state")) = true then
Response.Write("Both values are empty")
else
Response.Write rs("state")
end If
%>

I think that if I wanted to print out both city,state if we have a city and state or just a city if all we have is a city and the same with a state. I would do somthing like this or I would setup a case select.

If IsNull(rs("city")) = False And IsNull(rs("state")) = False Then
Response.Write(rs("city") & ", " & rs("state"))
ElseIf IsNull(rs("city")) = False And IsNull(rs("state")) = True Then
Response.Write(rs("city"))
ElseIf IsNull(rs("city")) = True And IsNull(rs("state")) = False Then
Response.Write(rs("state"))
Else
Response.Write("Both values are empty")
End If

I hope this helps, and I am sure some of the other guys here have better ways of using this.

Sorry nikkiH I was typing while you were posting :)

[anessa05]

Thanks. I tried this one. One problem though.

If IsNull(rs("city")) = False And IsNull(rs("state")) = False Then
Response.Write(rs("city") & ", " & rs("state"))
ElseIf IsNull(rs("city")) = False And IsNull(rs("state")) = True Then
Response.Write(rs("city"))
ElseIf IsNull(rs("city")) = True And IsNull(rs("state")) = False Then
Response.Write(rs("state"))
Else
Response.Write("Both values are empty")
End If

This only works if the value of the fields in the db have <NULL> in them. If the city and state fields are blank, it doesn't work. How can I fix it?

[Freon22]

Well what you do is use nikkiH coded. Its amost the same as IsNull were with the IsNull = False you are say that its not empty. When you say <> "" you are say not equal to "". So anyway rewrite the code like this.

If rs.Fields("city") <> "" And rs.Fields("state") <> "" Then
Response.Write(rs("city") & ", " & rs("state"))
ElseIf rs.Fields("city") <> "" And rs.Fields("state") = "" Then
Response.Write(rs("city"))
ElseIf rs.Fields("city") = "" And rs.Fields("state") <> "" Then
Response.Write(rs("state"))
Else
Response.Write("Both values are empty")
End If

What you are saying here is if city and state is not equal <> to "" nothing then print city, state.
Else if city not equal to "" and state is equal to "" then print only city then the next one say if city equal "" and state not equal to "" then print only state. And the last is a default were if nothing else is called then else say print Both values are empty.