Error running Make table Query

[ielamrani]

Hi,
I am trying to run a make table query and it's giving me the following error:
"Numeric Field Overflow"

Here the query:

SELECT tblPCRAFees.[Plan ID],
       tblPCRAFees.[Plan Name],
       tblPCRAFees.[Part# Count],
       IIf([Plan ID]="SSSG" Or [Plan ID]="QUADA" Or [Plan ID]="169","50","35.70") AS Charge,
       [Part# Count]*[Charge] AS Fees,
       IIf([Plan ID]="UNSAPS" Or [Plan ID]="AAM" Or [Plan ID]="ROPC" Or [Plan ID]="North*","Invoice","Plan Assets") AS Method
INTO tblPCRAFeesFinal
FROM tblPCRAFees
WHERE (((tblPCRAFees.[Investment ID])="FSIX"
   Or (tblPCRAFees.[Investment ID])="Fidelity"
   Or (tblPCRAFees.[Investment ID])="Brokerage"
   Or (tblPCRAFees.[Investment ID])="PCRA"));

Thanks in advance

[ielamrani]

Problem sovled. Thanks

Solution:

"Numeric Field Overflow" error message occurs when you query a table that is linked to Excel spreadsheet

Access assigns a data type for each field of the Excel spreadsheet. This assignment is based on the data that is contained in the first eight rows. For example, if a field has a Number data type that is in the first eight rows and then has text values in some of the remaining rows, Access assigns the Number data type to the link table field. This causes Access to fail to link the records that have text data values. When you query this field, Microsoft Jet Database Engine encounters text where a number is expected. The query fails with the error message in the "Symptoms" section.



WORKAROUND
To work around this problem, you must make sure that the data values in each field of the source Excel spreadsheet are exactly the same data type. Or, if the fields of the Excel spreadsheet contains data values of a mixed data type, then format the field as Text. Use the following code to reenter the values in the cells. Then, link the Excel spreadsheet to Access. To do this, follow these steps:

Note Before you start these steps, you must backup your Excel spreadsheet. 1. Open the source spreadsheet in Excel.
2. In the spreadsheet, identify the fields that have data values of mixed data types.
3. Select the identified columns and then format the cells in the spreadsheet as Text.
4. Create a macro in Excel that contains the following procedure:Sub Addspace() Dim cell As Object For Each cell In Selection cell.Value = " " & cell.Value cell.Value = Right(cell.Value, Len(cell.Value) - 1) NextEnd Sub


5. Select the fields in the spreadsheet that contain data values of mixed data types. Run the macro that you created in step 4.

Note If an error occurs when you run the macro on the problem cells, ignore the error.
6. Save the spreadsheet.
7. Open your database in Access.
8. From your database, link to the source spreadsheet in Excel.

[Rabbit]

I'm glad you solved your problem but next time you should let us know you were running a query on an excel spreadsheet.