I am a beginner and attempting to "retrofit" an old .mdb (which is essentially a flat file) and move the old "cleaned up" data into my new normalized database. To do this, I am creating several procedures in a module and running from the Immediate Window. (And for other data updates/moves am using Update queries).

The current procedure I'm working with creates 2 RSs (Old [the source] and New [the target]), loops through the old, stuffs variables, then transfers the contents of the variables into the new RS record by record. To prevent VB complaints, I used the NZ function against the field before stuffing into the variable. The procedure "steps through" until it gets to updating the new table. Then it errors with "field name cannot be a 0 length string". The new table appears to accept the data (a 0 length string where the original text field was empty) when I change the property "allow 0 length strings" = true..... but before I procede, could I get some advise from experienced heads, please??

If I allow fields to contain 0 length strings in the new table, are there other code strategy changes I'll need to make? (I imagine "IsNull" will no longer work)....

As the tables are populated with more data, will they take up more space if empty text and memo fields contain a 0 length string as opposed to a null?

Can I change a 0 length string back to a null? (I could not find a function to do that when I looked).

Thanks,

Loralee

Sub PopulateCTUProviders()
' use to populate tblCTUPVendor from the old CTU database data

Dim lngContactID As Long
Dim strBusAddress As String
Dim strBusCity As String
Dim strPhone As String
Dim strFax As String
Dim strCategory As String
Dim blnActive As Boolean
Dim strComments As String
Dim strPrint As String
Dim db As DAO.Database
Dim rsOLD As DAO.Recordset
Dim rsNew As DAO.Recordset

Set db = CurrentDb
Set rsOLD = db.OpenRecordset("ctuproviders", dbOpenDynaset)
Set rsNew = db.OpenRecordset("tblCTUVendor", dbOpenDynaset)

rsOLD.MoveFirst
Do While Not rsOLD.EOF
    lngContactID = Nz(rsOLD!contactid)
    strBusAddress = Nz(rsOLD!busaddress)
    strBusCity = Nz(rsOLD!buscity)
    strPhone = Nz(rsOLD!phone)
    strFax = Nz(rsOLD!fax)
    strCategory = Nz(rsOLD!Category)
    blnActive = Nz(rsOLD!active)
    strComments = Nz(rsOLD!comments)
    strPrint = Nz(rsOLD!Print)

    With rsNew
            .MoveFirst
            .FindFirst "[contactidtemp] = " & lngContactID
        If rsNew.NoMatch Then
            MsgBox "There is no match for provider"
            Exit Sub
        Else
            .Edit
            !busaddress = strBusAddress
            !BusCityTemp = strBusCity
            !busphone = strPhone
            !busfax = strFax
            !Categorytemp = strCategory
            !active = blnActive
            !comments = strComments
            !Print = strPrint
            .Update
        End If
    End With
rsOLD.MoveNext

Loop

rsNew.Close
rsOLD.Close
Set rsOLD = Nothing
Set rsNew = Nothing

End Sub

The cause is that you are using:

strBusCity = Nz(rsOLD!buscity)

If the field has a Null value then this causea the Null value to be converted to an emptry string.


then

!BusCityTemp = strBusCity

gets an error since strBusCity is empty and can not be a zero length.


If you want to use your method, you will have to check to see if the strings are empty before assigning the vaule like:

if len(strBusCity) > 0 then
  !BusCityTemp = strBusCity
end if

You will have to do this for every text field.


HTH...










Boyd
Access Based Accounting/Business Solutions developer.

Loralee, you said:

"If I allow fields to contain 0 length strings in the new table, are there other code strategy changes I'll need to make? (I imagine "IsNull" will no longer work)...."

Not quite. Zero-length strings are not the same as Null. Null means absolutely nothing. A zero-length string is something. It's a string with no characters in it, but the string itself exists. Think of it as a placeholder with nothing in it (yet).

Here's a tangible example. You want to find a list of crates were the egg cartons contained within are empty. Some crates don't have any egg cartons (NULL). Some crates have egg cartons, but the egg cartons are empty. The cartons themselves are the empty strings. Some crates have egg cartons that have eggs in them. Those "strings" are not empty (i.e. zero-length).

Therefore, the IsNull function will still work if you're trying to find truly null fields. If you want to find a list of data that are both null OR zero-length, you can do something like this:

If Len(Nz(varMyVariable, "")) = 0 Then

If varMyVariable is Null, the the NZ function will turn it into an empty string "". Then the LEN function will evaluate to zero. If varMyVariable is zero-length to begin with, the NZ will just return "" anyway and LEN will still give you zero.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thank you, Greg.

Would the zero length string then take up more space than if I allowed the field to be null? (The tables I'm working with have only about 300 records, but when I get to moving data into another set of tables there will be about 20,000+ records in them.)

And is there a better strategy? This is just a "seat of the pants" method to disperse data into the correct tables so the thing works better.

Loralee

"Would the zero length string then take up more space than if I allowed the field to be null?"

Yes. The rule of thumb is that if you don't want data stored, don't store it. Don't set a field equal to "" or even a space if it's null because you are now storing SOMETHING into the database (not only that but your queries will not work correctly when searching for no data). One or two is not a great deal of overhead, but if you're storing thousands of these, they add up. If you truly want to empty a field that has a value, set it to null.



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks, Greg.

I was not able to find a way to change a zero length string back into a null, (at least it kicked back "invalid use of null" when I tried) so it looks like the best course of action is to start again and use Boyd's suggestion and not the NZ function.

On second thought, and several re-reads, the implication is that one CAN return a field to null.......
"If you truly want to empty a field that has a value, set it to null."

Did I do something else wrong here?

Sub TestNull()
Dim strFax As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblCTUVendor", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
    strFax = rs!busfax
    If strFax = "" Then
        strFax = Null ' scores error 94 here "invalid use of null"
    End If
    rs.Edit
    rs!busfax = strFax
    rs.Update
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing

End Sub
 and thank you!

Loralee

Sure you can! Just change the datatype of strFax.

Old: Dim strFax as String

New: Dim varFax as Variant

And change all strFax in your code to varFax. Strings cannot be null, but variants can!


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks, Greg. I'll give that a try!

Loralee