locked
MS ACCESS select Query with full text field size RRS feed

  • Question

  • User266794086 posted

    Hi,

    Can somebody tell me how to write MS ACCESS select Query which will display full text field size in a resultset,

    Ex. if a Column is company (text(15)) and the value in a row is 'Microsoft' (9 chars) then I want a result set to display Microsoft + 6 spaces = 15 chars

    whatever is the field's size minus field's value should be filled with spaces in the resultset

    Can somebody help me with this?

    Friday, March 16, 2012 6:42 AM

Answers

  • User-1199946673 posted

    Is there any way to pickup dynamic column field size instead of putting it static 15?

    Not in the same query. You can retrieve the fieldsize using another query:

            Using conn As New OleDbConnection("YourConnectionString")
                conn.Open()
                Using cmd As New OleDbCommand("SELECT fieldname FROM tablename", conn)
                    Dim ColumnSize As Integer = cmd.ExecuteReader(System.Data.CommandBehavior.SchemaOnly).GetSchemaTable().Rows(0)("ColumnSize")
                End Using
            End Using
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 20, 2012 5:04 PM

All replies

  • User-1199946673 posted

    SELECT [company] & SPACE(15 - LEN([company])) AS companyFull FROM .......

    Friday, March 16, 2012 6:28 PM
  • User266794086 posted

    Is there any way to pickup dynamic column field size instead of putting it static 15?

    Tuesday, March 20, 2012 3:32 AM
  • User-1199946673 posted

    Is there any way to pickup dynamic column field size instead of putting it static 15?

    Not in the same query. You can retrieve the fieldsize using another query:

            Using conn As New OleDbConnection("YourConnectionString")
                conn.Open()
                Using cmd As New OleDbCommand("SELECT fieldname FROM tablename", conn)
                    Dim ColumnSize As Integer = cmd.ExecuteReader(System.Data.CommandBehavior.SchemaOnly).GetSchemaTable().Rows(0)("ColumnSize")
                End Using
            End Using
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 20, 2012 5:04 PM