Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    NZ function goes where (Access97-SR2)

    Where would the NZ function go in this script?
    It keeps hitting "Illegal use of Null" around the Address portions, but I'd like to make it apply to all.
    These records are imported from an existing Word form, not hand entered so I don't believe that I can validate it's implementation from a Form at this time.

    Public Sub DataStrip()
    Dim Db As Database
    Dim Rs As Recordset
    Dim Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9 As String

    Set Db = CurrentDb
    Set Rs = Db.OpenRecordset("BBBNewFinal", dbOpenDynaset)

    Do Until Rs.EOF()
    Rs.Edit
    Field2 = Trim(Rs.Fields("First_Name"))
    Field3 = Trim(Rs.Fields("Last_Name"))
    Field4 = Trim(Rs.Fields("Office"))
    Field5 = Trim(Rs.Fields("DOT"))
    Field6 = Trim(Rs.Fields("DistDiv"))
    Field7 = Trim(Rs.Fields("FinalOrg"))
    Field8 = Trim(Rs.Fields("Mailing_Address"))
    Field9 = Trim(Rs.Fields("Ct-St-Zip"))

    '
    'Place values back in fields AND format names into Upper case

    Rs.Fields("First_Name") = StrConv(Field2, vbUpperCase)
    Rs.Fields("Last_Name") = StrConv(Field3, vbUpperCase)
    Rs.Fields("Office") = StrConv(Field4, vbUpperCase)
    Rs.Fields("DOT") = StrConv(Field5, vbUpperCase)
    Rs.Fields("DistDiv") = StrConv(Field6, vbUpperCase)
    Rs.Fields("FinalOrg") = StrConv(Field7, vbUpperCase)
    Rs.Fields("Mailing_Address") = StrConv(Field8, vbUpperCase)
    Rs.Fields("Ct-St-Zip") = StrConv(Field9, vbUpperCase)

    Rs.Update
    Rs.MoveNext

  2. #2
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NZ function goes where (Access97-SR2)

    I'd say that your 'Invalid Use of Null' error is occuring when you try to set your 'field9' variable (and the value in the rs.field("Ct-St-Zip") is null).

    To alleviate this you could change the line to

    Field9 = Trim(Nz(Rs.Fields("Ct-St-Zip"), ""))

    This type of error shouldn't occur on the lines for variables field2-8 because you have actually declared these variables as variants rather than strings.

    Dim Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9 As string

    In the above line you are only setting Field9 to a string variable, and the rest of the variables to the type 'variant'. This is why you wouldn't get an error on fields2-8 (because a variant data type can hold a null value).

    You need to explicitly sate the variable type for each variable even if they are on the same line... ie.

    Dim Field2 as String, Field3 As String, Field4 As String, Field5 As String, Field6 As String, Field7 As String, Field8 As String, Field9 As String

    Cheers

    Jayden

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NZ function goes where (Access97-SR2)

    Very cool...
    So here's a general question.

    Seeing that this data will be continuously updated,
    Is it preferable to format data in the table, like this script does or at the report level?
    Where in the report would this type of event go?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: NZ function goes where (Access97-SR2)

    It's preferable in your code to explicitly declare each of your variables as a type other than the default variant. That does two things for you: it reduces the resources needed for the variables (variants are hogs), and it gives you a useful error when you try to put the wrong kind of data into a declared type (like a Null into a string variable).

    Even if you didn't get an error on the first 7 variables when you assign values to them, you will get an error if any of them contain nulls and the table field is set as required. The suggested solution to Field9 will only work if the equivalent table field allows zero-length strings, since that's what you would be passing in later in the routine. You might want to test each variable to see if it is null and then just skip that one if it is. You don't really need to write nulls into the table, after all, just skip over that field.

    If the data is being entered from a form, you can format it in the BeforeUpdate event of each control. If you're always going to be parsing the values out from a file, you might as well uppercase the data as you write it into the table. In either case, you always have the option of formatting it on the fly as well.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •