Results 1 to 11 of 11
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Expression too long

    Hi all,

    I am trying to output a table using a query into a .csv file or spreadsheet. I need to join all the fields from a table into one cell in the spreadsheet with a set amount of spaces between the fields of the table I am querying on.

    All goes well until I add too many fields when it comes up with an error. Which I think is because the string is too long.

    I know I have mentioned .csv and .xls in the same breath this is because I am not too concerned as if it is output into .xls in can the be saved as .csv file and works fine like this.

    What is the best way round this problem, any ideas?

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Could you just write directly to a file? Do a search in Access Help for FileSystem Object (I think that's I mean).

    John

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by HelpMePlease
    All goes well until I add too many fields when it comes up with an error. Which I think is because the string is too long.
    What is the actual error? What is the origin of the error - Access or Excel? What is the code that generates the error? How many fields are too many?

  4. #4
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    I can do that but I would have to output the data from somewhere. And, I was thinking the easiest would be a query. Actually outputting it is not the problem it is the format of what I am outputting that is causing me problem.

  5. #5
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Quote Originally Posted by pootle flump
    What is the actual error? What is the origin of the error - Access or Excel? What is the code that generates the error? How many fields are too many?

    The error I get is #Error in the query result if MS Access. As I join or concatenate the number of fields in the expression it works fine until the number of characters (I guess) is too many and the it then resuls in an error message. Until that point what is showing, is as I want, all the fields joined to together in one field.

    My Expression that I am using is
    Output: A+B+C+D+E+F+G+H+I+J+K+L+M+N+O+P+Q+R

    Each of the letters representing a field.

  6. #6
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    I would just use some VBA code to loop through a recordset and output the fields to a file with the number of spaces needed to separate each field. Don't have the query concatenate the fields together do that in the code when outputting.

    If you need an example of what I'm talking about let me know and I'll post some code.

    John

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    First thing I would try is substituting the + signs for & signs.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Quote Originally Posted by StarTrekker
    First thing I would try is substituting the + signs for & signs.
    That works a treat - Thanks for that.

  9. #9
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Quote Originally Posted by canupus
    I would just use some VBA code to loop through a recordset and output the fields to a file with the number of spaces needed to separate each field. Don't have the query concatenate the fields together do that in the code when outputting.

    If you need an example of what I'm talking about let me know and I'll post some code.

    John
    StarTrekkers reply works well, however I would be interested to see other option so if you can post a sample of the code on how you would do that would be great.

    Thanls all for your help.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Cool

    Glad it helped!

    I never use + to concatenate... only to add
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Code:
        Dim rs As DAO.Recordset
        Dim FileNumber As Integer
        Dim i As Integer
        Dim strTemp As String
        
        FileNumber = FreeFile    ' Get unused file
        Open "C:\TestFile.txt" For Output As #FileNumber    ' Create file name.
    
        Set rs = CurrentDb.OpenRecordset("SomeQueryName")
        
        Do While Not rs.EOF
            'loop through all fields in record and create a string with 5 spaces in between
            For i = 0 To rs.Fields.Count - 1
                strTemp = strTemp & rs.Fields(i).Value & Space(5)
            Next i
            
            Print #FileNumber, Trim$(strTemp)    ' Output text
            
            strTemp = ""    'reset string
            
            rs.MoveNext
        Loop
        
        Close #FileNumber    ' Close file.
    
        rs.Close
        Set rs = Nothing
    That's just a simple way to output directly to a file.

    C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •