SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot toograffic's Avatar
    Join Date
    Mar 2002
    Location
    Sarasota, Florida
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    INSERT INTO syntax, wildcard

    Couldn't find an answer by doing a search, so I'll ask my question:

    I want to take data from an Access query (NewFolks) and put it into a table (New_tbl). The query (NewFolks) pulls from an externally linked, tab delimited .txt file that is updated by uploading a new .txt file into the folder. The problem arise when someone doesn't have a value for "address2" in their information - I get the following error "The Microsoft Jet database engine does not recognize '[address2]' as a valid field name or expression."

    Here is what I have used as an SQL statement:

    INSERT INTO New_tbl (id,lastname,firstname,address1,address2)

    SELECT id,lastname, firstname, address1, address2 FROM NewFolks

    Is there a way to use a wildcard to only select and insert the data that is available?

    Any suggestions would be appreciated!
    The two most common elements in the universe are hydrogen
    and stupidity. - Harlan Ellison
    Retirement Communities and Senior Housing Options
    Freecycle.org

  2. #2
    Jamison DaDaHost's Avatar
    Join Date
    Sep 2002
    Location
    Florida, USA
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you make the missing address2 into a double tab? This should be interperted as an empty string.

    Tab delimited file:
    id,lastname, firstname, address1, address2
    Data:
    5[tab]smith[tab]john[tab]123 Main Street[tab][tab]

    Jamie

  3. #3
    SitePoint Zealot toograffic's Avatar
    Join Date
    Mar 2002
    Location
    Sarasota, Florida
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I wish I could!

    Unfortunately, I do not have control over the .txt files (there are two, and I combine them in an Access query). I simplified it for the the example - my actual SQL statement has about 30 fields it is working with, and 21 of those may or may not be used, and I have no control over them. I have to make it work no matter which fields the .txt files contains.


Bookmarks

Posting Permissions

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