INSERT sql gets stuck if field more than 128 characters?

[Happy YN]

I have an insert sql which picks up the characters in a text box and inserts them in the correct field (memo). It works fine but if I type too many characters in the text box it gets stuck.It gets stuck even if I just view the preview without actually running it. At about 120-128 letters it suddenly displays nothing in the field in preview of the query and after that if I try to close anything it tells me code is being run click yes to continue or no to break but I still can't do anything more.
I stress this error is before any append is run, merely by viewing it.
If I run the sql as code in vba I get a similar error

 

[FoFa]

Could be wrong, but it sounds like you are exceeding the string limit of 256 (not in that one field, for the whole SQL statement).

 

[Happy YN]

I am sure I have sql's longer than 256?
Anyway the contents of the field do not form part of the sql only the reference to the text control on the form
here it is and as I said it works fine until I exceed 128 letters in the text control [Forms]![frmPopupPupilDetails]![txtreason]


INSERT INTO tblReminder ( PupilID, EntryDate, ReminderDate, reason )
SELECT [Forms]![frmPopupPupilDetails]![Combopupil] AS Expr1, Date() AS Expr2, IIf([Forms]![frmPopupPupilDetails]![optReminder]=2,Date()+7,IIf([Forms]![frmPopupPupilDetails]![optReminder]=3,Date()+14,IIf([Forms]![frmPopupPupilDetails]![optReminder]=4,Date()+21,IIf([Forms]![frmPopupPupilDetails]![optReminder]=5,Date()+30,IIf([Forms]![frmPopupPupilDetails]![optReminder]=6,[Forms]![frmPopupPupilDetails]![txtReminderDate]))))) AS Expr3, [Forms]![frmPopupPupilDetails]![txtreason] AS Expr4;

 

[FoFa]

You can have SQL longer 256, unless you are storing it in a VBA string. Are you using any special characters in the text box?

 

[Jon K]

I was able to run your query in Access 97 with up to 1024 characters in txtReason, but I couldn't run it in Access 2000 with more than 124 characters.

It seems the following code can insert any number of characters into the memo field.

Private Sub cmdUsingVBA_Click()
  On Error GoTo Err_Handler
  
  Dim SQL As String
  Dim RemDate As Date
  
  Select Case Me.optReminder
  Case 1
    ' ?
  Case 2
    RemDate = Date + 7
  Case 3
    RemDate = Date + 14
  Case 4
    RemDate = Date + 21
  Case 5
    RemDate = Date + 30
  Case Else
    RemDate = Me.txtReminderDate
  End Select
 
  SQL = "INSERT INTO tblReminder" & _
        " ( PupilID, EntryDate, ReminderDate,reason )" & _
        " Values ( """ & _
        [Forms]![frmPopupPupilDetails]![Combopupil] & _
        """,#" & Date & "#,#" & RemDate & "#,""" & _
        [Forms]![frmPopupPupilDetails]![txtreason] & """);"

  DoCmd.RunSQL SQL
  Exit Sub

Err_Handler:
  MsgBox Err.Description
End Sub

You can open the form in the attached database to see if the VBA code works on your system.


My system uses the US M/d/yyyy format for dates. If the dates don't work because your system uses another date format, you can try replacing each of the # signs in the code with two consecutive double quotes ""

Note   The code uses double quotes to delimit the text from txtReason. If you want to include double quotes in the text, just type two double quotes rather than one e.g.
The highest grade is ""A+""


Hope it helps.

The database was saved from Access 2000.

 

[Happy YN]

Jon your'e a marvel!!!!
Sorry for the delay in reply I only picked this up Sat night
I copied and pasted in your code and yes - it works brilliantly. I had to replace the # because the dates I need are UK format. What will happen if i run it on a US machine?
Also what was the real cause of the problem, was the sql just to complicated and assigning it to variables was a simplified sql
Thanks again
A happier Happy YN

 

[Happy YN]

Having now downloaded your db and studying it I feel I owe you an even bigger appreciation for all the work you put into replicating my problem and trying out the various possibilities

 

[Jon K]

Glad that it worked.

... I had to replace the # because the dates I need are UK format. What will happen if i run it on a US machine?

Also what was the real cause of the problem, was the sql just to complicated and assigning it to variables was a simplified sql ...

On a UK system, # returns dates in the m/d/yyyy format while " returns dates in d/m/yyyy. Hence " should be used to delimit dates.

On a US system, both # and " can return dates in the correct format of m/d/yyyy.

In code that uses " as the text delimiter, we need to replace each # in a text string with "".


As for the second question, I think the problem was caused by a bug in saved queries in (some versions of?) Access.

 

[Happy YN]

Ok Thanks again
I will use "" so that it should work with both uk and US formats
Happy YN

 

[SIM]

Jon-

It seems " is universally applicable. Why do we have the delimiter #?

Sim


 

 

[Jon K]

Sim,

# is documented as the delimiter for date literals in applications where the locale setting is US, whereas " is not documented as the delimiter for date literals.  So " may or may not work in future versions of Access. (I haven't tested it in Access 2002 and 2003 either.)

Jon K