Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    This doesn't happen in all my databases but - in at least two

    when I select only some of the columns the datasheet view displays all columns.

    I've unchecked "output all fields' in Access Options > Object Designers, closed all instances of Access and restarted, but still the pain persists.

    I can't run an append because I get an error

    The INSERT INTO Statement contains Unknown field '300809'. Make sure you have typed the name correctly, and try the operation again.

    when I try to execute using 'Run'. The unkown field is a field in the FROM table but it's not in the SQL

    Code:
    INSERT INTO LandUse ( Supplier_No, Supplier_Name )
    SELECT LanduseImport.[Supplier No], LanduseImport.[Supplier Name], *
    FROM LanduseImport;
    what am I missing?

  2. #2
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Found it - there's an Output all fields property for the query. Where did that come from!
    New in 2007. Defaulted to the value of the Object designer setting but didn't change when I changed the setting.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by kentg View Post
    Found it - there's an Output all fields property for the query. Where did that come from!
    New in 2007. Defaulted to the value of the Object designer setting but didn't change when I changed the setting.
    I just looked back in 2003, and found that the same property and behaviour is there too.
    I can't imagine when you would want this.
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    No idea why anyone would ever want this.

    What would be good is a button to add all fields so that you could then delete just the ones you don't need.

    That would be useful when you are creating append queries with lots of fields.

    But to put out all fields regardless of the SQL seems a bit bizare!

  5. #5
    Lounger
    Join Date
    Jun 2010
    Location
    Manchester, NH
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Kentg,

    This comes from using the asterisk in the SELECT statement. The * in a SELECT statement indicates to "OutPut All Fields" and vice-versa, if you then also include the field names as well, you will get duplicate field names. Access will give the duplicate column/field a name of its choosing: For example, in your statement "SELECT LanduseImport.[Supplier No], LanduseImport.[Supplier Name], * FROM LanduseImport" both [Supplier no] and [Supplier Name] would be duplicated in the output. Remove the * and you should be fine.

    You said..."What would be good is a button to add all fields so that you could then delete just the ones you don't need."

    Tip: If you use the QBE Grid/designer, you can double click on the Titlebar of the Field list. That will select all of the fields in the field list and then you can easily drag them all down into the grid and then delete the ones you don't want.

    Hope that helps.

    Bob Oxford
    Bob Oxford
    Software Wizards, Inc.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Bob Oxford View Post
    This comes from using the asterisk in the SELECT statement. The * in a SELECT statement indicates to "OutPut All Fields"

    I think Kentg's observation is that the "Output all Fields" property is in the Properties box but not in Query Design. But it is true that if Output All Fields is set to true you do get an * added to the SQL. What I find is that if you leave out the table name in front of the * in the SQL Output All Fields is set to yes.
    So SELECT * FROM tblNurseInfo does it but SELECT tblNurseInfo.* FROM tblNurseInfo does not.

    The advantage of * is that it automatically adds any new fields to the query. I would like something that works like that, but means "Return all fields except...." so I could exclude some fields, but retain the capacity to have new field added automatically.
    Regards
    John



  7. #7
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Bob Oxford View Post
    Tip: If you use the QBE Grid/designer, you can double click on the Titlebar of the Field list. That will select all of the fields in the field list and then you can easily drag them all down into the grid and then delete the ones you don't want.
    Bob Oxford
    Here I've been a developer since version 1.0 and I just learned something new. Thank you.

    Also found that you can select the fields in the usual way (using shift and control keys) to select a range or non-consecutive fields and drag them down. Control/A works too! Cool!

Posting Permissions

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