Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 771
  • Last Modified:

If Null Statement on ADO field

This does not work...I need one string value if the DB field (SQL) is null, and seperate value to the string IF the field is not NUll.

Yes, the table is set to allow Nulls onthis column.

If IsNull(adoMRS.Fields("ClearName")) Then

strCD = "      "
strCN = "  "

strT1 = strCD & " " & strCN & " " & adoMRS.Fields("Code") & " " &adoMRS.Fields("Description")
ElseIf adoMRS.Fields("ClearName") <> "" Then
     
      strT1 = Format(adoMRS.Fields("ClearDate"), "mm/dd/yy") & " " & Left(adoMRS.Fields("ClearName"), 2) & " " & adoMRS.Fields("Code") & " " & adoMRS.Fields("Description")
End If  
0
lexo
Asked:
lexo
  • 10
  • 4
  • 4
  • +9
10 Solutions
 
cnfeiCommented:
how it doesn't work...what is the result? or what is the error message?
0
 
rajaloysiousCommented:
You can try out adoMRS.Fields("ClearName") = vbEmpty
OR
adoMRS.Fields("ClearName"))  = vbnull
OR
IsEmpty(adoMRS.Fields("ClearName")) )

also i guess that this could should work...
Good Luck....


0
 
lexoAuthor Commented:
"(10075) Null value can't be used here"
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
rajaloysiousCommented:
Get me the code snippet please if you dont mind .....
0
 
lexoAuthor Commented:
Tried em all, no luck...this should be a lot simpler than it's turning out to be. Here's ALL the code:

   Set adoMCN = New ADODB.Connection
   With adoMCN
      .ConnectionString = strCon
      .Open
   End With




   strProdID = GetCalcString(7073)  

strCond= "D"

   Set adoMRS = New ADODB.Recordset
   With adoMRS
      strSql = "Select * from UATracking_file  Where Loan_id= '" & strProdID & "' AND RemoveCond='" & strCond & "' Order by Code"
      .Open strSql, adoMCN, , , 1
   End With

   
   intReg = 8800
   
   While Not adoMRS.EOF


If IsEmpty(adoMRS.Fields("ClearName")) Then

strCD = "      "
strCN = "  "

strT1 = strCD & " " & strCN & " " & adoMRS.Fields("Code") & " " & adoMRS.Fields("Description")
ElseIf adoMRS.Fields("ClearName") <> "" Then

      strT1 = Format(adoMRS.Fields("ClearDate"), "mm/dd/yy") & " " & Left(adoMRS.Fields("ClearName"), 2) & " " & adoMRS.Fields("Code") & " " & adoMRS.Fields("Description")
End If  

      Do
         Select Case Len(strT1)
            Case Is <= 100
               strT2 = strT1
               strT1 = ""
            Case Is > 100
               strT2 = Left(strT1, 100)
               strT1 = Trim(Mid(strT1, 101))
         End Select
         intReg = intReg + 1
         SetRegString intReg, strT2

      Loop Until Len(strT1) = 0

      adoMRS.Movenext
   
 Wend  
   Set adoMRS = Nothing
   Set adoMCN = Nothing
0
 
lexoAuthor Commented:
Oh, and this question is posted inthe SQL section as well, I'll give both questions points if you can answer it...1000 points, I need this ASAP. Tired and need to go home.  

Also another more tip:  I'm using ghetto @$$ Sax Basic, so if I need to add a reference library to get functionality, I can do that.
0
 
StephenSimpsonxCommented:

Which line specifically is giving the error? Did you step though it?

is code or cleardate null?

 


If IsNull(adoMRS.Fields("ClearName")) Then

  strCD = "      "
  strCN = "  "
  strT1 = strCD & " " & strCN & " " & adoMRS.Fields("Code") & " " &adoMRS.Fields("Description")

