Max function in vba

[knowledge76]

Hello friends,

I am trying to scan the fields of a table with the following code. This should normally give me the maximum length value for each field in the table. However max is not working in vba code as it normally works on table/query level. I therefore used DMAX but it is not working. Any idea how to used the MAX functionality in VBA??
Thanks for your suggestions.

Option Compare Database

Sub FieldsAnalysis()

On Error GoTo maxlength_error

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer


Set db = CurrentDb
strobjectname = InputBox("Enter name  of table or query to analyse the fields", "Fields Analysis")
Set rs = db.OpenRecordset(strobjectname, dbOpenDynaset)


Debug.Print "Field Name          " & "Maximum Length"
Debug.Print "------------------------------"

Do Until rs.EOF
For i = 0 To rs.Fields.Count

Debug.Print rs.Fields(i).Name; "------->" & DMax(Len(rs.Fields(i).Name), strobjectname)


Debug.Print rs.Fields(i)
Next i
Loop
maxlength_error:
Exit Sub
End Sub

 

[namliam]

Why not simply use the SQL Select Max in a SQL statement??

Would be much easier wouldnt it?

 

[knowledge76]

I have partially found the mistake it was in the line

Debug.Print rs.Fields(i).Name; "------->" & DMax(Len(rs.Fields(i).Name),strobjectname)

But now I am just scanning just one record and not whole of the recordset in the code.

Option Compare Database 

Sub FieldsAnalysis() 

On Error GoTo maxlength_error 

Dim db As DAO.Database 
Dim rs As DAO.Recordset 
Dim i As Integer 


Set db = CurrentDb 
strobjectname = InputBox("Enter name  of table or query to analyse the fields", "Fields Analysis") 
Set rs = db.OpenRecordset(strobjectname, dbOpenDynaset) 


Debug.Print "Field Name          " & "Maximum Length" 
Debug.Print "------------------------------" 

Do Until rs.EOF 
For i = 0 To rs.Fields.Count 

Debug.Print rs.Fields(i).Name; "------->" & DMax(Len(rs.Fields(i).Name),strobjectname)

Debug.Print rs.Fields(i) 
Next i 
Loop 
maxlength_error: 
Exit Sub 
End Sub

 

[DCrake]

What are you actually trying to determine?

a) the maximum lenght of the contents of a field
b) the max lenght property of a text field

You say in you second post that you were not testing all records in rescordset, you still are not becuse you do not have .MoveNext in your Do.. Loop section.

And as MaxLength only applies to Text fields you are not testing for that
A bit more clarification would be helpful.

David

 

[namliam]

Why not simply use the SQL Select Max in a SQL statement??

Would be much easier wouldnt it?

Why not simply use the SQL Select Max in a SQL statement??

 

[knowledge76]

What are you actually trying to determine?

a) the maximum lenght of the contents of a field
b) the max lenght property of a text field

You say in you second post that you were not testing all records in rescordset, you still are not becuse you do not have .MoveNext in your Do.. Loop section.

And as MaxLength only applies to Text fields you are not testing for that
A bit more clarification would be helpful.

David

I want to get the maximum lenght of the contents of a field.

 

[wheelchair]

DMax(Len(rs.Fields(i).Name),strobjectname) will be firstly calculated to DMax(Numberic Value,strobjectname) , so DMax will not return the right thing you are looking for. You'd better to use select ... max(len(fieldname)) from....., will give you the max len and the field value.

By the way, do not believe you need to loop the record if you DMax.

 

[namliam]

Why not simply use the SQL Select Max in a SQL statement??

AM I *BLEEPING* INVISIBLE ??? !!!







You need to dynamicaly build a query to get the max(len(yourfield)) from the table you want to analyze!!!

 

[knowledge76]

AM I *BLEEPING* INVISIBLE ??? !!!







You need to dynamicaly build a query to get the max(len(yourfield)) from the table you want to analyze!!!

could you please give me an example how you would implement it with dynamic query. Itried with

strsql =select max(len(rs!fields(i))) from & strobjectname
docmd.runsql strsql

is gving me error.

 

[namliam]

strsql = " select max(len([" & rs!fields(i) & "])) from " & strobjectname
docmd.runsql strsql

Something like above... you need to " every string ...

 

[knowledge76]

strsql = " select max(len([" & rs!fields(i) & "])) from " & strobjectname
docmd.runsql strsql

Something like above... you need to " every string ...

I am playing with " and ' with all the combinations but the code rs.fields(i) value is not read by the code. Is there a good link where I can read about the logic how to insert variables in sql statement in vba.
Thanks again

 

[namliam]

The way I gave it to you should work... With one exception, since this is not an action query you cannot use RunSQL... you have to actually open the SQL.

Dim rs as DAO.Recordset ' << If you get an error here search for the error on the forum
strsql = " select max(len([" & rs!fields(i) & "])) as Field" & i & " from " & strobjectname
set rs = Currentdb.openrecordset(strsql)

 

[knowledge76]

strsql = "Select max(Len([" & rs.Fields(i).Name & "]))  From " & strobjectname & ";"

The above is working but I am getting runtime error 2342
"A runsql action requires an argument consisting of an sql statement."

 

[gemma-the-husky]

to go back to the beginning -

a) why on earth do you need to find the longest string?

b) do it with 2 visual queries
first, do a query that evaluates the length of the string in which you are interested in.
second, do another query to find the max value of that length

although this is probably the same as already advised, its so much easier to do these things in visual queries

 

[namliam]

The above is working but I am getting runtime error 2342
"A runsql action requires an argument consisting of an sql statement."

Correct, which is what I said...

Suggest you re-read my post...

The way I gave it to you should work... With one exception, since this is not an action query you cannot use RunSQL... you have to actually open the SQL.

Dim rs as DAO.Recordset ' << If you get an error here search for the error on the forum
strsql = " select max(len([" & rs!fields(i) & "])) as Field" & i & " from " & strobjectname
set rs = Currentdb.openrecordset(strsql)

 

[knowledge76]

Thanks all of you at last it is working.
Tahnks again

 

[namliam]

and your ultimate solution (for posterity?)