Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    17

    Question Unanswered: Concatenate in the report or in the query

    In my report, I have a single text box which describes the current record based on a number of fields. Which of the following would be the correct way to implement this:

    - Setting the text box 'Control Source' to:
    "This record is " & [Field1] & " and " & [Field2] & " and " & [Field3]

    - or doing the concatenation in the query:
    SELECT "This record is " & [Field1] & " and " & [Field2] & " and " & [Field3] AS MyLine FROM MyTable

    I wasn't sure whether making the query do it is quicker and/or uses less memory.

    Thanks for any help,
    Stephen.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    if memory is your primary concern, then you should do it in the query.

    Otherwise, quite frankly it doesn't matter. Both will produce identicle results with very nominal performance difference.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004
    Posts
    17
    Thanks again teddy. It seems as if you are going to be my personal saviour as far as Access is concerned! As for my SQL query problem, I have resorted to dumping the results into a temporary table, but this really churns up the database and makes compacting a daily task. Don't suppose you know how to programmatically compact the database when the user closes it (I read that you can't have the database open to compact it). I'm using a client/server arrangement so the client mde needs to compact the data mdb that sits on the server. Obviously, it will be allowed to fail if other users have the database open.

    Thanks,
    Stephen.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That would be a sticky proposition indeed. If you require daily compaction, I would suggest running an automated script to compact that database after hours, maybe every day at 2am or something.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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