SQL String Limit???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am coding an Access 2000 form and ran into an interesting thing I have not
seen before. I created a long SQL statement in the code window and in the
process of stepping through the code and watching the "Locals" window, I saw
that my SQL statement was getting concatenated. Is there some restriction on
the length a SQL string can be, or is this a limitation of viewing the entire
string within the Locals window? My code is:

strSQL = "UPDATE [Sample Limits] SET Number= " & N & ", PID= " & Pd & ",
ParPID= '" & Ppd & "', PWIUNum= " & iNum & ", PWSiteNum= " & SNum & ",
PWPermitNum=" & pNum & ", PWPara=" & PWPar & ", ParmCode= '" & PCode & "',
LimitType= '" & Ltyp & "', Unit= '" & Unt & "', Notes= '" & Nts & "',
Minimum= " & Mnm & ", Maximum= " & Maxm & ", Max_TRC= " & MTRC & ",
Average_Type= " & ATyp & ", [Freq_Code]= '" & FCode & "'" & " WHERE
(([Sample Limits].[Number])= "" "

The result is the Locals window is:

"UPDATE [Sample Limits] SET Number= 28176, PID= 312, ParPID= '312021',
PWIUNum= 1, PWSiteNum= 3, PWPermitNum=12, PWPara=21, ParmCode= 'Phen',
LimitType= 'Daily', Unit= 'mg/L', Notes= 'Test', Minimum= 1, Maximum= 25,
Max_TRC= 0, Average_Type= 0, [Freq_"

I have not seen this before and I would appreciate any ideas about what is
happening here.

Thanks in advance,
Al
 
Al said:
I am coding an Access 2000 form and ran into an interesting thing I have not
seen before. I created a long SQL statement in the code window and in the
process of stepping through the code and watching the "Locals" window, I saw
that my SQL statement was getting concatenated. Is there some restriction on
the length a SQL string can be, or is this a limitation of viewing the entire
string within the Locals window? My code is:

strSQL = "UPDATE [Sample Limits] SET Number= " & N & ", PID= " & Pd & ",
ParPID= '" & Ppd & "', PWIUNum= " & iNum & ", PWSiteNum= " & SNum & ",
PWPermitNum=" & pNum & ", PWPara=" & PWPar & ", ParmCode= '" & PCode & "',
LimitType= '" & Ltyp & "', Unit= '" & Unt & "', Notes= '" & Nts & "',
Minimum= " & Mnm & ", Maximum= " & Maxm & ", Max_TRC= " & MTRC & ",
Average_Type= " & ATyp & ", [Freq_Code]= '" & FCode & "'" & " WHERE
(([Sample Limits].[Number])= "" "

The result is the Locals window is:

"UPDATE [Sample Limits] SET Number= 28176, PID= 312, ParPID= '312021',
PWIUNum= 1, PWSiteNum= 3, PWPermitNum=12, PWPara=21, ParmCode= 'Phen',
LimitType= 'Daily', Unit= 'mg/L', Notes= 'Test', Minimum= 1, Maximum= 25,
Max_TRC= 0, Average_Type= 0, [Freq_"

I have not seen this before and I would appreciate any ideas about what is
happening here.
Click to expand...


I'm just guessing here. but that may be an effect of the
Locals display. Try using Debug.Print strSQL

OTOH, you have all that on a single long line. It might be
better to use several lines with the line-continuation
characters space underscore. E.g.

strSQL = "UPDATE [Sample Limits] SET Number= " & N & _
", PID= " & Pd & ", ParPID= '" & Ppd & "', PWIUNum= " _
& iNum & ", PWSiteNum= " & SNum & _
", PWPermitNum=" & pNum & ", PWPara=" & PWPar & _
", ParmCode= '" & PCode & _
. . .
 
Or use
strSQL = "UPDATE [Sample Limits] SET Number= " & N & _
", PID= " & Pd & ", ParPID= '" & Ppd & "', PWIUNum= "
strSQL = strSQL & iNum & ", PWSiteNum= " & SNum & _
", PWPermitNum=" & pNum & ", PWPara=" & PWPar & _
", ParmCode= '" & PCode & _


--
Duane Hookom
MS Access MVP
--

Marshall Barton said:
Al said:
I am coding an Access 2000 form and ran into an interesting thing I have
not
seen before. I created a long SQL statement in the code window and in the
process of stepping through the code and watching the "Locals" window, I
saw
that my SQL statement was getting concatenated. Is there some restriction
on
the length a SQL string can be, or is this a limitation of viewing the
entire
string within the Locals window? My code is:

strSQL = "UPDATE [Sample Limits] SET Number= " & N & ", PID= " & Pd & ",
ParPID= '" & Ppd & "', PWIUNum= " & iNum & ", PWSiteNum= " & SNum & ",
PWPermitNum=" & pNum & ", PWPara=" & PWPar & ", ParmCode= '" & PCode & "',
LimitType= '" & Ltyp & "', Unit= '" & Unt & "', Notes= '" & Nts & "',
Minimum= " & Mnm & ", Maximum= " & Maxm & ", Max_TRC= " & MTRC & ",
Average_Type= " & ATyp & ", [Freq_Code]= '" & FCode & "'" & " WHERE
(([Sample Limits].[Number])= "" "

The result is the Locals window is:

"UPDATE [Sample Limits] SET Number= 28176, PID= 312, ParPID= '312021',
PWIUNum= 1, PWSiteNum= 3, PWPermitNum=12, PWPara=21, ParmCode= 'Phen',
LimitType= 'Daily', Unit= 'mg/L', Notes= 'Test', Minimum= 1, Maximum=
25,
Max_TRC= 0, Average_Type= 0, [Freq_"

I have not seen this before and I would appreciate any ideas about what is
happening here.
Click to expand...


I'm just guessing here. but that may be an effect of the
Locals display. Try using Debug.Print strSQL

OTOH, you have all that on a single long line. It might be
better to use several lines with the line-continuation
characters space underscore. E.g.

strSQL = "UPDATE [Sample Limits] SET Number= " & N & _
", PID= " & Pd & ", ParPID= '" & Ppd & "', PWIUNum= " _
& iNum & ", PWSiteNum= " & SNum & _
", PWPermitNum=" & pNum & ", PWPara=" & PWPar & _
", ParmCode= '" & PCode & _
. . .
Click to expand...
 
Back
Top