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.