Re: Invalid Use of Null

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Brian (bcap_at_IHATESPAMclara.co.uk)
Date: 03/10/05 

Date: Thu, 10 Mar 2005 09:56:37 -0000

"Steven M. Britton" <StevenMBritton@discussions.microsoft.com> wrote in
message news:8D9F2973-5FCB-4417-98B7-60603BD779B0@microsoft.com...
> I have a txt file that I import into an Access table. After it is in the
> table I need to compare the above record to the below record for
differences.
> I am having trouble comparing a field if it's blank or Null. If both the
> current record and the next record IsNull I can skip those two records.
> However, if the current record IsNull and the next record isn't Null or
vice
> versa, I would need to update the records. This has to do with parts
being
> an option or standard in a build. They can switch from standard or
optional
> or the other direction as well as having price changes or qty changes.
>
> This is what I have so far and get the error message "Invalid use of
Null" -
> I should also state that this txt file comes from an ancient system that I
> have no control over and can't change. :(
>
>
>
> DoCmd.Hourglass (True)
> Set db = CurrentDb()
> Set rs = db.OpenRecordset("MM877PF5")
>
> Dim strDate, strTime, strUser, strChangeType, strBfrAccy, strAftAccy As
> String
>
> rs.MoveFirst
> Do While Not rs.EOF
> strChangeType = rs!ChangeType
>
> If strChangeType = "Before" Then GoTo 500
> If strChangeType = "DELETED" Then GoTo 600
> If strChangeType = "ADDED" Then GoTo 700
> 400
> Loop
> GoTo 800
>
> 500 'Move data from following "After" lines up to "Before" line
> rs.MoveNext
> strDate = rs!Date
> strTime = rs!Time
> strUser = rs!User
> rs.MovePrevious
> rs.Edit
> rs!Date = strDate
> rs!Time = strTime
> rs!User = strUser
> rs.Update
>
> 'did part change from basic to accy or vice-versa?
> strBfrAccy = rs!GrpAccy: rs.MoveNext: strAftAccy = rs!GrpAccy:
> rs.MovePrevious
>
> If IsNull(strBfrAccy) And IsNull(strAftAccy) Then
> rs.MoveNext
> rs.MoveNext
> Else
> If strBfrAccy <> strAftAccy Then
> rs.Edit: rs!ChangeType = "DELETED": rs.Update
> rs.MoveNext
> rs.Edit: rs!ChangeType = "ADDED": rs.Update
> rs.MoveNext
> End If
> End If
>
> GoTo 400
>
> 600
> rs.MoveNext
> GoTo 400
> 700
> rs.MoveNext
> GoTo 400
>
> 800
> DoCmd.Hourglass (False)
> Exit Sub
>
> End Sub
>

It would help if you indicated the line on which the error is occurring. I
must also point out that the use of GoTo and labels to control program flow
is very poor programming practice as it leads to "spaghetti" code, of which
this is a good example. It is never necessary to use GoTo, except in the
very specific case of an On Error statement. It is always possible to
achieve the same logic much more elegantly and readably using
control-of-flow constructs such as Do loops, For loops, If...Then and
Select...Case.

Relevant Pages

  • Re: goto
    ... > But the same is true about blanket forbidding of goto. ... > appropriate because the 'cleanup' code would need to be duplicated which ... I'd also bet that there is significant duplication within the class ... If we have to do 3 nested for loops here, ...
    (comp.lang.cpp)
  • [RFC][2/3] Account and control virtual address space allocations
    ... This patch implements accounting and control of virtual address space. ... belonging to the cgroup is incremented or decremented. ... Charge the memory controller for page usage. ... goto out; ...
    (Linux-Kernel)
  • Re: COBOL aint quite dead - yet !
    ... of control there is nothing wrong with goto. ... The loop is entirely abstracted away. ... Then we have well-formed loops with a single invariant and a looping ...
    (comp.lang.cobol)
  • Re: COBOL aint quite dead - yet !
    ... lengthy discussion in this group some time ago about a verb to break out ... any computable function can be written without GOTO. ... written without use of transfers of control (i.e., ... If you accept a definition of GOTO's as a transfer of control, ...
    (comp.lang.cobol)
  • Re: Poor style loops?
    ... you certainly could achieve the same effects with goto. ... The question at the time was whether having labelled loops, ... I have never yet encountered code ... > exceptional pigs' breakfasts, ...
    (alt.comp.lang.learn.c-cpp)