MS Access Query - Overflow

What are the values in the column? Perhaps you have values that are too big to be represented as numerics in access?

How can you tell which are numbers and which are not?

The field I am converting from is InvoiceNr(nvarchar(20), null)

Like:

123456

Vs

12345.6

Please help and thanks...

Try adding this to your SELECT statement

Where IsNumeric(InvoiceNr) = 1

Curiously though... You say MS ACCESS and yet your question references SQL Server data types nvarchar. Knowing exactly which is which helps us know exactly what were working with and how to help

I am using MS Access to run the conversion query.

I see nvarchar in the ssms window.

Data gets imported into ssms

I use External Data> ODBC Database>Import the source data into a new table int the currnt database>Machine Data Source>Import Objects

To get the table from ssms to access.

So I am running the query in access to Insert into the new tables...

I ran this query (in TSQL) to find all none numeric:

SELECT [txtInvoiceNo]
  FROM [SIOP_siophal].[dbo].[tblAccounting]
  Where txtInvoiceNo like '%[^0-9]%'

Select allOpen in new window

But have filtered them out yet still get the same error...


The view works fine, it happens when I run.



Please help and thanks...

SELECT  CAST( [txtInvoiceNo] as int) NewInvoiceNr
  FROM [SIOP_siophal].[dbo].[tblAccounting]
  Where txtInvoiceNo like '%[^0-9]%'
ORDER BY NewInvoiceNr DESC

Does that work in SSMS? What is the biggest value you see there?

So I got this:
Msg 248, Level 16, State 1, Line 1
The conversion of the nvarchar value '3165101084' overflowed an int column.

What does this mean?

Please help and thanks

Thanks for helping... Great

Glad to help.