By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,972 Members | 1,134 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,972 IT Pros & Developers. It's quick & easy.

Erroe during move from one table to another

P: n/a
When i run my query it transfer last 4 digits of account number from
one table to another and its wrong. There are two tables one i use for
parsing. Second thru query i use to move data from temp table to actual
table with some little changes in temp table. I use the same code in
two different databases One database works fine and another database
wont move the account number with 9 digit size. Difference is only the
structre of text files so parsing is little different. But after
parsing same method i use to move from temp table to actual table. when
it parse it move the account number from temp to actual table with this
function: AccountNo: Right(Trim([AccountNum]),9) but it shows only last
4 digit of account number instead of last 9 digit (75-02) and that is
wrong. I dont know why its doing that becuase same method in another
database works fine. Length of account number in temp
table(tblcustomers is 100 of tpe TEXT). Length of account number in
another table is 10 type TEXT input masrk AAAAAA\-AA. It moves fine in
tblcustomer but it wont move correct in another table which have an
input mask

[Account Number]
07836-105575-02

Thanks

Aug 12 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
did you try making the field size bigger?

"when I try to store the string in this field, it fails"

That will only happen if:
1. the value is of the wrong type (like trying to put text in a numeric
field)
2. violates referential integrity constraints
3. the value is too large.

Aug 12 '06 #2

P: n/a
After parsing i move the values form tblcustomer to tblRequest. There
are too many spaces in all the field of tblcustomer but they all move
good accept Account number.
Below is the query which i use to move values but it still give me last
4 digit from tblcustomers.

tblcustomers
AccountNum Text 100
Value: 07836-105575-02
Input Mask: CCCCCC\-CC

tblRequest
AcctCustNum Text 15
Value: 75-02

INSERT INTO tblRequest ( AcctCustNum, CableDataDate, CreditRequested,
ZipCode, CorpNum, BoxType, Comments, CustFName, CustLName, NewRequest,
BoxQty, ReturnMethod, DateLoaded, ConverterNum, RequestRecd,
RequestRecdDtl, RequestDate, RequestStatus, ErrorType, RequestType,
SenderInitials, SenderCorp, CableDataID ) SELECT
Right(Trim([AccountNum]),9) AS AccountNo, tblCustomers.ReturnDate,
tblCustomers.CreditAmount,
Right(Trim([CityStateZip]),Len(Trim([CityStateZip]))-InStr(1,[CityStateZip],",
")) AS Zip, Left([AccountNum],5) AS Corp,
tblCustomers.BoxType, tblCustomers.Comments,
Left([Name],InStr(1,[Name]," ")-1) AS Expr,
Right(Trim([Name]),Len(Trim([Name]))-InStr(1,[Name]," ")) AS Expr2,
"Yes" AS NewWork, tblCustomers.BoxQty, tblCustomers.ReturnMethod,
blCustomers.ImportDate, tblCustomers.ConverterNumbers, "E Mail" AS
RecdHow, "KDB" AS RecdDetail, Left([RequestDate],10) AS ReqDate, "Open"
AS ReqStat, "Open" AS ErrTyp, tblCustomers.Subject,
Left([Sender],InStr(1,[Sender]," (")-1) AS SenderInits,
Right([Sender],4) AS SenderCorp, Left((Right([Sender],8)),3) AS CDID
FROM tblCustomers;

Jerry Whittle wrote:
Without seeing the data, I'm betting that it's the A in the imput mask. "A"
means that a letter or digit is required. It won't help if there are spaces.
Try a "C" instead which will let in any character or a space plus entry is
optional.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
"Eric" wrote:
When i run my query it transfer last 4 digits of account number from
one table to another and its wrong. There are two tables one i use for
parsing. Second thru query i use to move data from temp table to actual
table with some little changes in temp table. I use the same code in
two different databases One database works fine and another database
wont move the account number with 9 digit size. Difference is only the
structre of text files so parsing is little different. But after
parsing same method i use to move from temp table to actual table. when
it parse it move the account number from temp to actual table with this
function: AccountNo: Right(Trim([AccountNum]),9) but it shows only last
4 digit of account number instead of last 9 digit (75-02) and that is
wrong. I dont know why its doing that becuase same method in another
database works fine. Length of account number in temp
table(tblcustomers is 100 of tpe TEXT). Length of account number in
another table is 10 type TEXT input masrk AAAAAA\-AA. It moves fine in
tblcustomer but it wont move correct in another table which have an
input mask

[Account Number]
07836-105575-02

Thanks
pi********@hotmail.com wrote:
did you try making the field size bigger?

"when I try to store the string in this field, it fails"

That will only happen if:
1. the value is of the wrong type (like trying to put text in a numeric
field)
2. violates referential integrity constraints
3. the value is too large.
Aug 12 '06 #3

P: n/a
It moves fine in
tblcustomer but it wont move correct in another table which have an
input mask

Again, how large is your field that you're trying to append to?
For grins, try increasing the size. Are you saving the data with the
input mask? If so, that's what could be taking up the extra spaces.
Either set the inputmask not to be stored or make the field bigger.

Aug 13 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.