473,748 Members | 10,539 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Erroe during move from one table to another

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(tblcustom ers 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
3 1884
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
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.Re turnDate,
tblCustomers.Cr editAmount,
Right(Trim([CityStateZip]),Len(Trim([CityStateZip]))-InStr(1,[CityStateZip],",
")) AS Zip, Left([AccountNum],5) AS Corp,
tblCustomers.Bo xType, tblCustomers.Co mments,
Left([Name],InStr(1,[Name]," ")-1) AS Expr,
Right(Trim([Name]),Len(Trim([Name]))-InStr(1,[Name]," ")) AS Expr2,
"Yes" AS NewWork, tblCustomers.Bo xQty, tblCustomers.Re turnMethod,
blCustomers.Imp ortDate, tblCustomers.Co nverterNumbers, "E Mail" AS
RecdHow, "KDB" AS RecdDetail, Left([RequestDate],10) AS ReqDate, "Open"
AS ReqStat, "Open" AS ErrTyp, tblCustomers.Su bject,
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(tblcustom ers 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********@hotm ail.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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
3763
by: Wolfgang Kaml | last post by:
Hello All, I have been working on this for almost a week now and I haven't anything up my sleeves anymore that I could test in addition or change.... Since I am not sure, if this is a Windows 2003 Server or ADO or ODBC issue, I am posting this on all of the three newsgroups. That's the setup: Windows 2003 Server with IIS and ASP.NET actiavted Access 2002 mdb file (and yes, proper rights are set on TMP paths and path,
4
4378
by: Xela | last post by:
Hi I am facing the following problem. I load a fact table with around 25 millons lines, and 7 indexes. I load it with 3 million line subsets. I am on a quadriprocessor Solaris machine. The loading phase seems quite efficient and seems to be well parallelized. But the build phase is long and seems to use only one processor. Very low IO wait occurs during this phase. Is there something can I do to fasten this process? I guess that if the 4...
3
1681
by: Punisher | last post by:
I have a datagrid that I want to move another part of the page. I'm able to do this but the datagrid looses it's values after the move. General_DataGrid_CurrentItems is the datagrid name. I want to move it to a tablecell called tdControls. this.Controls.Remove(General_DataGrid_CurrentItems); tdControls.Controls.Add(General_DataGrid_CurrentItems); This works. It moves the datagrid from the bottom of the page to the middle
2
1704
by: Newbie | last post by:
Hello! I'm trying to figure out how to do the following: I need to show a table (perhaps using DataGrids, repeaters or others) for each salesperson in the sales dept. But the no. of tables to be shown is to be determined during runtime. It's possible that some salespersons have no sales figures to begin with and therefore no table to show. Thanks in advance and please help!
7
3104
by: tshad | last post by:
Is there a way to move a row in a Datalist up or down without having to re-read the data? I have a datalist which has embedded Datagrids in it. I want to allow the user to move a row up or down by just pushing a button and have the page post back with the row moved without actually re-reading the data from the database? Thanks,
4
1799
by: **Developer** | last post by:
I have a Listbox application. I allow users to drag items from one spot in the listbox to another spot. I also allow them to drag to, say, WordPad In the DoDragDrop I use DragDropEffects.Copy so that when dragging to WordPad the copy cursor will show. (If I use "DragDropEffects.Move Or DragDropEffects.Copy" WordPad show the move cursor.) But when the user moves an item from one spot to another in the listbox I'd
9
24677
by: Joshua.Buss | last post by:
I am trying to move a record from one linked table to another within access, but I'm a complete beginner to VBA and don't know exactly where to begin. I have an access file that has the two linked tables, which each link to separate SQL databases using separate ODBC connections. I have a form that has a view of table1 and a set of buttons for manipulating the data in database1.
4
1841
by: WillMiller | last post by:
Hi, I'm currently attempting to write a small Access Database which carries out the following tasks : 1. Imports Submission files (of a text variety) to a table using TransferText on a daily basis (many files into 1 table) 2. Imports Reject Files files (again of a text variety) to another table using Transfer Text. (again, daily, many files into 1 table) 3. Run a query which reports all submissions that haven't had a matching...
5
4299
by: trixb | last post by:
Hello all, Here is what I need to do and need help with: I have a table that is feeding a chart in a report in Access. If this table has more than 50 records, the graph gets messy, and this is where I need help with my solution to this. If the table has more than 50 records, I want to take the next 50 records after the 50th and move these to another table (which I need to create), and this will continue until there are no more records. I...
0
8991
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9374
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9249
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8244
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6796
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4876
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3315
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2787
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2215
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.