Help with error 'invalid use of null' 1

[ooch1]

Hello,

Can someone help me.

I am trying to update field NA_CTRT_STATUS, based upon the criteria within the if statement.

However, the first (probably of many) error i get is runtime error 94 - invalid use of null' at the code highlighted below.

Can someone please help me resolve this issue, plus let me know if there are any other major gaffs with the if statement because obviously i have not tested that yet.

Sub T2CUpt()

Dim rs As ADODB.Recordset
Dim strOrderQy As String
Dim i As Integer

'create recordset objest
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection

strOrderQy = "SELECT A.MeterPointRef, A.SiteRefNum, A.[Confirmation Reference], A.[Contract Num], A.PricesIDNum, A.Price," & _
                       "A.StandingCharge, A.[Site status], A.[Effective From Date], A.[Ceased Date], A.NA_SOURCE_STATUS, " & _
                       "a.NA_Ctrt_STATUS " & _
        "FROM [A01-NBS_GAS_ROOTDATA_20040923] as A " & _
        "GROUP BY A.MeterPointRef, A.SiteRefNum, A.[Confirmation Reference], A.[Contract Num], A.PricesIDNum,A.Price, " & _
                            "A.StandingCharge, A.[Site status], A.[Effective From Date], A.[Ceased Date], A.NA_SOURCE_STATUS, " & _
                            "a.NA_Ctrt_STATUS " & _
        "ORDER BY A.MeterPointRef, A.SiteRefNum, A.[Confirmation Reference], A.[Contract Num], A.PricesIDNum;"

With rs
    .Open strOrderQy, _
    CursorType:=adOpenDynamic, _
    LockType:=adLockBatchOptimistic
    
    'Call T2CColUpt
   Dim C, M, F, S As Boolean
Dim CS As String

[COLOR=red yellow]CS = rs![NA_Ctrt_STATUS][/color]
C = rs![standingcharge]: M = rs![meterpointref]: S = rs![siterefnum] ' store Charge and MeterRef of row 2
    Do Until rs.EOF ' loop from 1st row last row
        If F And ((M <> M.MoveNext) Or (M.MoveNext = M.EOF)) And _
        (C <> 0.1055 And C <> 0.1056 And C <> 0.1089 And C <> 0.0928 And C <> 0.1193) Then
        'F And (Cells(r, "h") <> M Or r = lastR) And _
        (C <> 0.1055 And C <> 0.1056 And C <> 0.1089 And C <> 0.0928 And C <> 0.1193) Then
        ' f is true and either MeterRef changed or we're in last row, so flag preceding row and set f to false
        CS = "T2C"
        F = False
        ElseIf Not F And (M = M.MoveNext) And C <> C.MoveNext And _
        (C = 0.1055 Or C = 0.1056 Or C = 0.1089 Or C = 0.0928 Or C = 0.1193) Then
        'ElseIf Not F And Cells(r, "h") = M And Cells(r, "e") <> C And _
        '(C = 0.1055 Or C = 0.1056 Or C = 0.1089 Or C = 0.0928 Or C = 0.1193) Then
        ' f is false and Charge changed for same MeterRef, so set f to true
        F = True
        End If
        M.MoveNext ' store Charge and MeterRef of current row
    Loop ' proceed with next row
   
   Debug.Print rs.GetString
      
    rs.Close
    Set rs = Nothing
    
End With
End Sub

Thanks in advance.

OOch

 

[DoubleD]

You can't assign a Null value to a String Variable. Try this instead:

CS = Nz(rs![NA_Ctrt_STATUS],"")


I am what I am based on the decisions I have made.

DoubleD

 

[PHV]

Take a look at the IsNull and/or Nz functions.

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

 

[ooch1]

Thanks guys that solved that bit, but i now get the error
'Object required' on the first line of the if statement.

i.e

Dim C, M, F, S As Boolean
Dim CS As String

CS = rs![NA_Ctrt_STATUS]
C = rs![standingcharge]: M = rs![meterpointref]: S = rs![siterefnum] ' store Charge and MeterRef of row 2
[COLOR=red yellow]Do Until rs.EOF ' loop from 1st row last row
        If F And ((M <> M.MoveNext) Or (M.MoveNext = M.EOF)) And _
        (C <> 0.1055 And C <> 0.1056 And C <> 0.1089 And C <> 0.0928 And C <> 0.1193) Then [/color].

Have you any ideas what this is about?

OOch

 

[PHV]

M.MoveNext
NO Boolean has a MoveNext method ...

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

 

[ooch1]

PHV,

What should i be declaring these varaibles as, or should i just reference the rs.meterpointref etc or would this not work either??

Basically what i am trying to do is test the meterpointref with the next one to see if they are equal..amongst other things, but i'm not to sure how i transvers around a recordset object?

OOch