Max Len of all fields in table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I need to run a quick check on a table which will tell me the most
characters used in each field.

I can use

SELECT Max(Len(myfieldname)) as fieldnameMaxLen,
FROM mytable

which works for one field at a time but how can I change it so that it give
me the max len of each field in the table even if I add/remove fields.

Cheers

Matt
 
You can't do this with a query and add and remove fields.

SELECT Max(Len(FieldA)) as MaxA, Max(Len(FieldB)) as MaxB, Max(Len(fieldC))
as MaxC, ...
FROM SomeTable

You could build a custom user procedure that would loop through the fields
of a table and build the query for you or just extract the values one field
at a time.

Sample VBA function: Call it passing the tablename.
BuildMaxSQL("SomeTableName")


Public Function buildMaxSQL(strTableName as string)
Dim strSQL As String
Dim dbAny As DAO.Database
Dim fldAny As DAO.Field
Dim rstAny As DAO.Recordset

Set dbAny = CurrentDb()

With dbAny.TableDefs(strTableName)
For Each fldAny In .Fields
strSQL = strSQL & ", Max(Len([" & .Name & "].[" & fldAny.Name &
"])) as " & fldAny.Name
Next fldAny

strSQL = "SELECT " & Mid(strSQL, 2) & " FROM [" & .Name & "]"
End With

Set rstAny = dbAny.OpenRecordset(strSQL)

strSQL = vbNullString
If rstAny.RecordCount = 1 Then
For Each fldAny In rstAny.Fields
strSQL = strSQL & "max length of " & fldAny.Name & ": " &
Nz(fldAny.Value, 0) & vbCrLf
Next fldAny
buildMaxSQL = strSQL
End If

End Function
 
Whoops, I forgot to account of line wrapping in the newsgroup. So you will
need to fix the wrapped lines. Also, I didn't cover field or table names
that have spaces in them (or other non-alpha numeric characters) very well.
You could end up with errors based on that.

John Spencer said:
You can't do this with a query and add and remove fields.

SELECT Max(Len(FieldA)) as MaxA, Max(Len(FieldB)) as MaxB,
Max(Len(fieldC)) as MaxC, ...
FROM SomeTable

You could build a custom user procedure that would loop through the fields
of a table and build the query for you or just extract the values one
field at a time.

Sample VBA function: Call it passing the tablename.
BuildMaxSQL("SomeTableName")


Public Function buildMaxSQL(strTableName as string)
Dim strSQL As String
Dim dbAny As DAO.Database
Dim fldAny As DAO.Field
Dim rstAny As DAO.Recordset

Set dbAny = CurrentDb()

With dbAny.TableDefs(strTableName)
For Each fldAny In .Fields
strSQL = strSQL & ", Max(Len([" & .Name & "].[" & fldAny.Name &
"])) as " & fldAny.Name
Next fldAny

strSQL = "SELECT " & Mid(strSQL, 2) & " FROM [" & .Name & "]"
End With

Set rstAny = dbAny.OpenRecordset(strSQL)

strSQL = vbNullString
If rstAny.RecordCount = 1 Then
For Each fldAny In rstAny.Fields
strSQL = strSQL & "max length of " & fldAny.Name & ": " &
Nz(fldAny.Value, 0) & vbCrLf
Next fldAny
buildMaxSQL = strSQL
End If

End Function


Matt D Francis said:
Hi

I need to run a quick check on a table which will tell me the most
characters used in each field.

I can use

SELECT Max(Len(myfieldname)) as fieldnameMaxLen,
FROM mytable

which works for one field at a time but how can I change it so that it
give
me the max len of each field in the table even if I add/remove fields.

Cheers

Matt
Click to expand...
Click to expand...
 
Back
Top