Building SQL from variables with quoted string value

[SME]

I would like to insert some values into a table, but I'm
having trouble adding quotes (") around the string value.
Would I also need to add hash marks (#) around the date
entry?

Also, the text box containing the date is formated to
accept a short date (mm/dd/yyyy) ... and an error is
displayed if a date is entered incorrectly, but I would
like to catch this and present a friendlier error
message. What would be the best method to do that?

And last question What is the difference
between 'DoCmd.RunSQL strSQL' and 'CurrentDb.Execute
strSQL, dbFailOnError'

Many thanks in advance for your time.


****** Code used to insert values

Private Sub btnSave_Click()

Dim newDate As Date
Dim newEntry As String
Dim newReferral As Boolean
Dim internNumber As Integer
Dim strSQL As String

'check for null values here

internNumber = Me.internID
newDate = Me.txtDate
newEntry = Me.txtEntry
newReferral = Me.chkReferral

strSQL = "INSERT INTO tblInternsContactLog (internID,
contactLog_date, contactLog_entry,
contactLog_isreferral) "

strSQL = strSQL & "VALUES (" & internnumber & ", " &
newDate & ", " & newEntry & ", " & newReferral & " )"

' DoCmd.RunSQL strSQL
' CurrentDb.Execute strSQL, dbFailOnError

 

[John Vinson]

I would like to insert some values into a table, but I'm
having trouble adding quotes (") around the string value.

One way is to use the ASCII code: e.g.

strSQL = strSQL & " AND LastName LIKE " & Chr(34) & strLast
strSQL = strSQL & "*" & Chr(34) & " AND..."

Would I also need to add hash marks (#) around the date
entry?

Yes; and the date must be in mm/dd/yyyy format or an unambiguous
format such as dd-mmm-yyyy.

Also, the text box containing the date is formated to
accept a short date (mm/dd/yyyy) ... and an error is
displayed if a date is entered incorrectly, but I would
like to catch this and present a friendlier error
message. What would be the best method to do that?

Rather than using an input mask, I'd suggest appending

Format(CDate(Me!txtDate), "mm/dd/yyyy")

into the SQL string; the user can enter 7/8 or 8 July or however they
like to type dates (just so it's recognizable as a date; and the date
parser is pretty clever).

And last question What is the difference
between 'DoCmd.RunSQL strSQL' and 'CurrentDb.Execute
strSQL, dbFailOnError'

They both work, but the Execute method allows error trapping and seems
to run faster.

 

[Andy Cole]

SME

Try this;

strSQL = strSQL & "VALUES (" & internnumber & ", " & _
Format(newDate, "#\/mm\/dd\/yyyy#") & ", " & _
Chr(34) & newEntry & Chr(34) & ", " & _
Chr(34) & newReferral & Chr(34) & ")"

The Format(..) ensures that SQL will recognise newDate as a date type and
that its in the correct format. Use Chr(34) to delimit your text values

To trap date entry errors use a check for input mask errors in the Form's
OnError event;

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Select Case DataErr
Case 2113 'Entry inappropriate for field mask (invalid dates
like 02/31/2003)
MsgBox "..."
Case 2279 'Entry mask error (not in the required format like
2/7/03
MsgBox "..."
End Select

End Sub

ASAIK, RunSQL displays the messages allowing you to cancel if required.
db.Execute doesn't but allows a trappable error if the dbFailOnError
parameter is used

HTH

Andy