Working with null vlaues 3

[trezraven]

I have created a form using Word 2007 that queries an Access database. If the Access field, LT_Cases is null, I am trying to replace it with the text "None Given". Here is the code I have tried so far. I have used each block of commented code, separately, but to no avail. I get runtime error 94: invalid use of Null. Any help will be greatly appreciated.

'*****Add formatting for table*****
    .TypeText Text:="RE:   "
    .TypeText Text:=txtAppellant.Value
    .MoveRight unit:=wdCell
    .TypeText Text:="v.    " & txtAppellee.Value
    .MoveLeft unit:=wdCell
    .MoveDown unit:=wdLine, Count:=2
    .TypeText Text:="Docket No:  " & txtCaseNumber.Value
    .MoveDown unit:=wdLine, Count:=1
    .TypeText Text:="Lower Tribunal Case No.:  " & txtLowerTrib.Value
'        If IsNull(CoverLetter.txtLowerTrib.Value) Then
'        CoverLetter.txtLowerTrib.Value = "None Given"
'        .TypeParagraph
'        .TypeParagraph
'        End If
        
'        If Len(CoverLetter.txtLowerTrib & "") = 0 Then
'        CoverLetter.txtLowerTrib.Value = "None Given"
'        .TypeParagraph
'        .TypeParagraph
'        End If

'       =IIf(IsNull([LT_Cases]) or[LT_Cases] = ""),"None Given",[LT_Cases])

 

[TonyJollans]

You haven't really posted enough code for me to understand what's going on - are you running this in Word or Access? Whichever, why can't you just use an NZ function in the SQL?

When you get over this issue you might like to consider better ways of building up your Document. Using the Selection is rarely the best method - and relying on UI-based features (the insertion point, or cursor position) is error-prone without knowledge of all the factors affecting the user view.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[trezraven]

I am running this in Word, but pulling information from an Access db. Here is more code.

Private Sub btnGetData_Click()
Dim conn                As New ADODB.Connection
Dim rs                  As New ADODB.Recordset
Dim lngConnectionState  As Long
Dim strSQL              As String
Dim CurDoc              As Document
Dim Addressee_Name      As Variant
Dim Addressee           As Variant
Dim LT_Cases            As Variant


'*****Set up the connection to the database*****
conn.ConnectionString = "Provider=MSDAORA; Data Source=TSD1; User ID=omitted; Password=omitted"

'*****Open the connection to the database*****
conn.Open
Set rs = New ADODB.Recordset

''*****Check the state of the database*****
lngConnectionState = conn.State