else
   If adoMRS.Fields("ClearName") <> "" Then
     
       strT1 = Format(adoMRS.Fields("ClearDate"), "mm/dd/yy") & " " & Left(adoMRS.Fields("ClearName"), 2) & " " &      _
       adoMRS.Fields("Code") & " " & adoMRS.Fields("Description")
  endif
End If

0
 
ramesh12Commented:
Use Trim

ElseIf trim(adoMRS.Fields("ClearName")) <> "" Then
0
 
lexoAuthor Commented:
Same damn Error....this sucks.
0
 
cnfeiCommented:
add a function

Public Function checkNull(ByVal tmpString As Variant) As String
    If IsNull(tmpString) Then
        checkNull = ""
    Else
        checkNull = tmpString
    End If
End Function

and change the code to

If IsNull(adoMRS.Fields("ClearName")) Then

  strCD = "      "
  strCN = "  "
  strT1 = strCD & " " & strCN & " " & checknull(adoMRS.Fields("Code")) & " " & checknull(adoMRS.Fields("Description"))

elseIf adoMRS.Fields("ClearName") <> "" Then

      if not isdate(checknull(adoMRS.Fields("ClearDate"))) then
            msgbox "The value of Clear date is invalid"
      else

            strT1 = Format(adoMRS.Fields("ClearDate"), "mm/dd/yy") & " " & Left(adoMRS.Fields("ClearName"), 2)    & " " &      _
            checknull(adoMRS.Fields("Code")) & " " & checknull(adoMRS.Fields("Description"))
      end if
end if
0
 
rajaloysiousCommented:
hmmmmmmmmm,
if u want to solve it now by any means... see this ... i attack the problem from the other side
Instead of using Select * from table name

use this
const XXX_XXX_XXXX = "_____________________XX_" or 99999999 ( if it is an integer)
Select NVL(ClearName, XXX_XXX_XXXX), ClearDate ..... from table name

XXX_XXX_XXXX can be any pre defined value

and check in the code
If (adoMRS.Fields("ClearName") = XXX_XXX_XXXX) Then
instead of If IsNull(adoMRS.Fields("ClearName")) Then

Good Luck
0
 
8tricksCommented:
You didnt indicate which line of code that contains that error, but i think it is the 'If' and 'ElseIF' part that you have used.

1) If IsEmpty(NULL) Then
function IsEmpty cannot use to check Null so this will throw false
You must change this to use -> If IsNull(adoMRS.Fields("ClearName") Then
                                                ====
2) ElseIf adoMRS.Fields("ClearName") <> "" Then
0
 
8tricksCommented:
You didnt indicate which line of code that contains that error, but i think it is the 'If' and 'ElseIF' part that you have used.

1) If IsEmpty(NULL) Then
function IsEmpty cannot use to check Null so this will throw false
You must change this to use -> If IsNull(adoMRS.Fields("ClearName") Then
                                                ====
2) ElseIf adoMRS.Fields("ClearName") <> "" Then
0
 
8tricksCommented:
continued....

2) ElseIf adoMRS.Fields("ClearName") <> "" Then
I do not recommend this. Split the else if. Use..
 
Else
If adoMRS.Fields("ClearName") <> "" or isEmpty(adoMRS.Fields("ClearName")) Then
....

End If
End If

0
 
rajaloysiousCommented:
The below statement works with me - from VB 6 connecting to Oracle 8i
It works for a string column with null and not null values perfectly well
If IsNull(adoMRS.Fields("char_value")) Then

''''''''''''''''''''''''''''''''''''''''''
    If IsNull(adoMRS.Fields("Col1")) Then
        MsgBox "Col1 value is null"
    End If
   
    If Not IsNull(adoMRS.Fields("Col2")) Then
        MsgBox "Col2 is " & adoMRS.Fields("Col2")
    End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
You have said initially that this does not work out.
I guess there is not an issue with this ISNull.

Check with the columns in the table. Check whether BOF is true ... in case
Also try MoveFirst ( even though it is not necessary). Also try my options in the database.
0
 
