Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Insert Into' SQL Error (Access 2003)

    I am constructing a simple append query. Please see attached jpg.

    It simply appends fields from one table to another. I am getting an error message stating "The INSERT INTO statement contains the following unknown field name: 'dtShift-Date'. Make sure you have typed the name correctly and try the operation again".

    Is this yet another "undocumented feature"? I don't "type" a field name -- I select it from a drop down list of fields in the target table. It's not recognizing a field name that was presented in a drop down list.

    I should add several facts: (1) When I restrict the query to append only the field before the offending field, the query works. It is when I add dtShift-Date that the problem occurs. (2) I did a search in the MS Knowledgebase and came up empty.

    Any ideas? TIA.
    Attached Images Attached Images
    Carol W.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'Insert Into' SQL Error (Access 2003)

    I thought that perhaps the use of spaces, minus sign and the reserved word Date in the field name might cause a problem, but that is not the case. I created two tables with the field names from your (hard to read) screenshot, and created a query as shown. It worked without a hitch (in Access 2002).

    There is one thing that I noticed: the screenshot shows 'dtShift - Date' (with spaces), as far as I can see, but you mention 'dtShift-Date' (without spaces) twice in your post.

    Could you post the SQL of your query?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Insert Into' SQL Error (Access 2003)

    Sorry about the size of the screen shot. First it was too big, then too small!

    Following is the generated sql:

    INSERT INTO [tblPlacementShift Dates] ( [intShift - Member Num], [dtShift-Date] )
    SELECT [Old Placement System-Shift Dates].[Shift - Member Num], [Old Placement System-Shift Dates].[Shift - Date]
    FROM [Old Placement System-Shift Dates];
    Carol W.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Insert Into' SQL Error (Access 2003)

    Problem solved! It is indeed an "undocumented feature"!

    The actual field name is dtShift - Date (with spaces around the hyphen). However, when the query is opened in design mode, that field is presented without spaces -- not a valid field in the table. When the field is re-selected from the dropdown list, the spaces reappear (of course) and the query executes properly.

    The "undocumented feature", as I see it, seems to be in the displaying of the field name. Spaces are being suppressed.

    Thanks, Hans, for pointing me in the right direction.
    Carol W.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Insert Into' SQL Error (Access 2003)

    Postscript to last post: The "undocumented feature" seems to occur when the query is closed. The field names are fine but when the query is closed and then reopened in design view, the spaces have been suppressed.
    Carol W.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'Insert Into' SQL Error (Access 2003)

    I see that too - I hadn't tried closing the query and reopening it. It is definitely a bug, and it is mentioned a few times in the newsgroups, but it only reinforces the general recommendation: don't use spaces and character like - in the names of fields and database objects.

    Added: The error is also mentioned in an old MSKB article for Access 1, 2 and 95: ACC: Query on Table with Space-Hyphen-Space Table Name Fails. It is stated there that it no longer occurs in Access 97, but apparently it does occur in Access 2002 and 2003.

Posting Permissions

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