Reserved error (Error 3000)

[AFSSkier]

Does anyone know what is causing this error?

Reserved error <Item>; there is no message for this error. (Error 3000)
An unexpected error occurred. The specified code identifies the conditions
under which this error can occur. Please contact Microsoft Product Support
Services for more information.

Here's SQL:
INSERT INTO tblSCANSTORELIST ( RETLUPC, [14-UPC], UrCl, Store, [Store Name],
[UC-Name], [Sales $], UnitSales, [Ur Cl], [Lf St], 52wk )
SELECT DISTINCT Format([CMAT_SCANSTORELIST]![14-UPC],"0000000000000") AS
RETLUPC, CMAT_SCANSTORELIST.[14-UPC], [STORES-SCAN].UrCl,
CMAT_SCANSTORELIST.Store, [STORES-SCAN].[Store Name],
[STORES-SCAN].[UC-Name], CMAT_SCANSTORELIST.[Sales $],
CMAT_SCANSTORELIST.UnitSales, [STORES-SCAN].[Ur Cl], [STORES-SCAN].[Lf St],
[STORES-SCAN].[52wk]
FROM CMAT_SCANSTORELIST LEFT JOIN [STORES-SCAN] ON CMAT_SCANSTORELIST.Store
= [STORES-SCAN].Store
ORDER BY CMAT_SCANSTORELIST.[14-UPC], [STORES-SCAN].UrCl,
CMAT_SCANSTORELIST.Store;

 

[Lord Kelvan]

for one you should try not to use special characaters or numbers in
field names as that can give you numerious problems

i think the problem is

Format([CMAT_SCANSTORELIST]![14-UPC],"0000000000000")

you have used a ! instead of a .

but that is a guess

 

[John Spencer]

Two things I see
-- 52Wk should probably be surrounded with square brackets
-- [CMAT_SCANSTORELIST]![14-UPC] should be [CMAT_SCANSTORELIST].[14-UPC]

Other than that
-- Check that RETLUPC is a text field since that is what you are assigning to
the field. And make sure the field will accept 14 characters or a zero-length
string (zls). If 14-UPC is null then the format expression will return a
zero-length string and if RETLUPC does not allow a zls, you should get an
error message.

All that said, I find that you are getting a Reserved error message curious as
usually you would be a more precise error for any of the suggestions I have made.


INSERT INTO tblSCANSTORELIST ( RETLUPC, [14-UPC], UrCl, Store, [Store Name],
[UC-Name], [Sales $], UnitSales, [Ur Cl], [Lf St], [52wk] )
SELECT DISTINCT
Format([CMAT_SCANSTORELIST].[14-UPC],"0000000000000") AS RETLUPC
, CMAT_SCANSTORELIST.[14-UPC]
, [STORES-SCAN].UrCl
, CMAT_SCANSTORELIST.Store
, [STORES-SCAN].[Store Name]
, [STORES-SCAN].[UC-Name]
, CMAT_SCANSTORELIST.[Sales $]
, CMAT_SCANSTORELIST.UnitSales
, [STORES-SCAN].[Ur Cl]
, [STORES-SCAN].[Lf St]
, [STORES-SCAN].[52wk]
FROM CMAT_SCANSTORELIST LEFT JOIN [STORES-SCAN]
ON CMAT_SCANSTORELIST.Store = [STORES-SCAN].Store
ORDER BY CMAT_SCANSTORELIST.[14-UPC]
, [STORES-SCAN].UrCl
, CMAT_SCANSTORELIST.Store;

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[AFSSkier]

John,

I pasted your suggested SQL into my query, now I get the following error:

Invalid argument. (Error 3001)
You tried to perform an operation that involves a routine in a DLL, and one
of the arguments to the routine is invalid. Check your entry to make sure you
have specified the correct arguments, and then try the operation again.
This error also occurs when you attempt to use mutually exclusive constants
in an argument to a method, such as specifying both dbConsistent and
dbInconsistent in the options argument to the OpenRecordset method.

