Invalid Argument

[JJOHNS]

I was asked to add a report to a database that counts records by type. Simple, right? Here's the problem (I think): There are about 20 users who add about 10 records each per day to this database. When I was asked to create my report, it had over 28,000 records in it. When I tried to count the types, I kept getting "invalid argument." I cut the data down to 18,000 records and still kept getting the error. It gets worse. The table isn't even in my database. It's linked from another database and I can't get into it. My solution was to create a blank copy of the table and start using that. I'm thinking I'll create 12 copies of the table and switch at the beginning of each month. That works OK, but I still can't get access to the data from the first three months of this year. Does anyone know a better way? Or at least a way to get that data out of the original table? I can open the linked table, but I can't copy the data.

 

[kewo99]

Can you create a Make Table query using the linked table as the source?

Ken

 

[UrbaneRove]

JJOHNS

if you can link to the data try in your database window to import the data. This would create a local copy of the data and then you can work with it. Alternatively run a make table query on the data. Third if you can read the data using VBA read the record into varialbes and then output the variable to a new table.

Hope it helps

Urbane Rove.

 

[AvGuy]

I think it depends on why you're getting the "invalid argument" message. What it sounds like you need is a query that counts the types. (Certain VBA methods, such as SEEK, don't work on linked tables which may be the source of your error.) So a query approach seems to be indicated.

Provided you can get a query to do just that then you can base the report on the query and spend the afternoon sipping suds. Unfortunately, you didn't give enough information to be more specific than this general approach suggestion, but that's what I would try because queries painlessly handle this sort of processing.

 

[JJOHNS]

My first attempt was a simple query to get the data out of the table based on a date range. Then I tried to use a report to sort by category and give a count of each category. When I tried to run the query, I would get the Invalid Argument error, but the data would show anyway. However, when I tried to open the report, I only got the Invalid Argument.

I made another, identical table and tried to create an append query to append all data to it. I got the error.

I tried to copy the table into a new table. That worked, but I only ended up with another linked table. No help.

I tried using a make table query but got the error.

I'm really thinking that something has happened to corrupt some of the data in the original table, maybe because it was set up without a primary key.

I tried exporting the data into Excel, but was only able to export one record at a time. Not good for 18,000 records. However, if I can do that, it will be a simple task to import it back into Access into a new table. Does anyone have any ideas on how to do that?

 

[JoyInOK]

Have you checked for null values and invalid dates in the field you're specifying criteria for? You may have a stray entry or two that is giving you trouble. Try sorting the field ascending in the table and checking the first and last records listed.
Even in a linked table, you can probably fix these. If not, you may have to eliminate these from your query results.
MyExpression: Iif(isDate([MyFieldDate]), [MyFieldDate],"&quot