String Truncation Problem

Nov 15, 2006

I already posted this as a response to another thread, but thought I should post it as a new one in case people don't follow threads...

I need to construct a SQL statement to insert a row. The VB code used to construct the insert statement is very long (the actual insert statement will be much shorter). For some reason that I do not understand, it is truncated after 255 bytes.

My VB code is:

Dim InsertStatement As String

InsertStatement = "INSERT INTO tCoverage (CompanyCd, ProvinceCd, " & _
"LOBCd, ProductID, CoverageCd, NBSStartDt, WIPVersion, " & _
"RWLStartDt, ScreenID, WithoutCreditChargeInd, " & _
"GrandfatheredInd, OverrideModeInd, EndorsementTypeCd, " & _
"NoteText, AvailabiltyOfOPCFsTxt, LimitDedTypeCd, LimitDedGroupCd) " & _
"VALUES ('" & Forms!fCoverageDetail!CompanyCd & "', " & _
Forms!fCoverageDetail!ProvinceCd & "', " & _
Forms!fCoverageDetail!LOBCd & "', " & _
Forms!fCoverageDetail!ProductID & "', " & _
Forms!fNewVersionDates!newNBSStartDt & ", 0, " & _
Forms!fNewVersionDates!newRWLStartDt & ", " & _
Forms!fCoverageDetail!ScreenID & ", " & _
Forms!fCoverageDetail!WithoutCreditChargeInd & ", " & _
Forms!fCoverageDetail!GrandfatheredInd & ", " & _
Forms!fCoverageDetail!OverrideModeInd & ", " & _
Forms!fCoverageDetail!EndorsementTypeCd & ", " & _
Forms!fCoverageDetail!NoteText & ", " & _
Forms!fCoverageDetail!AvailabiltyOfOPCFsTxt & ", " & _
Forms!fCoverageDetail!LimitDedTypeCd & ", " & _
Forms!fCoverageDetail!LimitDedGroupCd & ")"

When I look at the InsertStatement string in debug, it looks like the following:

"INSERT INTO tCoverage (CompanyCd, ProvinceCd, LOBCd, ProductID, CoverageCd, NBSStartDt, WIPVersion, RWLStartDt, ScreenID, WithoutCreditChargeInd, GrandfatheredInd, OverrideModeInd, EndorsementTypeCd, NoteText, AvailabiltyOfOPCFsTxt, LimitDedTypeCd, Limi"

It is truncated after byte 255. Does anybody know why this is occurring?

View Replies


ADVERTISEMENT

Truncation In Report That Uses Sub-reports

Jul 20, 2007

Hello,

I created a Microsoft Access database that has a data entry form. This “parent” form has two sub-forms. Each sub-form can have many records that map to the same parent record.

I created VBA code to perform a dynamic query based on the fields I select and their associated values (e.g. query by form). The VBA code creates an SQL string that I use to run a query and present the results. I present the results via a report that looks just like my data entry form. The report includes two sub-reports (one for each of the sub-forms used in my data entry form).

On the main page of my report, there is a field called Gen_Comments. This field is of type “memo”. I see that the data in this field is truncated after 255 characters. This is not the case for any of the fields in my two sub-reports. My SQL statement uses the keyword DISTINCT. (Please refer to the SQL statement below). I’m pretty sure that this is what is truncating my memo field because if I remove this keyword and do a query, I do not get the truncation. However, if I remove this keyword, I then get duplicate records in my query.

HELP!! Can anyone determine if there is something wrong with my SQL statement below (which uses two nested inner joins)? Or, is there something else that I can do in conjunction with the DISTINCT keyword to eliminate the truncation? Any help would be GREATLY appreciated.

SELECT DISTINCT [PR_TABLE].*,SubTbl_DefectClassification.DefectNum,SubTbl_Ac tionItems.StepID FROM (([PR_TABLE] INNER JOIN SubTbl_DefectClassification ON PR_TABLE.ID = SubTbl_DefectClassification.DefectNum) INNER JOIN SubTbl_ActionItems ON PR_TABLE.ID = SubTbl_ActionItems.StepID) WHERE (((InStr(1,[PR_Table].PR_Gen_Comments,"The Wavecom",1))<> "0") AND (([SubTbl_DefectClassification].SevLevel) = "Minor") AND ((InStr(1,[SubTbl_ActionItems].Description,"Just a test",1))<> "0"))ORDER BY [PR_Table].ID

I sincerely thank you, in advance, for any help that you may be able to provide.

-Al Oberneder
International Truck and Engine Corporation

View 14 Replies View Related

Data Truncation Error. My Favorite.

Aug 7, 2006

I just linked a table to an Access Database from SQL Server. Now, whenever I open the linked table, I receive the error "Scaling of decimal value resulted in data truncation."

After a quick Google search (and search of these forums), I found some solutions:

what this means is that your number / data had a whole load of numbers after the decimal point, the length (width) of the field was not enough to display them all so some of the end was got lost

Increase the field size, or, limit the size of the number via the table, in design view, FIELD SIZE

It would appear that somewhere along the way you are
implicitly converting from a decimal value that has high
precision to an integer or other type value that has low
precision. During the conversion their is some data loss
in the form of lost precision.

So, I guess I found the problem, right? I just don't know what I'm suppose to do to correct the issue. There is not a single field in my SQL database that has a precision beyond 10 and a scale beyond 2. So...erm...Why can't Access just deal? What am I doing wrong?

~Andrew

View 1 Replies View Related

General :: Scaling Of Decimal Value Resulted In Truncation?

Mar 22, 2013

I'm using Access 2003 to create some reports for an insurance company I work for by interrogating the Informix database of our policy maintenance software via an ODBC connection. The linked tables and the fields included within cannot be edited due to permission restrictions (the software is provided by a third party) but this doesn't usually cause issues.

However, I've recently created a Query that extracts a *number* field from one of our database tables, but I'm having trouble due to the 'Scale' attributed to this field in the Access table settings.

