NZ function goes where (Access97-SR2)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote