Any easy way to remove all initial value long integers from 0? (1 Viewer)

[JTQ911]

Hey, is there any way in some sort of module or macro to remove the intitial value from every long integer in a table or all tables in a database

 

[KenHigg]

cint(right(cstr(MyInt),len(MyInt)-1))

???

 

[JTQ911]

ken, thanks again. Do I put this into a new module?

 

[KenHigg]

Depends on what it is you're doing. You could leave the original data the way it is and plug the conversion in a query or a form, etc

 

[raskew]

Another method:

x = 1234567
? x mod 10^(len(x)-1)
234567

 

[KenHigg]

Another method:

x = 1234567
? x mod 10^(len(x)-1)
234567

I be darn rask, pretty cool. You just come up with that? Are you some kind of math whiz?

 

[Pat Hartman]

You need to write code that reads the tabledef collection and loops through the field collection looking for the fields with a data type of Long Integer.
Microsoft got the picture with A2007 and went back to not specifying 0 as the default and causing this problem to begin with.

 

[twoplustwo]

Would the LTrim fn not work?

 

[KenHigg]

You need to write code that reads the tabledef collection and loops through the field collection looking for the fields with a data type of Long Integer.
Microsoft got the picture with A2007 and went back to not specifying 0 as the default and causing this problem to begin with.

I must have missed something - How did you extrapolate that from the original issue? It did seem odd that anyone would need to remove the leading number from a value but I don't think I've ever seen a default zero cause an extra leading number to be added -?!?

 

[Pat Hartman]

JTQ911 asked to remove the initial value of a long integer. I believe everyone else read that to be the leading character of a string.

Since long integers are not strings it makes no sense to remove the leading character, I assumed the poster wanted to remove the default for the long integer which is the initial value when a record is saved if no specific value is provided.

The problem is that in versions of Acces prior to A2007, the default value for long integers is set to 0 by Access. The primary use of long integers is as foreign keys and you do not under any conditions want a foreign key to default to 0. You always want it to default to null.

 

[KenHigg]

I believe I did read the incorrectly. Odd that JT didn't respond and say so - ?

 

[datAdrenaline]

This is the code I use ... its basically Pat's advice all fleshed out. Note the I included all the Field Sizes of the Numeric data type (Access Table Design UI data type, that is), as well as the Currency data type since all those types have a default value of 0 from A2003 and lower. I included some other JET datatypes as well just for good measure.

Public Function FixDefaultOfZero() As Byte
 
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
 
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If Len(tdf.Connect) > 0 Or Left(tdf.Name, 4) = "MSys" Then
            'Do nothing
        Else
            Debug.Print tdf.Name
            For Each fld In tdf.Fields
                Select Case fld.Type
                Case dbBigInt, dbByte, dbCurrency, dbDecimal, dbDouble, dbFloat, _
                     dbInteger, dbLong, dbNumeric, dbSingle
 
                    fld.DefaultValue = "Null" 'Note that the DefaultValue
                                              'property expects a STRING
 
                End Select
            Next
        End If
    Next
 
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
 
End Function

 

[Pat Hartman]

Thanks for posting the code. Did you post the original question Brent?

 

[datAdrenaline]

>> Thanks for the code <<

... Always a pleasure to help out!

>> Did you post the original question Brent? <<

Nope ... I was responding to JTQ911's original question that read:

>> Hey, is there any way in some sort of module or macro to remove the intitial value from every long integer in a table or all tables in a database <<

Basically adding to your response ... but with code ...

 

[Pat Hartman]

So we really don't know what JTQ911 was asking Ken, Bob, and two thought he was asking one thing and I thought it was another.

 

[datAdrenaline]

Hello Pat ...

I read it the same as you ... I took it to mean the default value of the field ...