virenderpratapCommented:
"(10075) Null value can't be used here"

doesn't say that your
>If IsNull(adoMRS.Fields("ClearName")) Then
line of code has any error.
Error 10075 can be occured due to your invalid cursor position or statement.

Even if u want to get the size of Fields("ClearName")  then use ActualSize Property


>If adoMRS.Fields("ClearName").Actualsize < 1 Then
then allocate ur String.(or Whatever).



0
 
_ys_Commented:
Try this:

If "" = (adoMRS.Fields("ClearName") & "") Then
 ...
0
 
taherzmCommented:
which db are u using?
cant u use the IIF condition in the SELECT query itself?
0
 
lexoAuthor Commented:
I am using SQL..sure I do whatever...I just need it to work.  Wouldn't I have to use two queries?  No big deal there either though.
0
 
lexoAuthor Commented:
Public Function checkNull(ByVal tmpString As Variant) As String
    If IsNull(tmpString) Then
        checkNull = ""
    Else
        checkNull = tmpString
    End If
End Function

and change the code to

If IsNull(adoMRS.Fields("ClearName")) Then

  strCD = "      "
  strCN = "  "
  strT1 = strCD & " " & strCN & " " & checknull(adoMRS.Fields("Code")) & " " & checknull(adoMRS.Fields("Description"))

elseIf adoMRS.Fields("ClearName") <> "" Then

     if not isdate(checknull(adoMRS.Fields("ClearDate"))) then
          msgbox "The value of Clear date is invalid"
     else

          strT1 = Format(adoMRS.Fields("ClearDate"), "mm/dd/yy") & " " & Left(adoMRS.Fields("ClearName"), 2)    & " " &      _
          checknull(adoMRS.Fields("Code")) & " " & checknull(adoMRS.Fields("Description"))
     end if
end if


...this errors at: Public Function checkNull(ByVal tmpString As Variant) As String

0
 
_ys_Commented:
Any luck with what I suggested?

>> If "" = (adoMRS.Fields("ClearName") & "") Then
0
 
taherzmCommented:
try this out -
strSql = "Select field1,field2,IIF(ISNULL(field3),"stringvalue_ifTrue","stringValue_ifFalse") from UATracking_file  Where Loan_id= '" & strProdID & "' AND RemoveCond='" & strCond & "' Order by Code"

please replace field1,field2,field3 with the approriate fieldnames...
and mention all the fields you want your query to fetch...

this way u will not need 2 queries...the query itself sets the appropriate value depending onthe value of the field...



0
 
lexoAuthor Commented:
OK ok, this is not the problem I thought it was.  DAMN IT!!

For some reason it's thinking that (adoMRS.Fields("Description") is null, even though it's not in the db (SQL).  here's the code, that works, but my string is not including the value of (adoMRS.Fields("Description"))..it's blank in the string, but not null in the db...what gives?

If IsNull (adoMRS.Fields("Description")) Then
adoMRS.Fields("Description") = ""
End If

 If "" = (adoMRS.Fields("ClearName") & "") Then


strCD = "       "
strCN = "  "

strT1 = strCD & " " & strCN & " " & adoMRS.Fields("Code") & " " & adoMRS.Fields("Description")
Else
'adoMRS.Fields("ClearName") isnot "" 
      strT1 = Format(adoMRS.Fields("ClearDate"), "mm/dd/yy") & " " & Left(adoMRS.Fields("ClearName"), 2) & " " & adoMRS.Fields("Code") & " " & adoMRS.Fields("Description")
End If  

      Do
         Select Case Len(strT1)
            Case Is <= 100
               strT2 = strT1
               strT1 = ""
            Case Is > 100
               strT2 = Left(strT1, 100)
               strT1 = Trim(Mid(strT1, 101))
         End Select
         intReg = intReg + 1
         SetRegString intReg, strT2

      Loop Until Len(strT1) = 0

      adoMRS.Movenext
   
 Wend  
   Set adoMRS = Nothing
   Set adoMCN = Nothing
