VBA string length

[Tom]

I use the following lines of code to build a portion of a
SQL query string:

sqlInsert = "INSERT INTO
AgentGroupTrafficProfileReport (Date, Time, CollatedTime,
AnsweredCalls, IntercomCalls,"
sqlInsert = sqlInsert & " OutgoingCalls,
AvgAnsweredCallTime, AvgWrapupTime, AvgIntercomCallTime,
AvgOutgoingCallTime, CallsTransferredIn,
CallsTransferredOut,"
sqlInsert = sqlInsert & " CallsInquired,
AvgHeldOnTime, LongestHeldOnTime, LongestWrapupTime,
LongestIntercomCallTime, LongestOutgoingCallTime,"
sqlInsert = sqlInsert & " MimimumAgentsLoggedOn,
MaximumAgentsLoggedOn, NumberOfShortCalls,
NumberOfLongCalls) VALUES("

For some reason the string stops appending after "
CallsInquired, Ag" - notice that it uses the first letter
and third letter of the next word.

At one point I made five separate variables - one for each
line and one to concatinate the first four. The four
individual variables were all correct, but the fifth (sum)
showed the same problem.

The length of the string appears to max out at 444
characters. I made a fixed length string of 2000
characters (dim sqlString as string * 2000) but it didn't
fix the problem.

Anybody know what's going on?

 

[Dirk Goldgar]

I use the following lines of code to build a portion of a
SQL query string:

sqlInsert = "INSERT INTO
AgentGroupTrafficProfileReport (Date, Time, CollatedTime,
AnsweredCalls, IntercomCalls,"
sqlInsert = sqlInsert & " OutgoingCalls,
AvgAnsweredCallTime, AvgWrapupTime, AvgIntercomCallTime,
AvgOutgoingCallTime, CallsTransferredIn,
CallsTransferredOut,"
sqlInsert = sqlInsert & " CallsInquired,
AvgHeldOnTime, LongestHeldOnTime, LongestWrapupTime,
LongestIntercomCallTime, LongestOutgoingCallTime,"
sqlInsert = sqlInsert & " MimimumAgentsLoggedOn,
MaximumAgentsLoggedOn, NumberOfShortCalls,
NumberOfLongCalls) VALUES("

For some reason the string stops appending after "
CallsInquired, Ag" - notice that it uses the first letter
and third letter of the next word.

At one point I made five separate variables - one for each
line and one to concatinate the first four. The four
individual variables were all correct, but the fifth (sum)
showed the same problem.

The length of the string appears to max out at 444
characters. I made a fixed length string of 2000
characters (dim sqlString as string * 2000) but it didn't
fix the problem.

Anybody know what's going on?

Not immediately. Could you post more of the surrounding code, including
the definition of the variable, sqlInsert?

 

[Guest]

Here's the entire subroutine. As you can see, I tried
abbreviating the variable names in order to shorten the
string length - this also didn't work. Now the string
maxes out at 337 characters.

The subroutine grabs data from an exported text file from
our phone software and then places it into two empty
tables.

Public Sub GetAllAgentPhones()
Dim text As String
Dim sqlInsert As String
Dim sqlStr As String
Dim Values As String
Dim cnt As Integer
Dim path As String

path = CurrentProject.path
path = path & "\report1.txt"
Open path For Input As #1

Do
Input #1, text
Loop While text <> "Number Of Long Calls"

sqlInsert = "INSERT INTO AGTPR (Date, Time, CT, AC,
IC,"
sqlInsert = sqlInsert & " OC, AACT, AWT, AICT, AOCT,
CTI, CTO,"
sqlInsert = sqlInsert & " CI, AHOT, LHOT, ROC, AROWT,
LROWT, LACT, LWT, LICT, LOCT,"
sqlInsert = sqlInsert & " MinALO, MaxALO, NOST, NOLC)
VALUES("

'***** get a record
'clear variable
Values = ""

'Agent Number
Input #1, text
Do
Values = Values & "#" & text & "#, "

'Agent Name
Input #1, text
Values = Values & "'" & text & "'"

For cnt = 3 To 26
Input #1, text
Values = Values & ", " & text
Next

Values = Values & ");"

sqlStr = sqlInsert & Values
Dim l As Integer
l = Len(sqlStr)

DoCmd.RunSQL sqlStr

'clear variable
Values = ""

'Agent Number
Input #1, text
Loop While text <> "New Item"

'Skip over text
Do
Input #1, text
Loop While text <> "Time Unavailable"

sqlInsert = "INSERT INTO AgentTrafficReport
(AgentNumber, AgentName, AC, AACT, AAWT,"
sqlInsert = sqlInsert & " TI, TO, IC, AHT, ROC, AROWT,
IntC,"
sqlInsert = sqlInsert & " AICT, OC, AOCT, SC, LC, NOL,"
sqlInsert = sqlInsert & " TLO, NOU, TU) VALUES("

'***** get a record
Do
'clear variable
Values = ""

'Agent Number
Input #1, text
Values = Values & "'" & text & "', "

'Agent Name
Input #1, text
Values = Values & "'" & text & "'"

For cnt = 3 To 21
Input #1, text
Values = Values & ", " & text
Next

Values = Values & ");"

sqlStr = sqlInsert & Values
DoCmd.RunSQL sqlStr

Loop While Not EOF(1)

Close #1

End Sub

 

[Rodrigo]

Looks like this will work better if you link your report1.txt file. Then
just use a simple append query to update
AGTPR with it. It will definetly run faster, and be much cleaner than
looping trough each line of the text file.

Rodrigo.

 

[Dirk Goldgar]

Here's the entire subroutine. As you can see, I tried
abbreviating the variable names in order to shorten the
string length - this also didn't work. Now the string
maxes out at 337 characters.

The subroutine grabs data from an exported text file from
our phone software and then places it into two empty
tables.

Public Sub GetAllAgentPhones()
Dim text As String
Dim sqlInsert As String
Dim sqlStr As String
Dim Values As String
Dim cnt As Integer
Dim path As String

path = CurrentProject.path
path = path & "\report1.txt"
Open path For Input As #1

Do
Input #1, text
Loop While text <> "Number Of Long Calls"

sqlInsert = "INSERT INTO AGTPR (Date, Time, CT, AC,
IC,"
sqlInsert = sqlInsert & " OC, AACT, AWT, AICT, AOCT,
CTI, CTO,"
sqlInsert = sqlInsert & " CI, AHOT, LHOT, ROC, AROWT,
LROWT, LACT, LWT, LICT, LOCT,"
sqlInsert = sqlInsert & " MinALO, MaxALO, NOST, NOLC)
VALUES("

'***** get a record
'clear variable
Values = ""

'Agent Number
Input #1, text
Do
Values = Values & "#" & text & "#, "

'Agent Name
Input #1, text
Values = Values & "'" & text & "'"

For cnt = 3 To 26
Input #1, text
Values = Values & ", " & text
Next

Values = Values & ");"

sqlStr = sqlInsert & Values
Dim l As Integer
l = Len(sqlStr)

DoCmd.RunSQL sqlStr

'clear variable
Values = ""

'Agent Number
Input #1, text
Loop While text <> "New Item"

'Skip over text
Do
Input #1, text
Loop While text <> "Time Unavailable"

sqlInsert = "INSERT INTO AgentTrafficReport
(AgentNumber, AgentName, AC, AACT, AAWT,"
sqlInsert = sqlInsert & " TI, TO, IC, AHT, ROC, AROWT,
IntC,"
sqlInsert = sqlInsert & " AICT, OC, AOCT, SC, LC, NOL,"
sqlInsert = sqlInsert & " TLO, NOU, TU) VALUES("

'***** get a record
Do
'clear variable
Values = ""

'Agent Number
Input #1, text
Values = Values & "'" & text & "', "

'Agent Name
Input #1, text
Values = Values & "'" & text & "'"

For cnt = 3 To 21
Input #1, text
Values = Values & ", " & text
Next

Values = Values & ");"

sqlStr = sqlInsert & Values
DoCmd.RunSQL sqlStr

Loop While Not EOF(1)

Close #1

End Sub

I don't see anything obvious, but I seriously doubt it's a problem with
the length of the string value as stored in the variable. How certain
are you that it's sqlStr that is being truncated? Did you check its
value and length immediately before calling DoCmd.RunSQL each time? Is
it possible that

(a) RunSQL is more limited than the help file says? The A2K2 help
file says you get up to 32,768 characters for the SQL argument of this
method, but maybe that's a mistake; I'm not sure. What version of
Access are you using?

(b) One of the values you've concatenated into strSQL contains a
single-quote character ('), so that when you attempt to run the SQL
statement it's not being parsed properly?

(c) You are looping beyond where you expected, so that strSQL is
actually growing beyond its allowable limits?

Beyond those ideas, I'm baffled at the moment.

 

[Guest]

I'm using Acess 2000 on W2K

Unfortunately, I have to filter out a number of useless titles and column headings, so I can't just link and append

The two strings sqlInsert and Values are complete at the point where they are concatenated to produce sqlStr. I'll check again, but after 15 minutes of staring at them I haven't yet found a missing or misplaced '. But when I combine them into sqlStr the total string is cut short

In the original version (with the long variable names) the concatenation stopped before sqlInsert was completely formed - just after CallsInquired - 444 characters in length

After abbreviating all of the variables (second version), the concatenation reaches until a little over half way into the Values string - but now it is 337 characters in length

When I check sqlStr prior to the LEN function it is incomplete. The DoCmd fails because of that incomplete SQL string

Thanks for your input guys. I'm the only programmer here and sometimes it helps just to have some other minds looking at the problem.

 

[Guest]

Here's a copy of Report1.txt. Maybe it'll help to see the data

"New Item
"Export Header
"Sales DNC","24 Hour
"04/01/04","08:00:00
"04/19/04","22:00:00
160560
"New Item
"Agent Group Traffic Profile Report
"Agent Group Traffic Profile for AG551
"Date","Time","Collated Time","Answered Calls","Intercom Calls","Outgoing Calls","Ave. Answered Call Time","Ave. Wrapup Time","Ave. Intercom Call Time","Ave. Outgoing Call Time","Calls Transferred In","Calls Transferred Out","Calls Inquired","Ave. Held On Time","Longest Held On Time","Ring On Calls","Ave. Ring On Wait Time","Longest Ring On Wait Time","Longest Answered Call Time","Longest Wrapup Time","Longest Intercom Call Time","Longest Outgoing Call Time","Minimum Agents Logged On","Maximum Agents Logged On","Number Of Short Calls","Number Of Long Calls
"04/01/04","08:00:00",86400,45,89,30,2433.02222,2169.42222,56.32584,146.73333,5,13,3,31.18750,107,0,0.00000,0,57682,57667,824,582,0,0,0,
"04/02/04","08:00:00",86400,59,59,21,310.00000,11.03390,23.15254,114.19048,3,18,7,30.84000,278,0,0.00000,0,1275,106,211,427,0,0,0,
"04/03/04","08:00:00",86400,0,0,0,0.00000,0.00000,0.00000,0.00000,0,0,0,0.00000,0,0,0.00000,0,0,0,0,0,0,0,0,
"04/04/04","08:00:00",86400,0,0,0,0.00000,0.00000,0.00000,0.00000,0,0,0,0.00000,0,0,0.00000,0,0,0,0,0,0,0,0,
"04/05/04","08:00:00",86400,78,40,18,239.76923,11.17949,28.35000,101.00000,5,18,4,38.13636,402,0,0.00000,0,856,148,291,375,0,0,0,
"04/06/04","08:00:00",86400,36,45,31,299.47222,8.25000,39.51111,104.67742,0,10,3,54.23077,348,0,0.00000,0,1195,11,173,451,0,0,0,
"04/07/04","08:00:00",86400,62,82,28,328.03226,11.38710,27.42683,114.21429,2,15,8,34.00000,153,0,0.00000,0,1024,137,309,684,0,0,0,
"04/08/04","08:00:00",86400,46,45,40,1213.19565,871.93478,17.95556,70.20000,1,6,5,35.09091,130,0,0.00000,0,39717,39702,104,232,0,0,0,
"04/09/04","08:00:00",86400,0,0,0,0.00000,0.00000,0.00000,0.00000,0,0,0,0.00000,0,0,0.00000,0,0,0,0,0,0,0,0,
"04/10/04","08:00:00",86400,0,0,0,0.00000,0.00000,0.00000,0.00000,0,0,0,0.00000,0,0,0.00000,0,0,0,0,0,0,0,0,
"04/11/04","08:00:00",86400,0,0,0,0.00000,0.00000,0.00000,0.00000,0,0,0,0.00000,0,0,0.00000,0,0,0,0,0,0,0,0,
"04/12/04","08:00:00",86400,71,61,31,405.83099,20.87324,25.77049,75.00000,2,11,7,73.88889,377,0,0.00000,0,1489,442,139,266,0,0,0,
"04/13/04","08:00:00",86400,88,46,33,370.42045,20.25000,22.04348,128.39394,8,11,13,23.33333,123,0,0.00000,0,2373,561,149,895,0,0,0,
"04/14/04","08:00:00",86400,43,43,32,1869.88372,1518.72093,24.88372,144.87500,2,8,3,28.27273,193,1,6.00000,6,64895,64880,167,783,0,0,0,
"04/15/04","08:00:00",86400,55,39,31,2772.85455,2434.45455,100.53846,135.77419,1,6,4,26.50000,136,1,1.00000,1,133392,133377,1125,510,0,0,0,
"04/16/04","08:00:00",86400,58,33,27,376.12069,10.39655,78.27273,104.85185,3,15,6,18.33333,94,0,0.00000,0,1689,54,1117,306,0,0,0,
"04/17/04","08:00:00",86400,0,0,0,0.00000,0.00000,0.00000,0.00000,0,0,0,0.00000,0,0,0.00000,0,0,0,0,0,0,0,0,
"04/18/04","08:00:00",86400,0,0,0,0.00000,0.00000,0.00000,0.00000,0,0,0,0.00000,0,0,0.00000,0,0,0,0,0,0,0,0,
"04/19/04","08:00:00",50400,10,7,6,21543.80000,21423.20000,9.42857,113.33333,1,5,0,27.60000,125,1,3.00000,3,214159,214144,31,221,0,5,0,
"New Item
"Agent Traffic Report
"Agent Traffic for AG551
"Agent Number","Agent Name","Answered Calls","Ave. Answered Call Time","Ave. Answered Wrapup Time","Transferred In","Transferred Out","Inquiry Calls","Ave. Held Time","RingOn Calls","Ave. RingOn Wait Time","Intercom Calls","Ave. Intercom Call Time","Outgoing Calls","Ave. Outgoing Call Time","Short Calls","Long Calls","Number of Logons","Time Logged On","Number of Unavails","Time Unavailable
"A6206","Elizabeth",97,772.84536,418.76289,7,32,12,15.02273,0,0.00000,0,0.00000,42,103.61905,0,0,22,349336,43,129284
"A6207","Tracey",92,1028.66304,724.98913,3,19,15,52.55882,0,0.00000,0,0.00000,127,112.38583,0,0,26,299020,44,139140
"A6208","Kelly",151,1729.41060,1428.30464,7,28,18,53.86957,0,0.00000,0,0.00000,73,117.10959,0,0,32,343552,40,128202
"A6210","Dawn",122,863.74590,484.26230,9,19,10,46.06897,0,0.00000,0,0.00000,28,112.64286,0,0,13,334376,25,131071
"A6212","212",70,311.25714,19.55714,0,6,1,10.42857,0,0.00000,0,0.00000,20,114.50000,0,0,18,133342,24,1480552
"A6213","Donna L.",20,305.75000,9.50000,0,2,0,3.50000,0,0.00000,0,0.00000,12,75.66667,0,0,15,73524,9,1539449
"A6209","6209",99,2029.85859,1756.82828,7,30,7,17.00000,0,0.00000,0,0.00000,26,125.42308,0,0,31,306023,37,1375950

 

[Dirk Goldgar]

Here's a copy of Report1.txt. Maybe it'll help to see the data.

"New Item"
"Export Header"
"Sales DNC","24 Hour"
"04/01/04","08:00:00"
"04/19/04","22:00:00"
1605600
"New Item"
"Agent Group Traffic Profile Report"
"Agent Group Traffic Profile for AG551"
"Date","Time","Collated Time","Answered Calls","Intercom
Calls","Outgoing Calls","Ave. Answered Call Time","Ave. Wrapup
Time","Ave. Intercom Call Time","Ave. Outgoing Call Time","Calls
Transferred In","Calls Transferred Out","Calls Inquired","Ave. Held
On Time","Longest Held On Time","Ring On Calls","Ave. Ring On Wait
Time","Longest Ring On Wait Time","Longest Answered Call
Time","Longest Wrapup Time","Longest Intercom Call Time","Longest
Outgoing Call Time","Minimum Agents Logged On","Maximum Agents Logged
On","Number Of Short Calls","Number Of Long Calls"
"04/01/04","08:00:00",86400,45,89,30,2433.02222,2169.42222,56.32584,146.
73333,5,13,3,31.18750,107,0,0.00000,0,57682,57667,824,582,0,0,0,0
"04/02/04","08:00:00",86400,59,59,21,310.00000,11.03390,23.15254,114.190
48,3,18,7,30.84000,278,0,0.00000,0,1275,106,211,427,0,0,0,0
"04/03/04","08:00:00",86400,0,0,0,0.00000,0.00000,0.00000,0.00000,0,0,0,
0.00000,0,0,0.00000,0,0,0,0,0,0,0,0,0
"04/04/04","08:00:00",86400,0,0,0,0.00000,0.00000,0.00000,0.00000,0,0,0,
0.00000,0,0,0.00000,0,0,0,0,0,0,0,0,0
"04/05/04","08:00:00",86400,78,40,18,239.76923,11.17949,28.35000,101.000
00,5,18,4,38.13636,402,0,0.00000,0,856,148,291,375,0,0,0,0
"04/06/04","08:00:00",86400,36,45,31,299.47222,8.25000,39.51111,104.6774
2,0,10,3,54.23077,348,0,0.00000,0,1195,11,173,451,0,0,0,0
"04/07/04","08:00:00",86400,62,82,28,328.03226,11.38710,27.42683,114.214
29,2,15,8,34.00000,153,0,0.00000,0,1024,137,309,684,0,0,0,0
"04/08/04","08:00:00",86400,46,45,40,1213.19565,871.93478,17.95556,70.20
000,1,6,5,35.09091,130,0,0.00000,0,39717,39702,104,232,0,0,0,0
"04/09/04","08:00:00",86400,0,0,0,0.00000,0.00000,0.00000,0.00000,0,0,0,
0.00000,0,0,0.00000,0,0,0,0,0,0,0,0,0
"04/10/04","08:00:00",86400,0,0,0,0.00000,0.00000,0.00000,0.00000,0,0,0,
0.00000,0,0,0.00000,0,0,0,0,0,0,0,0,0
"04/11/04","08:00:00",86400,0,0,0,0.00000,0.00000,0.00000,0.00000,0,0,0,
0.00000,0,0,0.00000,0,0,0,0,0,0,0,0,0
"04/12/04","08:00:00",86400,71,61,31,405.83099,20.87324,25.77049,75.0000
0,2,11,7,73.88889,377,0,0.00000,0,1489,442,139,266,0,0,0,0
"04/13/04","08:00:00",86400,88,46,33,370.42045,20.25000,22.04348,128.393
94,8,11,13,23.33333,123,0,0.00000,0,2373,561,149,895,0,0,0,0
"04/14/04","08:00:00",86400,43,43,32,1869.88372,1518.72093,24.88372,144.
87500,2,8,3,28.27273,193,1,6.00000,6,64895,64880,167,783,0,0,0,0
"04/15/04","08:00:00",86400,55,39,31,2772.85455,2434.45455,100.53846,135
..77419,1,6,4,26.50000,136,1,1.00000,1,133392,133377,1125,510,0,0,0,0
"04/16/04","08:00:00",86400,58,33,27,376.12069,10.39655,78.27273,104.851
85,3,15,6,18.33333,94,0,0.00000,0,1689,54,1117,306,0,0,0,0
"04/17/04","08:00:00",86400,0,0,0,0.00000,0.00000,0.00000,0.00000,0,0,0,
0.00000,0,0,0.00000,0,0,0,0,0,0,0,0,0
"04/18/04","08:00:00",86400,0,0,0,0.00000,0.00000,0.00000,0.00000,0,0,0,
0.00000,0,0,0.00000,0,0,0,0,0,0,0,0,0
"04/19/04","08:00:00",50400,10,7,6,21543.80000,21423.20000,9.42857,113.3
3333,1,5,0,27.60000,125,1,3.00000,3,214159,214144,31,221,0,5,0,0
"New Item" "Agent Traffic Report" "Agent Traffic for AG551" "Agent
Number","Agent Name","Answered Calls","Ave. Answered Call Time","Ave.
Answered Wrapup Time","Transferred In","Transferred Out","Inquiry
Calls","Ave. Held Time","RingOn Calls","Ave. RingOn Wait
Time","Intercom Calls","Ave. Intercom Call Time","Outgoing
Calls","Ave. Outgoing Call Time","Short Calls","Long Calls","Number
of Logons","Time Logged On","Number of Unavails","Time Unavailable"
"A6206","Elizabeth",97,772.84536,418.76289,7,32,12,15.02273,0,0.00000,0,
0.00000,42,103.61905,0,0,22,349336,43,1292848
"A6207","Tracey",92,1028.66304,724.98913,3,19,15,52.55882,0,0.00000,0,0.
00000,127,112.38583,0,0,26,299020,44,1391403
"A6208","Kelly",151,1729.41060,1428.30464,7,28,18,53.86957,0,0.00000,0,0
..00000,73,117.10959,0,0,32,343552,40,1282022
"A6210","Dawn",122,863.74590,484.26230,9,19,10,46.06897,0,0.00000,0,0.00
000,28,112.64286,0,0,13,334376,25,1310710
"A6212","212",70,311.25714,19.55714,0,6,1,10.42857,0,0.00000,0,0.00000,2
0,114.50000,0,0,18,133342,24,1480552
L.",20,305.75000,9.50000,0,2,0,3.50000,0,0.00000,0,0.00000,12,75.66667,0
,0,15,73524,9,1539449

"A6209","6209",99,2029.85859,1756.82828,7,30,7,17.00000,0,0.00000,0,0.00
000,26,125.42308,0,0,31,306023,37,1375950

I ran your code with the data you supplied, and it had no problem with
truncation that I can see. This is what I get out (length above,
content below):

-----------------
338
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/01/04#, '08:00:00', 86400, 45,
89, 30, 2433.02222, 2169.42222, 56.32584, 146.73333, 5, 13, 3, 31.18750,
107, 0, 0.00000, 0, 57682, 57667, 824, 582, 0, 0, 0, 0);
-----------------
332
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/02/04#, '08:00:00', 86400, 59,
59, 21, 310.00000, 11.03390, 23.15254, 114.19048, 3, 18, 7, 30.84000,
278, 0, 0.00000, 0, 1275, 106, 211, 427, 0, 0, 0, 0);
-----------------
310
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/03/04#, '08:00:00', 86400, 0, 0,
0, 0.00000, 0.00000, 0.00000, 0.00000, 0, 0, 0, 0.00000, 0, 0, 0.00000,
0, 0, 0, 0, 0, 0, 0, 0, 0);
-----------------
310
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/04/04#, '08:00:00', 86400, 0, 0,
0, 0.00000, 0.00000, 0.00000, 0.00000, 0, 0, 0, 0.00000, 0, 0, 0.00000,
0, 0, 0, 0, 0, 0, 0, 0, 0);
-----------------
331
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/05/04#, '08:00:00', 86400, 78,
40, 18, 239.76923, 11.17949, 28.35000, 101.00000, 5, 18, 4, 38.13636,
402, 0, 0.00000, 0, 856, 148, 291, 375, 0, 0, 0, 0);
-----------------
330
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/06/04#, '08:00:00', 86400, 36,
45, 31, 299.47222, 8.25000, 39.51111, 104.67742, 0, 10, 3, 54.23077,
348, 0, 0.00000, 0, 1195, 11, 173, 451, 0, 0, 0, 0);
-----------------
332
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/07/04#, '08:00:00', 86400, 62,
82, 28, 328.03226, 11.38710, 27.42683, 114.21429, 2, 15, 8, 34.00000,
153, 0, 0.00000, 0, 1024, 137, 309, 684, 0, 0, 0, 0);
-----------------
335
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/08/04#, '08:00:00', 86400, 46,
45, 40, 1213.19565, 871.93478, 17.95556, 70.20000, 1, 6, 5, 35.09091,
130, 0, 0.00000, 0, 39717, 39702, 104, 232, 0, 0, 0, 0);
-----------------
310
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/09/04#, '08:00:00', 86400, 0, 0,
0, 0.00000, 0.00000, 0.00000, 0.00000, 0, 0, 0, 0.00000, 0, 0, 0.00000,
0, 0, 0, 0, 0, 0, 0, 0, 0);
-----------------
310
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/10/04#, '08:00:00', 86400, 0, 0,
0, 0.00000, 0.00000, 0.00000, 0.00000, 0, 0, 0, 0.00000, 0, 0, 0.00000,
0, 0, 0, 0, 0, 0, 0, 0, 0);
-----------------
310
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/11/04#, '08:00:00', 86400, 0, 0,
0, 0.00000, 0.00000, 0.00000, 0.00000, 0, 0, 0, 0.00000, 0, 0, 0.00000,
0, 0, 0, 0, 0, 0, 0, 0, 0);
-----------------
331
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/12/04#, '08:00:00', 86400, 71,
61, 31, 405.83099, 20.87324, 25.77049, 75.00000, 2, 11, 7, 73.88889,
377, 0, 0.00000, 0, 1489, 442, 139, 266, 0, 0, 0, 0);
-----------------
333
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/13/04#, '08:00:00', 86400, 88,
46, 33, 370.42045, 20.25000, 22.04348, 128.39394, 8, 11, 13, 23.33333,
123, 0, 0.00000, 0, 2373, 561, 149, 895, 0, 0, 0, 0);
-----------------
337
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/14/04#, '08:00:00', 86400, 43,
43, 32, 1869.88372, 1518.72093, 24.88372, 144.87500, 2, 8, 3, 28.27273,
193, 1, 6.00000, 6, 64895, 64880, 167, 783, 0, 0, 0, 0);
-----------------
341
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/15/04#, '08:00:00', 86400, 55,
39, 31, 2772.85455, 2434.45455, 100.53846, 135.77419, 1, 6, 4, 26.50000,
136, 1, 1.00000, 1, 133392, 133377, 1125, 510, 0, 0, 0, 0);
-----------------
331
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/16/04#, '08:00:00', 86400, 58,
33, 27, 376.12069, 10.39655, 78.27273, 104.85185, 3, 15, 6, 18.33333,
94, 0, 0.00000, 0, 1689, 54, 1117, 306, 0, 0, 0, 0);
-----------------
310
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/17/04#, '08:00:00', 86400, 0, 0,
0, 0.00000, 0.00000, 0.00000, 0.00000, 0, 0, 0, 0.00000, 0, 0, 0.00000,
0, 0, 0, 0, 0, 0, 0, 0, 0);
-----------------
310
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/18/04#, '08:00:00', 86400, 0, 0,
0, 0.00000, 0.00000, 0.00000, 0.00000, 0, 0, 0, 0.00000, 0, 0, 0.00000,
0, 0, 0, 0, 0, 0, 0, 0, 0);
-----------------
337
INSERT INTO AGTPR (Date, Time, CT, AC, IC, OC, AACT, AWT, AICT, AOCT,
CTI , CTO, CI, AHOT, LHOT, ROC, AROWT, LROWT, LACT, LWT, LICT, LOCT,
MinALO, MaxALO, NOST, NOLC) VALUES(#04/19/04#, '08:00:00', 50400, 10, 7,
6, 21543.80000, 21423.20000, 9.42857, 113.33333, 1, 5, 0, 27.60000, 125,
1, 3.00000, 3, 214159, 214144, 31, 221, 0, 5, 0, 0);
-----------------
296
INSERT INTO AgentTrafficReport (AgentNumber, AgentName, AC, AACT, AAWT,
TI, TO, IC, AHT, ROC, AROWT, IntC, AICT, OC, AOCT, SC, LC, NOL, TLO,
NOU, TU) VALUES('A6206', 'Elizabeth', 97, 772.84536, 418.76289, 7, 32,
12, 15.02273, 0, 0.00000, 0, 0.00000, 42, 103.61905, 0, 0, 22, 349336,
43, 1292848);
-----------------
295
INSERT INTO AgentTrafficReport (AgentNumber, AgentName, AC, AACT, AAWT,
TI, TO, IC, AHT, ROC, AROWT, IntC, AICT, OC, AOCT, SC, LC, NOL, TLO,
NOU, TU) VALUES('A6207', 'Tracey', 92, 1028.66304, 724.98913, 3, 19, 15,
52.55882, 0, 0.00000, 0, 0.00000, 127, 112.38583, 0, 0, 26, 299020, 44,
1391403);
-----------------
295
INSERT INTO AgentTrafficReport (AgentNumber, AgentName, AC, AACT, AAWT,
TI, TO, IC, AHT, ROC, AROWT, IntC, AICT, OC, AOCT, SC, LC, NOL, TLO,
NOU, TU) VALUES('A6208', 'Kelly', 151, 1729.41060, 1428.30464, 7, 28,
18, 53.86957, 0, 0.00000, 0, 0.00000, 73, 117.10959, 0, 0, 32, 343552,
40, 1282022);
-----------------
292
INSERT INTO AgentTrafficReport (AgentNumber, AgentName, AC, AACT, AAWT,
TI, TO, IC, AHT, ROC, AROWT, IntC, AICT, OC, AOCT, SC, LC, NOL, TLO,
NOU, TU) VALUES('A6210', 'Dawn', 122, 863.74590, 484.26230, 9, 19, 10,
46.06897, 0, 0.00000, 0, 0.00000, 28, 112.64286, 0, 0, 13, 334376, 25,
1310710);
-----------------
287
INSERT INTO AgentTrafficReport (AgentNumber, AgentName, AC, AACT, AAWT,
TI, TO, IC, AHT, ROC, AROWT, IntC, AICT, OC, AOCT, SC, LC, NOL, TLO,
NOU, TU) VALUES('A6212', '212', 70, 311.25714, 19.55714, 0, 6, 1,
10.42857, 0, 0.00000, 0, 0.00000, 20, 114.50000, 0, 0, 18, 133342, 24,
1480552);
-----------------
287
INSERT INTO AgentTrafficReport (AgentNumber, AgentName, AC, AACT, AAWT,
TI, TO, IC, AHT, ROC, AROWT, IntC, AICT, OC, AOCT, SC, LC, NOL, TLO,
NOU, TU) VALUES('A6213', 'Donna L.', 20, 305.75000, 9.50000, 0, 2, 0,
3.50000, 0, 0.00000, 0, 0.00000, 12, 75.66667, 0, 0, 15, 73524, 9,
1539449);
-----------------
292
INSERT INTO AgentTrafficReport (AgentNumber, AgentName, AC, AACT, AAWT,
TI, TO, IC, AHT, ROC, AROWT, IntC, AICT, OC, AOCT, SC, LC, NOL, TLO,
NOU, TU) VALUES('A6209', '6209', 99, 2029.85859, 1756.82828, 7, 30, 7,
17.00000, 0, 0.00000, 0, 0.00000, 26, 125.42308, 0, 0, 31, 306023, 37,
1375950);

 