'*****Set the data source*****
strSQL = "Select Mandate_Type, Mandate_Date, Date_Mandate_Released, Addressee, Appellant, Appellee, CaseNo, Lt_Cases, Accident_Date, Addressee_Name, Recipients " & _
         "From CMS.V_Macro4CoverLetter " & _
         "Where Date_Mandate_Released between to_date('" & IIf(IsNull(CoverLetter.txtStart.Value), #1/1/100#, CoverLetter.txtStart.Value) & "', 'mm/dd/yyyy') and to_date('" & IIf(IsNull(CoverLetter.txtEnd.Value), #12/31/9999#, CoverLetter.txtEnd.Value) & "', 'mm/dd/yyyy')" & _
         "Or CaseNo Like '" & IIf(IsNull(CoverLetter.txtCaseNumber.Value), "*", CoverLetter.txtCaseNumber.Value) & "'" & _
         "Order by CaseYear, CaseNo "

'*****Open the recordset*****
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic

'*****Get the data if not end of the record set*****
If rs.EOF Then
MsgBox "There is no information in the database! Please verify your date, mandate type or case number.", vbCritical, "ERROR!"
End If

rs.MoveFirst
If Not rs.EOF Then
    Do Until rs.EOF
CoverLetter.txtMandateDate = rs.Fields("Mandate_Date").Value & " "
CoverLetter.txtAddressee = rs.Fields("Addressee").Value & " "
CoverLetter.txtAppellant = rs.Fields("Appellant").Value & " "
CoverLetter.txtAppellee = rs.Fields("Appellee").Value & " "
CoverLetter.txtCaseNumber = rs.Fields("CaseNo").Value & " "
CoverLetter.txtLowerTrib = rs.Fields("LT_Cases").Value & " "
CoverLetter.txtRecipients = rs.Fields("Recipients").Value & " "
CoverLetter.txtStart.Value = " "
CoverLetter.txtEnd.Value = " "

'*****Hide the form so the document can come up*****
CoverLetter.Hide

'*****Add formatting for table*****
    .TypeText Text:="RE:   "
    .TypeText Text:=txtAppellant.Value
    .MoveRight unit:=wdCell
    .TypeText Text:="v.    " & txtAppellee.Value
    .MoveLeft unit:=wdCell
    .MoveDown unit:=wdLine, Count:=2
    .TypeText Text:="Docket No:  " & txtCaseNumber.Value
    .MoveDown unit:=wdLine, Count:=1
    .TypeText Text:="Lower Tribunal Case No.:  " & txtLowerTrib.Value
'        If IsNull(CoverLetter.txtLowerTrib.Value) Then
'        CoverLetter.txtLowerTrib.Value = "None Given"
'        .TypeParagraph
'        .TypeParagraph
'        End If
        
'        If Len(CoverLetter.txtLowerTrib & "") = 0 Then
'        CoverLetter.txtLowerTrib.Value = "None Given"
'        .TypeParagraph
'        .TypeParagraph
'        End If

'       =IIf(IsNull([LT_Cases]) or[LT_Cases] = ""),"None Given",[LT_Cases])

Thanks!!!

 

[TonyJollans]

Can you not change your SQL to ...

[blue][tt]strSQL = "Select Mandate_Type, ..., [/tt][red][tt]NZ([/tt][/red][tt][Lt_Cases][/tt][red][tt],"None given")[/tt][/red][tt], Accident_Date, ... [/tt][/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[trezraven]

I changed my strSQL to

strSQL = "Select Mandate_Type, Mandate_Date, Date_Mandate_Released, Addressee, Appellant, Appellee, CaseNo, NZ([Lt_Cases],"None Given"), Accident_Date, Addressee_Name, Recipients " & _

and now I get compile error: Expected end of statement and None is highlighted.

 

[TonyJollans]

Sorry .. to get quotes inside quotes you must double them ..

[blue][tt]..., NZ([Lt_Cases],[/tt][red][tt]""[/tt][/red][tt]None given[/tt][red][tt]""[/tt][/red][tt]), ...[/tt][/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[trezraven]

I did as you suggested and changed my SQL to

strSQL = "Select NZ([Lt_Cases], ""None Given""), Mandate_Type, Mandate_Date, Date_Mandate_Released, Addressee, Appellant, Appellee, CaseNo, Accident_Date, Addressee_Name, Recipients " & _
         "From CMS.V_Macro4CoverLetter " & _
         "Where Date_Mandate_Released between to_date('" & IIf(IsNull(CoverLetter.txtStart.Value), #1/1/100#, CoverLetter.txtStart.Value) & "', 'mm/dd/yyyy') and to_date('" & IIf(IsNull(CoverLetter.txtEnd.Value), #12/31/9999#, CoverLetter.txtEnd.Value) & "', 'mm/dd/yyyy')" & _
         "Or CaseNo Like '" & IIf(IsNull(CoverLetter.txtCaseNumber.Value), "*", CoverLetter.txtCaseNumber.Value) & "'" & _
         "Order by CaseYear, CaseNo "

Now I am getting ORA-00936: missing expression error. I even tried

"Select IIf(IsNull([Lt_Cases], ""None Given""),[Lt_Cases]), Mandate_Type, Mandate_Date, Date_Mandate_Released, Addressee, Appellant, Appellee, CaseNo, Accident_Date, Addressee_Name, Recipients "

 

[PHV]

In your post stamped 23 Mar 07 8:14, you may replace this:
CoverLetter.txtLowerTrib = rs.Fields("LT_Cases").Value & " "
with this:
CoverLetter.txtLowerTrib = rs.Fields("LT_Cases").Value & " "
If Trim(CoverLetter.txtLowerTrib) = "" Then
CoverLetter.txtLowerTrib = "None Given "
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TonyJollans]

I'm not up to speed on Oracle - does it not have NZ? - but the syntax of this is wrong. You have ...

[tt] "Select IIf(IsNull([Lt_Cases], ""None Given""[/tt][red][tt])[/tt][/red][tt],[Lt_Cases]), ...[/tt]

... and it should be ...

[blue][tt] "Select IIf(IsNull([Lt_Cases][/tt][red][tt])[/tt][/red][tt], ""None Given"",[Lt_Cases]), ...[/tt][/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[fumei]

I can not comment on the SQL stuff, but I would like to reiterate Tony's comment re: using Selection, and moving through the document.

You can put content into table cells directly, instead of the movement you are doing. For example, you move to the cell to the right, put in text, then move back to the original cell and move down. These movements are not needed.

Also, the table contents assume that the Selection is in fact in the table. That may be so, but it may be a good idea to check that.

However, with some assumption as to where the Selection is....and I may not have your exact movements figured out correctly...which is the point. You can put content into cells directly.

Dim oTable As Word.Table
Set oTable = ActiveDocument.Tables(1) ' or whatever table
With oTable
  .Cell(1,1).Range.Text = "RE:  " & txtAppellant.Value
  .Cell(1,2).Range.Text = "v.    " & txtAppellee.Value
  .Cell(2,1).Range.Text = _
      "Docket No:  " & txtCaseNumber.Value
  .Cell(3,1).Range.Text = _
      "Lower Tribunal Case No.:  " & txtLowerTrib.Value
 ' etc. etc.
End With

Again, I am not sure if those MoveDown Lines are moving through paragraphs, or cells - so the numbers may be incorrect.

I am also not sure why you are using two TypeText instructions for "RE: " and txtAppellant.Value. Is it your intention to have txtAppellant.Value as a new paragraph?


Gerry
My paintings and sculpture

 

[trezraven]

Thanks to everyone!!! I took PHV's advice and it worked perfectly. Also, Gerry I created another table and your way works even better.