Need function to convert string to Integer

jal

Registered User.
Local time
Yesterday, 16:47
Joined
Mar 30, 2007
Messages
1,709
I used DoCmd.TransferSpreadsheet to import a sheet into Access.

But the Acct number column wasn't getting read/transferred into Access.

So I changed the Access table Acct to type Text. Now it's getting read.


Now I want to copy this Acct column (text) to another column in the same table called Account (Long Integer).

In other words I am simply converting the column to another type.

I tried this:

sql = "UPDATE tblPosted Set Account = CInt(Acct)"


And it didn't work (i guess I'll have to turn warnings back on). "Didn't work" means that the Account column remained all null. Also tried Cdbl in addition to CInt. No luck.

A sample acct number is

980508121234
 
Did you try CLng yet? That function converts strings to LONG Integers. CInt only goes up to 32767.


Also, I think that CDbl is for real numbers, but I do not know the limit in scope
 
Last edited:
SELECT Acct FROM tblPosted works fine,

but covnersion doesn't work,

again, sample acct number is 960805019997


I checked my tables to insure dataype in the Destination column is long integer.
 
Last edited:
Just to make sure not a vba error I put this in the editor

SELECT CLng(Acct) FROM tblPosted

returns a column of "#Error"
 
I'm not sure exaclty what it is you're trying to do but I think you need to split the function and field name out if you're trying to build a sql statement. Something like:

sql = "UPDATE tblPosted Set Account = " & CInt(Acct)

???
 
SELECT CLng([Acct]) As NewFieldName FROM tblPosted
 
I'm not sure exaclty what it is you're trying to do but I think you need to split the function and field name out if you're trying to build a sql statement. Something like:

sql = "UPDATE tblPosted Set Account = " & CInt(Acct)

???

I'll try that - I'm a newbie, and I got the impression that most functions work without "separation". For example the full query uses the instring function with no "separation" works fine.
 
You may be fine - It just kind of looked to me like you building a sql string to use later in your code. ?
 
I'm not sure exaclty what it is you're trying to do but I think you need to split the function and field name out if you're trying to build a sql statement. Something like:

sql = "UPDATE tblPosted Set Account = " & CInt(Acct)

???

This is maddening. Here's the full query as it actually looks - all of it works fine except for the Clng part at the end still not working:

sql = "UPDATE t835 SET FirstName = Trim(Right(Name, Len(Name) - instr(Name, ' '))), " _
& "LastName = Trim(Left(Name, instr(Name, ' ') - 1)), Account = " & CLng(Acct)
 
I'm not sure exaclty what it is you're trying to do but I think you need to split the function and field name out if you're trying to build a sql statement. Something like:

sql = "UPDATE tblPosted Set Account = " & CInt(Acct)

???

I've now tried it both with and without separation.

When I try it with separation, I get a runtime error.

When I try it witout separation, the conversion fails. I am more inclined to favor this no-separation based on my (minimal) experience with Access. Don't know why it's not working.
 
There could be several thing going on here. My suggestion is to get all the conversion stuff working in a simple select query first. Then it will be easy to make it an update query.
 
There could be several thing going on here. My suggestion is to get all the conversion stuff working in a simple select query first. Then it will be easy to make it an update query.
Great suggestion, but that's exactly what I was talking about above when I pasted suff like this into sql view:

SELECT Clng(Acct) FROM tblPosted

Returns a column of "#Error"
 
There could be several thing going on here. My suggestion is to get all the conversion stuff working in a simple select query first. Then it will be easy to make it an update query.

Well, your suggestion helped after all. I thought I had made it as basic as possible, but after reading what you said, I reduced it to this:

SELECT Clng("960805019997")

The result is a MsgBox("Overflow")


I am shocked. I guess I thought it could handle bigger numbers than that.
 
sql view is not a place where an inexperienced sql coder needs to be tinkering around, unless you ARE trying to learn sql. :)

I recomend you try to figure out how to put the 'Acct' thing in using the QBE grid. Then when you get it working there you can examine the sql view to see how it needs to be coded.
 
Okay, went back to CDbl and finally got it working. Don't know why I had trouble with Cdbl the first time. Here is the code:

sql = "UPDATE Posted SET FirstName = Trim(Right(Name, Len(Name) - instr(Name, ',') -1)), " _
& "LastName = Trim(Left(Name, InStr(Name, ',') - 1)), Account = CDbl([Acct])"
 

Users who are viewing this thread

Back
Top Bottom