[Guest]

Thanks Dirk. I wonder if I'm missing a service pack

The code always "felt" right to me. But even on a second computer I have the truncation problem

So the problem must be somewhere within the Access project. I installed several ActiveX references which I eventually didn't use, but they caused a number of problems with normal VB (actually with the QB portion of VB). Things like CHR and MID and LEFT stopped working until I removed the references. Maybe there's something else floating around in there

Oh well. At least I know where the problem isn't

Thanks again.

 

[Dirk Goldgar]

Thanks Dirk. I wonder if I'm missing a service pack?

The code always "felt" right to me. But even on a second computer I
have the truncation problem.

So the problem must be somewhere within the Access project. I
installed several ActiveX references which I eventually didn't use,
but they caused a number of problems with normal VB (actually with
the QB portion of VB). Things like CHR and MID and LEFT stopped
working until I removed the references. Maybe there's something else
floating around in there.

Oh well. At least I know where the problem isn't.

Thanks again.

I'm curious as to what may be going on here. If you'd like to send me a
cut-down copy of your database, containing only the elements necessary
to demonstrate the problem, compacted and then zipped to less than 1MB
in size (preferably much smaller) -- I'll have a look at it, time
permitting. You can send it to the address derived by removing NO SPAM
from the reply address of this message.

 

[Guest]

I'm also running into a similar problem, and I'm running
A2000 on W98SE. My variable is truncated after 255
characters, which seems like one of those
inherent 'limitations' we used to have in the old days of
DOS-based programming. I also tried the 'dim sqlstr as
string * 400' and it didn't change a thing...
Another sole-provider looking for answers...

-----Original Message-----
I'm using Acess 2000 on W2K.

Unfortunately, I have to filter out a number of useless

titles and column headings, so I can't just link and
append.

The two strings sqlInsert and Values are complete at the

point where they are concatenated to produce sqlStr. I'll
check again, but after 15 minutes of staring at them I
haven't yet found a missing or misplaced '. But when I
combine them into sqlStr the total string is cut short.

In the original version (with the long variable names)

the concatenation stopped before sqlInsert was completely
formed - just after CallsInquired - 444 characters in
length.

After abbreviating all of the variables (second version),

the concatenation reaches until a little over half way
into the Values string - but now it is 337 characters in
length.

When I check sqlStr prior to the LEN function it is

incomplete. The DoCmd fails because of that incomplete
SQL string.

Thanks for your input guys. I'm the only programmer here

and sometimes it helps just to have some other minds
looking at the problem.

 

[Dirk Goldgar]

I'm also running into a similar problem, and I'm running
A2000 on W98SE. My variable is truncated after 255
characters, which seems like one of those
inherent 'limitations' we used to have in the old days of
DOS-based programming. I also tried the 'dim sqlstr as
string * 400' and it didn't change a thing...
Another sole-provider looking for answers...

Are you sure that it's a variable -- an actual, VBA, "Dim strMyVar As
String"-type variable -- that is being truncated? There are a number of
things that can force the value of a control or a field to be truncated
at 255 characters, and a combo or list box column is always limited to
255 characters.

 

[Tom]

-----Original Message-----


I'm curious as to what may be going on here. If you'd like to send me a
cut-down copy of your database, containing only the elements necessary
to demonstrate the problem, compacted and then zipped to less than 1MB
in size (preferably much smaller) -- I'll have a look at it, time
permitting. You can send it to the address derived by removing NO SPAM
from the reply address of this message.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

I'll send you a stripped down version of the database
within the next few days.

For now, might the problem be related to Microsoft
Knowledge Base Article - 824182?

Tom

 

[Dirk Goldgar]

I'll send you a stripped down version of the database
within the next few days.
Great.

For now, might the problem be related to Microsoft
Knowledge Base Article - 824182?

I can't see how. Are you sure that's the article ID you meant? That
article has to do with a bug in field type mapping when importing text
files.