trouble with quotation marks... (1 Viewer)

access7

Registered User.
Local time
Today, 11:13
Joined
Mar 15, 2011
Messages
172
Good morning all

I am hoping some can help... I have written the following code which allows a user to save notes on a phone call taken (for a CRM system). There is a 'notes' field (memo) which the user is free to type whatever they want in. I am having major problems if the user decides to use certain characters as they conflict with the SQL, e.g. quotation marks / speech marks etc.

Can anyone give me some help to find away around this?? Someone recommended parameter queries but I am not very familiar with these and dont really know where to start... :banghead:

Example of some code below that is involved in this procedure:-

Sub SaveCorrespondenceOnly()
Dim lsSQL As String
Dim lsFormatDate As String

' saves the correspondence record only - with no linking action
lsFormatDate = "#" & Format(Me.txtDate.Value, "DD-MM-YYYY") & "# "
lsSQL = ""
lsSQL = lsSQL & " INSERT INTO Tbl_Correspondence ( DiaryActionID, CompanyRef, CorrType, Contact, DateofCorr, TimeofCorr, Notes, CreatedBy, CreatedWhen ) "
lsSQL = lsSQL & " VALUES ( " & 0 & ", " & ICompanyRef & ", '" & lstCorrType.Value & "', '" & CboContact.Value & "', " & lsFormatDate & ", #" & txtTime.Value & "#,'" & Replace(txtNotes.Value, "'", "`") & "', '" & SUser & "', #" & Now() & "#)"

CurrentDb.Execute lsSQL

 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:13
Joined
Feb 19, 2013
Messages
16,553
You haven't said what your destination table datatypes are which could affect this solution so I've made the following assumptions:

DiaryActionID - is an autonumber field
CompanyRef - text
CorrType - numeric
Contact - text
DateofCorr - Date - if it is actually text, then use your isformatdate function as I've corrected and remove the # either side in the query
Timeof Corr -Date
Notes - Memo
CreatedBy - text
CreatedWhen - Date

Note I have taken out DiaryActionID since this is autocompleted. You also do not need to refer to values.

Also, if you set the default value of your CreateWhen field to now() then you do not need to update it in this query.

I am also assuming that your txtDate and TxtTime fields have date and time inputmasks respectively

Code:
lsFormatDate ="'" & Format(Me.txtDate.Value, "DD-MM-YYYY") & "'"
lsSQL = ""
lsSQL = lsSQL & " INSERT INTO Tbl_Correspondence ( CompanyRef, CorrType, Contact, DateofCorr, TimeofCorr, Notes, CreatedBy, CreatedWhen ) "
lsSQL = lsSQL & " VALUES ('" & ICompanyRef & "', " & lstCorrType & ", '" & CboContact & "', #" & format(txtdate,"mmddyyyy") & "#, #" & txtTime & "#,'" & Replace(txtNotes.Value, "'", "`") & "', '" & SUser & "', " & Now())"

Note: When inserting dates, Access always requires the US form of mm/dd/yyyy so if you are using a different format (UK or whatever) you need to reformat into the US style.
 

access7

Registered User.
Local time
Today, 11:13
Joined
Mar 15, 2011
Messages
172
Sorry, I should have been a bit clearer. Datatypes are as follows:

CorrRef - Autonumber
DiaryID - Number
CompanyRef - Number
CompanyType - Text
Contact - Text
Date - Date/Time
Time - Date/Time
Notes - memo
Createdby - text
CreatedWhen - Date/Time
 

MarkK

bit cruncher
Local time
Today, 04:13
Joined
Mar 17, 2004
Messages
8,178
Parameter query is best and would work like this ...
Code:
Sub SaveCorrespondenceOnly()
    Dim qdf As DAO.QueryDef
    
[COLOR="Green"]    'here we create a temp QueryDef where the unknown fields p0, p1, etc 
    'are assumed by the SQL parser to be parameters[/COLOR]
    Set qdf = CurrentDb.CreateQueryDef("", _
        "INSERT INTO tblCorrespondence " & _
            "( DiaryActionID, CompanyRef, CorrType, Contact, DateofCorr, _
               TimeofCorr, Notes, CreatedBy, CreatedWhen ) " & _
        "VALUES " & _
            "( p0, p1, p2, p3, p4, p5, p6, p7, p8 )")
[COLOR="Green"]    'here we assign values to the parameters, note: NO Delimiters!!![/COLOR]
    With qdf
        .Parameters(0) = 0
        .Parameters(1) = ICompanyRef
        .Parameters(2) = Me.lstCorrType
        .Parameters(3) = Me.cboContact
        .Parameters(4) = lsFormatDate
        .Parameters(5) = Me.txtTime
        .Parameters(6) = txtNotes
        .Parameters(7) = SUser
        .Parameters(8) = Now()
        
        .Execute dbFailOnError  [COLOR="Green"]'here we execute the query[/COLOR]
        .Close
    End With
End Sub
This approach is a little more typing maybe, but it's much more reliable, much more readable, much easier to maintain--so come back to it six months from now because business rules have changed--this will be easier to work with, but most of all: delimiters are handled automatically and string can contain both single and double quotes, no problem.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:13
Joined
Feb 19, 2013
Messages
16,553
Don't disagee with you Lagbolt!

access7, you have given me

CorrRef - Autonumber
DiaryID - Number
CompanyRef - Number
CompanyType - Text
Contact - Text
Date - Date/Time
Time - Date/Time
Notes - memo
Createdby - text
CreatedWhen - Date/Time

but your query has

INSERT INTO Tbl_Correspondence ( DiaryActionID, CompanyRef, CorrRef, Contact, DateofCorr, TimeofCorr, Notes, CreatedBy, CreatedWhen )

Which is correct? Just need to check
 

Users who are viewing this thread

Top Bottom