Access recognizes that the field is a 'Decimal' type, but attributes a 'scale' of '0' which causes an error. 'Scale' (for those who aren't aware) refers to the maximum number of digits that can appear to the right of a decimal separator, meaning whenever this value exceeds 0 (e.g. if an advisor enters something like 240.51 - a 'Scale' of '2'), the 'Scaling of decimal value resulted in truncation' error occurs.

As mentioned earlier, we don't have the ability to amend the Scale attributed to this value due to permission restrictions, although I'm told that 'Scale' is set in Access and not on the external database anyway.

how to bypass this issue? I can't reformat the field as Access attributes the Scale upon the initial linking of the table, long before the field is used in any queries. Permission restrictions also prevent me from amending the 'Scale' value in the linked table settings.

The software house themselves can't assist as they've set this value to hold multiple decimals on the actual database. It's as if Access only checks a small proportion of the fields held in the table (the first few hundred of this particular field are whole numbers) and attributes the scale based on this.

View 4 Replies View Related

Queries :: Truncation Error While Importing Data In Access 2007

Mar 4, 2015

I am getting "Truncation Error" while importing data from Excel to access 2007. I have tried changing the format to short text but the result is the same.

View 3 Replies View Related

Linked Table Manager File Path View Truncation Problem

Sep 30, 2005

How do you get to see the entire file path in Access Linked Table Manager of linked tables that are located in a folder with a long path? The problem is that you can only view the first 64 or so characters of the table’s path. This is a real problem when these linked tables need to be updated and you don't know where it’s stored.

Jean-Guy

View 3 Replies View Related

Modules & VBA :: Separate Numbers And String From Alpha-numeric String

Jun 7, 2013

MS-Access VBA code to separate numbers and string from an alphanumeric string.

Example:

Source: 598790abcdef2T
Output Required: 598790

Source: 5789065432abcdefghijklT
Output Required: 5789065432

View 13 Replies View Related

String Validation (string Must Start With Http://)

Mar 12, 2007

Hi all,

I was looking for some help. I am trying to setup a table with a field for web address. People are entering www.website.com etc however I need them to make sure it starts with http:// Is their any way I can put validation on the field to make sure that this is entered? Or maybe I could use an input mask?

Any suggestions would be gratefully recieved.

Andy.

View 3 Replies View Related

Search For A String Within A String

Oct 27, 2006

I have a column called CPU_S within a table called workstation that contains sample text like P111 933

I want to use the update command to search the CPU_S column for entries that contain this in there string then add P3 to a column called CPU_N

So far I have the code below but I don't know how to search a column entry for a specific string within a string. Can this be done and how?


UPDATE workstations SET CPU_N = "P4"
WHERE CPU_S = ;

View 3 Replies View Related

String Not Getting Any Value

Jun 8, 2005

It worked in acces 2000, and now am using acces 2003. Anybody any clue why the string strVoorstel would not get the value? It just returns nothing.

Here's the code:

Dim strVoorstel As String
Dim lngTel As Long
Dim strQer As String
Dim strDa As String
Dim lngNum As Long

...........

View 6 Replies View Related

SQL String

Jul 26, 2006

Hi Folks,

i am using the sql below but i am having a little syntax error:

i am trying to refer to a variable called strRAGCol


strSQL = "SELECT tblSite.SiteID, tblSite.SiteRAG, tblSite.Active " _
& " FROM tblSite " _
& " WHERE (((tblSite.Active)=Yes)AND((tblSite.SiteRAG)= strRAGCol));"

Any help much appreciated.

Mark

View 4 Replies View Related

SQL String Help Please

Sep 29, 2005

The following SQL query is returning no records when I know for a fact there are some there! Can anyone please hlp me?!

Dim strSQL As String
strSQL = "SELECT * FROM tblHirer WHERE HirerSurname= 'Forms!frmFinanceProposal!Child845!Text430'"
rsFindDuplicates.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rsFindDuplicates.RecordCount


Also, I was wondering how I would put as 2nd filter on the recordset to equal a particular date?

Cheers

View 2 Replies View Related

Sql String

Dec 16, 2004

I had a query and the sql is:

SELECT Institutions.*, Institutions.merge, Institutions.Inst_type
FROM Institutions
WHERE (((Institutions.merge)=True) AND ((Institutions.Inst_type)=[forms]![merge_inst_type]![Inst_type]));

and I had a command button which process

MergeAllWord ("select * from merge_query_true")

but I am getting an error like this "make sure the sql is correct , sql was
select * from merge_query_true

select * from merge_query_true ---- is this code ok?

If I had cut the part "AND ((Institutions.Inst_type)=[forms]![merge_inst_type]![Inst_type])); from the sql it works fine....

View 4 Replies View Related

SQL String

May 30, 2007

WHat am I doing wrong...I cannot get the syntax correct...

Form the Query Builder:

SELECT tbl_GroupMembers.GroupNumber, tbl_GroupMembers.Email
FROM tbl_GroupMembers
WHERE (((tbl_GroupMembers.GroupNumber)="10"));


NEED IT TO BE IN THIS SQL STATEMENT:

Set rs = dbs.OpenRecordset("Select * From tbl_Groups", dbOpenDynaset)

View 3 Replies View Related

String Length.

Aug 19, 2005

I have a fax number field iwhich has a mask. +(000)-00-000-000 in a table of many records.
The problem is that every record has a number in the first part of country code +(001)which must have come there by mistakes, few records have complete and true fax numbers.

I need a query that can Update this Fax_Number field where the length of the string is less than 7 digits.
Some thing like UPDATE Contacts SET Contacts.Fax_Number = ""
WHERE ((Len("Fax_Number")>"6"));

How can i do this. Thanks.

View 4 Replies View Related

How To Split Up This String

Sep 15, 2005

I have a string:

Cars > Car Import > Car Import: Car Import

The first section is the Category, the second is the Product, the third is the SubProduct and the fourth is the keyword.

What is the best way to split each of these words into its own variable? I think I can do the first and end one but not the Car Import and Car Import one.

Thanks,

Dave

View 4 Replies View Related

Lookup String

Sep 18, 2005

I have a 67 binary code string produced from a query which concatenates these 1's and 0's.

What I need to do is have access decide what a particular string value/range is and return the process name. e.g

11000000000000000000000000000000000001000010000000 00000000000000001. The process name would be COMP RESOLVED MSA


10100000000000000000000000000000000001000010000000 00000000000000001.The process name would be COMP UNRESOLVED MSA

00000000000000000111010000000110000000000000000000 00000000000000001

The process name would be MSA NEW

There could be about 60 different Process Names

View 1 Replies View Related

Please Look At This Boolean Vs String

Dec 2, 2005

I'm working on a software that was developed by someone who left the company. The problem is that the software works for all locations execpt one that is in austria, after debuging I found out that there is a statement in the code that uses boolean true or false, the only way it worked is when I put an if statement with false or true as string, ex: if doc = "true" then
buttom line has anyone faced this problem, I searched microsoft website and they said that some vb engines don't convert boolean to string. Please Please if someone has a clue try to help. Thanks.

View 4 Replies View Related

Need Help With String Formatting

Dec 15, 2005

Hey everyone, I need some help with formatting a string for use with the DoCmd.RunSQL() method. I can't figure out how the hell VB deals with escape characters for the purpse of variables inside strings. I have this line right now.

strSQL = "INSERT INTO OS (OS) VALUES(" + OS.Value + ")"


This is supposed to insert one row into the "OS" Table, in the "OS" field. In my form there is a textbox called "OS" and I'm trying to insert that value into the DB table.

Two things.
1) Yes, I know, I have a lot of things named "OS"
2) Yes, I know I don't need to use a string here, but I'm just presenting this is an abbreviated example, in my full program I do in fact need a string.

