MS Access Query - How to convert nvarchar to int

Amour22015
Amour22015 used Ask the Experts™
on
Hi experts,

I have a column
InvoiceNr it is a nvarchar data type

I am converting this into a int column

so something like:
CInt([InvoiceNR]) but that is for VBA

I am looking to do this using a Query

Please help and thanks...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Scott McDaniel (Former Microsoft Access MVP - EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
CINT works in a query ...
Amour22015

Author

Commented:
But when I use that I get:
#Num!

Please help and thanks...
CIO
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Try using Val():

    Val([InvoiceNR]) As InvoiceNo

or perhaps:

    Val(Nz([InvoiceNR])) As InvoiceNo

/gustav
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Mike EghtebasDatabase and Application Developer

Commented:
Try:

cint(Nz(InvoiceNr))

or perhaps:

IIF(IsNumeric(InvoiceNr), Cint(InvoiceNr), '')
Amour22015

Author

Commented:
That works for this query thanks
Gustav BrockCIO
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial