Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I created a simple Select Query that separates a single Zip+4 column into two separate fields (and then displays the other fields in the table:
    SELECT [Tbl Zip4_byLocation].LOCN_NBR, Left([ZIP],5) AS ZipCode, Right([ZIP],4) AS Zip4, [Tbl Zip4_byLocation].CountOfADDR1, [Tbl Zip4_byLocation].SumOfSumOfRX_SALES, [Tbl Zip4_byLocation].SumOfSumOfRX_COUNT
    FROM [Tbl Zip4_byLocation];

    When I convert it to a Make Table:
    SELECT [Tbl Zip4_byLocation].LOCN_NBR, Left([ZIP],5) AS ZipCode, Right([ZIP],4) AS Zip4, [Tbl Zip4_byLocation].CountOfADDR1, [Tbl Zip4_byLocation].SumOfSumOfRX_SALES, [Tbl Zip4_byLocation].SumOfSumOfRX_COUNT INTO NewTable
    FROM [Tbl Zip4_byLocation];

    I get an Invalid Argument error box.

    NOTE: the table has 13+M records.

    Why?
    There is always a way.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    One possibility is that there's an invalid value somewhere in the source table - hard to find with that many records.

    Does it work if you save the select query, then create a make-table query based on the select query instead of the table?

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I expect that Hans is correct, and you have an alpha variable somewhere - a Canadian address perhaps - try changing it to an append query and explicitly set the data type to text rather than numeric. When you do a make table query Access looks the first 100 or so records and sets the data type accordingly.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788858' date='13-Aug-2009 20:30']Does it work if you save the select query, then create a make-table query based on the select query instead of the table?[/quote]

    Thanks for this suggestion because now I know that I cannot even SAVE the Select Query. I get the same error when I try to save.

    I thought that it needed a PARAMETER statement but I cannot remember how to write one. Any thoughts?
    There is always a way.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You'd need to declare a parameter if the query prompts for a value or if it refers to a control on a form. As far as I can tell, neither applies here, so there are no parameters to declare.

    Can you save the select query if you remove one of the calculated fields, or both?

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788956' date='14-Aug-2009 12:29']Can you save the select query if you remove one of the calculated fields, or both?[/quote]


    NO. Even as a straight query of the main table I cannot save without the error.
    for that matter... I cannot even copy and paste the main table without the argument.

    Ironically, I got a smaller table (9.7M records) from my client before this and it went fine. They delivered this table to replace that one and here we are.

    Interesting.

    I am going to try to start over and import the table again.
    There is always a way.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Good luck!

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='delucmi' post='788967' date='14-Aug-2009 13:21']I am going to try to start over and import the table again.[/quote]

    NOW I cannot even import the table again without the error... I am at a loss for sure.
    There is always a way.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Have you tried importing the table into an empty database, and then linking to it? With that many records, you may be getting close to the maximum size of an Access database
    Wendell

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Sounds like your database has either become too large - can you compact it? - or corrupt.

  11. #11
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788973' date='14-Aug-2009 14:37']Sounds like your database has either become too large - can you compact it? - or corrupt.[/quote]

    I feel obligated to fill you in on the end results and while you were adding the avove post I was officially starting over. You must have been onto something here.

    I started a NEW Db, Imported the data all over again, Created the QUERY (exactly as above) and created the new table... ALL WITHOUT A SINGLE ERROR.

    Thanks for your help - The original Db uncompacted was 2GB, the new clean one is 1.6GB. This must have been the result of a huge amount of data hurting Access' ability to work.

    Thanks again!
    There is always a way.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    2 GB is the maximum size for an Access database, so if it's that large it doesn't have any spare room. This will cause many operations to fail. Take care to keep the database well below 2 GB in size.

Posting Permissions

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