0
 
sbennettsCommented:
Have you tried
If "" & adoMRS.Fields("ClearName")= "" Then
   ...
else
   ...
end if
0
 
Anthony PerkinsCommented:
>>For some reason it's thinking that (adoMRS.Fields("Description") is null, even though it's not in the db (SQL).  <<
Again, I can help, but please first close this old open question:
11/14/2003 How to "decode" this XML file  Open Web Development
http://www.experts-exchange.com/Web/Q_20798684.html

Let me know.
0
 
lexoAuthor Commented:
>>For some reason it's thinking that (adoMRS.Fields("Description") is null, even though it's not in the db (SQL).  <<
Again, I can help, but please first close this old open question:
11/14/2003 How to "decode" this XML file  Open Web Development
http://www.experts-exchange.com/Web/Q_20798684.html

Let me know.

I've got this issue fixed...I'd love to close the questions, is there any way to do it other than assigning the points?
0
 
Anthony PerkinsCommented:
>>I'd love to close the questions, is there any way to do it other than assigning the points?<<
Sure.  From the EE Guidelines at:
http://www.experts-exchange.com/help.jsp#hi67
<quote>
What are my choices?  
 
You have five choices when it comes to closing your open questions:
Accept an Expert's Comment as the Answer http://www.experts-exchange.com/help.jsp#hi68
Accept Comments from more than one Expert (a Split) http://www.experts-exchange.com/help.jsp#hi69
Request a refund because you answered your own question (Refund/PAQ) http://www.experts-exchange.com/help.jsp#hi69
Request a refund because no one answered your question (Delete) http://www.experts-exchange.com/help.jsp#hi70
Abandon your question and leave the mess for someone else to clean up  http://www.experts-exchange.com/help.jsp#hi78
</quote>

>>I've got this issue fixed<<
Glad to here it.  So you no longer need help with this question?  If so, please close this question (and the other cross-posted question) as per the third choice.
0
 
lexoAuthor Commented:
Abandon your question and leave the mess for someone else to clean up  http://www.experts-exchange.com/help.jsp#hi78
 sounds good to me, I don't have time to do whatever your asking me...
0
 
Anthony PerkinsCommented:
Here is the full text:
<quote>
There are several possible actions that may be taken, and while the Moderators and Page Editors don't like any of them, they are also very much inclined to take them.

By way of explanation, the Moderators and Page Editors consider abandoning questions as a violation of the Membership Guidelines, specifically the clause regarding "abuse of the points system", in that you've promised points for an answer and have received it, but haven't followed through in awarding them. Further, among the criteria used in selecting them is their tenure and familiarity with both Experts Exchange and a wide variety of subjects. As such, they are going to be inclined to believe Experts who say they, in fact, answered a question you've asked -- so they'll also be inclined to not believe you when you say that you didn't get an answer if 23 of your 32 asked questions are still open.

So here's what can happen. The first step is that a Page Editor or Moderator will ask you, politely, to deal with your open questions. Most of the Experts will tell you that it's not a good idea to ignore that request.

If you choose to ignore the request, you will probably receive an email, usually including a list of your open questions. You'll be asked to close them, usually within a week. Such an email is your last warning.

The third step is that your account gets suspended. You will receive an email notification; your account will be reinstated only after you've agreed to some severe conditions, and the Moderators and Page Editors will pay attention to your behavior.
</quote>
0
 
issamtaherCommented:
hi lexo

maybe it is strange way but it's work

if  adoMRS.Fields("ClearName") &"A" = "A" then
' this mean the value is null
else
' value not null
endif

i tried by myself

good luck
0
 
lexoAuthor Commented:
Actually this problem was solved when I held a string value with the column value and usd that in sting strT1...

I would like to split the points with all of you just for trying....
0
 
Anthony PerkinsCommented:
Thank you for maintaining your old open question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 4
  • 4
  • +9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now