The funny thing is, my original query was created & has been working ever
since 8/30/2007. Something is different, maybe in the flat file? This query
is in Access 2003 format.
--
Thanks, Kevin

Two things I see
-- 52Wk should probably be surrounded with square brackets
-- [CMAT_SCANSTORELIST]![14-UPC] should be [CMAT_SCANSTORELIST].[14-UPC]

Other than that
-- Check that RETLUPC is a text field since that is what you are assigning to
the field. And make sure the field will accept 14 characters or a zero-length
string (zls). If 14-UPC is null then the format expression will return a
zero-length string and if RETLUPC does not allow a zls, you should get an
error message.

All that said, I find that you are getting a Reserved error message curious as
usually you would be a more precise error for any of the suggestions I have made.


INSERT INTO tblSCANSTORELIST ( RETLUPC, [14-UPC], UrCl, Store, [Store Name],
[UC-Name], [Sales $], UnitSales, [Ur Cl], [Lf St], [52wk] )
SELECT DISTINCT
Format([CMAT_SCANSTORELIST].[14-UPC],"0000000000000") AS RETLUPC
, CMAT_SCANSTORELIST.[14-UPC]
, [STORES-SCAN].UrCl
, CMAT_SCANSTORELIST.Store
, [STORES-SCAN].[Store Name]
, [STORES-SCAN].[UC-Name]
, CMAT_SCANSTORELIST.[Sales $]
, CMAT_SCANSTORELIST.UnitSales
, [STORES-SCAN].[Ur Cl]
, [STORES-SCAN].[Lf St]
, [STORES-SCAN].[52wk]
FROM CMAT_SCANSTORELIST LEFT JOIN [STORES-SCAN]
ON CMAT_SCANSTORELIST.Store = [STORES-SCAN].Store
ORDER BY CMAT_SCANSTORELIST.[14-UPC]
, [STORES-SCAN].UrCl
, CMAT_SCANSTORELIST.Store;

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Does anyone know what is causing this error?

Reserved error <Item>; there is no message for this error. (Error 3000)
An unexpected error occurred. The specified code identifies the conditions
under which this error can occur. Please contact Microsoft Product Support
Services for more information.

Here's SQL:
INSERT INTO tblSCANSTORELIST ( RETLUPC, [14-UPC], UrCl, Store, [Store Name],
[UC-Name], [Sales $], UnitSales, [Ur Cl], [Lf St], 52wk )
SELECT DISTINCT Format([CMAT_SCANSTORELIST]![14-UPC],"0000000000000") AS
RETLUPC, CMAT_SCANSTORELIST.[14-UPC], [STORES-SCAN].UrCl,
CMAT_SCANSTORELIST.Store, [STORES-SCAN].[Store Name],
[STORES-SCAN].[UC-Name], CMAT_SCANSTORELIST.[Sales $],
CMAT_SCANSTORELIST.UnitSales, [STORES-SCAN].[Ur Cl], [STORES-SCAN].[Lf St],
[STORES-SCAN].[52wk]
FROM CMAT_SCANSTORELIST LEFT JOIN [STORES-SCAN] ON CMAT_SCANSTORELIST.Store
= [STORES-SCAN].Store
ORDER BY CMAT_SCANSTORELIST.[14-UPC], [STORES-SCAN].UrCl,
CMAT_SCANSTORELIST.Store;

 

[John Spencer]

Sorry. I have no idea what the problem is.

By the way, what flat file?

I guess it is possible that something has gotten corrupted in your database.

I would first try just copying the SQL into a new query and see if that works.
If so, then I would definitely suspect you have some corruption in the
database.

Try Allen Browne's article on Recovering from Corruption at:
http://allenbrowne.com/ser-47.html

Also you take a look at Tony Toews site
http://www.granite.ab.ca/access/corruptmdbs.htm

If you suspect the error is caused by changed data, try trimming the source
data down to a couple of records and see if the query runs. If so, you then
can suspect your data and examine it for problems.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County