[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Converting String to Number via Code VBA SQL Server MS Access

Posted on 2006-05-12
Medium Priority
Last Modified: 2008-02-01
I am scanning a barcode that reads into a textbox as follows..


I have a Sql Server where the value is being stored..

it is saved into the DB as

9090972, the field is a number datatype

Later on, I am scanning the barcode again to do some things, this value has to be compared against the earlier stored value in the DB (9090972)

I have been fooling around with the CInt function, to convert a string to a integer..but I am getting an overflow error.

How do you codewise convert  a string to a integer, in other words ...I want

09090972   -->  9090972

CInt function won't work..

Any help or insight would be much appreciated

Question by:nomar2
LVL 66

Accepted Solution

Jim Horn earned 80 total points
ID: 16669642
CLng(YourNumber) = 9090972, when YourNumber = 09090972  

The Access interger is +/- 32,767.  The Access Long Integer is equivalent to the SQL Server Integer.
LVL 44

Assisted Solution

GRayL earned 80 total points
ID: 16670387
If the barcode is 09090972, I don't think you should be saving it as a number which drops the leading zero(es).  That leading zero has significance and there may be serveral.  Once it is in the DB as a string, the next time it has to be compared to something, it is done directly, without conversion!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question