Code to fill data with zeros or spaces

[sw1085]

Hi

I've been looking for a VB code that can go through every row in my table and fill the cells with blank spaces or zeros to the maximum field length.

It would need to go through every column and row.

For example:

If I have a field that has a maximum field length of 10 characters and the cell has data which is 5 characters - I need the remainder to be filled with 5 blank spaces if its a text cell.

If I have a field that has a maximum field length of 15 characters and the cell has data which is 2 characters - I need the remainder to be filled with 13 blank spaces.

I hope someone can help with this easily.

Thanks very much in advance.

SW

 

[MarkK]

It's generally recommended that data producers should store data in as raw a form as possible, that is, without formatting. By contrast, data consumers should format their own data at retrieval time.

Another way to put it: Data is sort of bendy like a rope. Pushing it into storage is hard, pulling it out of storage is easy. Format your data when you pull it out of storage.

Make sense?

 

[Pat Hartman]

If your problem is that you need to export a fixed format recordset, Access provides a tool for that and a wizard to walk you through it. As lag said, you would never store data with space or zero padding and I'm pretty sure that Access won't even let you store space padding.

 

[sw1085]

access does let you store spaces using the Space() function.

I just don't know what the code should look like if I want to copy the current value in the field and then paste back into the field with spaces.

The logic behind this is to maintain the formatting of the data in the table data as they are our accounting software tables and the software pads the fields with spaces until the max field length.

When I edit the field data manually the spaces are lost unless I do it via an update query which is time consuming so just wanted a code that can just do through all the field data and ensure it meets the required format and if not then pad out.

Can someone help do this? I am sure it is possible.

 

[Pat Hartman]

You would need to know the fixed length you desire and you would use the Len() function to determine the actual length. If I had to do this, I would put the code in the form's BeforeUpdate event and go through every field that could be edited on the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Len(Me.Field1) < 15 Then
        Me.Field1 = Me.Field1 & Space(15 - Len(Me.Field1))
    End If
End Sub

 

[PhilsAVBAGuy]

I hope someone can help with this easily.

I don't know how easy it was but it took me 4 hours and I have a solution:
First I will say that you should limit your table information by using a MakeTable Query to select smaller recordsets to avoid using up the recordset memory while adding so many characters.
-----------Here you are
'you can pass the table name into this as a function or code a set table...just replace ' 'YOURTABLEHERE with your table
'I don't think you want to use any completely NULL fields because you will have issues with 'recordset 'memory with any large table so I start by eliminating any completely NULL fields. 'The actions on non'-Number fields was perfect but Numeric fields are a little tricky so I run a 'check to see if it 'Numeric and then add 0's instead of spaces

Dim dbDATABASE As dao.Database
Dim rsTable As Recordset
Dim fCheckField As Field
Dim vData As Variant
Dim sField As String
Dim bIsDirty As Boolean
Dim tdTABLEDEF As dao.TableDef
Dim sDelete As String
Dim arDeleteFields(50) As String
Dim iDeleteCounter As Integer
Dim iCounter As Integer
Dim iAdd0 As Integer
Dim iLengthCounter As Integer

On Error GoTo Error_Handler:

Set dbDATABASE = CurrentDb

Set tdTABLEDEF = dbDATABASE.TableDefs("YOURTABLEHERE")

Set rsTable = dbDATABASE.OpenRecordset("YOURTABLEHERE")

If rsTable.RecordCount = 0 Then 'if there are no records in the table then there are no 'actions needed

rsTable.Close
Set rsTable = Nothing
Set dbDATABASE = Nothing
Exit Sub

End If

rsTable.MoveFirst

iDeleteCounter = 0

For Each fCheckField In rsTable.Fields

bIsDirty = False

rsTable.MoveFirst

Do While Not rsTable.EOF

vData = fCheckField.Value

If IsNull(vData) Or vData = "" Then

rsTable.MoveNext

Else
bIsDirty = True 'This is the check to eliminate completely NULL fields if the
Exit Do 'field has data then move to check next field
End If

Loop

If bIsDirty = True Then

'do nothing

Else

sDelete = fCheckField.Name
iDeleteCounter = iDeleteCounter + 1
arDeleteFields(iDeleteCounter) = sDelete 'populate an array with the field numbers that 'will be deleted
End If

Next

rsTable.Close

Set tdTABLEDEF = dbDATABASE.TableDefs("YOURTABLEHERE")

iCounter = 1

For iCounter = 1 To iDeleteCounter

sDelete = arDeleteFields(iCounter)
tdTABLEDEF.Fields.Delete (sDelete)

Next

'a fresh recordset with no completely NULL fields

Set rsTable = dbDATABASE.OpenRecordset("YOURTABLEHERE")

rsTable.MoveFirst

For Each fCheckField In rsTable.Fields

sField = fCheckField.Name
rsTable.MoveFirst

Do While Not rsTable.EOF

If IsNull(rsTable.Fields(sField).Value) Then 'if the current record is blank then fill up
'with spaces
iLengthCounter = rsTable.Fields(sField).Size
rsTable.Edit
rsTable.Fields(sField) = "" & Space(iLengthCounter)
rsTable.Update
rsTable.MoveNext

Else

iCounter = Len(rsTable.Fields(sField).Value)
iLengthCounter = rsTable.Fields(sField).Size

vData = rsTable.Fields(sField).Type 'first check to see if field is Numeric
If vData = 4 Then

rsTable.Edit

Do While iCounter < iLengthCounter
rsTable.Fields(sField) = rsTable.Fields(sField) & 0
iCounter = iCounter + 1
Loop

rsTable.Update
rsTable.MoveNext

Else 'non-Numeric fields fill up with spaces
rsTable.Edit
rsTable.Fields(sField) = rsTable.Fields(sField) & Space(iLengthCounter - iCounter)
rsTable.Update
rsTable.MoveNext

End If
End If
Loop

Next

Set tdTABLEDEF = Nothing
Set rsTable = Nothing
Set dbDATABASE = Nothing

Exit Sub

Error_Handler:

Set tdTABLEDEF = Nothing
Set rsTable = Nothing
Set dbDATABASE = Nothing
MsgBox Err.Number & Err.Description
Resume Exit_Error:

Exit_Error:
Exit Sub