View 3 Replies View Related

String In Textbox

May 2, 2006

Hi,

I have string SELECT Field from Table:

A1
A2
A3
....

I need to see records in textbox: A1, A2, A3..

Thank You in Advance

View 1 Replies View Related

Compare SQL String

Jan 4, 2007

Hi, all.

I have string: strSQL = SELECT SUM column FROM table

I need compare it with constant number, for Example:

If strSQL > 50000 Then ....

its not working, any Ideas?

Thank You in advance

View 4 Replies View Related

String To Date

Jan 16, 2007

I have just inherited a database that has dates stored as strings ex.: 01/01/01. I really do not have time to add a new field and convert the string date to date format (this would cause a lot of rewrite to the application that is using this table). I am trying to use the 'between' to display records that are between specific dates. Also, I am using Cold Fusion as the front end, if that makes any difference. Below is an example I thought might work but did not:

<cfquery name="acty_due_month" datasource="customer">
SELECT acty_end_dt
FROM activity
Where DateValue(Format([acty_end_dt], "MM/DD/YY"))=10/30/05
</cfquery>

Any assistance would be greatly appreciated.

View 9 Replies View Related

String Concatenation

Sep 14, 2007

In the table, i already had a few columns:

string1 string2 string3
001 1 1.5
001 2 6.4


I'd like to add a new column and concatenate the strings in previous columns with additional characters:

string1 string2 string3 string4
001 1 1.5 c-001-b01_1_5x
001 2 6.4 c-001-b02_6_4x
002 3 7 c-002-b03_7x

Is there a good way to do this? Thanks in advance.

View 2 Replies View Related

Building SQL String As Sub For No Value

Jun 21, 2005

I'm running a 9 parameter query, where values for the criteria are drawn from a form. I'm confused as to how I build an "if, then" string so that if one of the parameters is left blank, the program will skip it and go to the next.

Any suggestions?

View 8 Replies View Related

Spaces In String

Aug 8, 2005

How can I remove the spaces in the example string!

HICX108-Standard Top -Vendered

Thanks in advance!

View 2 Replies View Related

Please Help String Parse

Nov 11, 2005

Probably very simple but I cannot get the syntax right - it has been far too long since I used Access.

I am trying to seperate a field NAME from these formats:

LAST/FIRST M
LAST/FIRST

To two fields: Field LAST and field FIRST

Can someone please help?

Thank you! It is Friday and I want to go home!

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved