Syntax (1 Viewer)

[Smart]

Not sure if this question should be her but

I am using a stored procedure to update a table
Con.Execute ("Exec sp_Update_People_Root_Cause '" & Me!TxtbehaviourDriver & "','" _
& Me!txtRootID & "','" & Me!Person_Responsible & "','" & Me!DTPicker9 & "','" & Me!cmbLevel_1_ID & "','" _
& Me!CmbLevel_2_ID & "','" & Me!CmbCustContacts & "','" & Me!TxtResponsibletxtBox & "','" & Me!Underlying_Type_ID & "','" _
& Me!ChkMultiplereq & "','" & Me!ChkInDispute & "','" & Me!ChkOther & "' ")

The following paramater Me!Person_Responsible errors because it contains an apostrophe eg martin o'neil
do i need to surround the parameter with a different type of syntax ?

Help please

 

[gemma-the-husky]

i find the easiest way to surround text is with the explicit dble quote char which is chr(34) or chr(39), the single quote i think

hence chr(34) & Me!Person_Responsible & chr(34)
or chr(39) & Me!Person_Responsible & chr(39)

obviously each test will work depending on the embedded character, so the first will prevent martin o'neill giving you a problem. However if its possible that you have BOTH, then i'm not sure.

 

[Smart]

Re Syntax

Con.Execute ("Exec sp_Update_People_Root_Cause '" & Me!TxtbehaviourDriver & "','" _
& Me!txtRootID & "','" & Me!Person_Responsible & "','" & Me!DTPicker9 & "','" & Me!cmbLevel_1_ID & "','" _
& Me!CmbLevel_2_ID & "','" & Me!CmbCustContacts & "','" & Me!TxtResponsibletxtBox & "','" & Me!Underlying_Type_ID & "','" _
& Me!ChkMultiplereq & "','" & Me!ChkInDispute & "','" & Me!ChkOther & "' ")

Sorry for being dim but how would the above look with chr(34) around the me!personsible field

 

[gemma-the-husky]

you need to get rid of your sgl quotes around the person_responsible bit. you can actually use two sets of double quotes to represent a single double quote, but it really gets messy. Instead of nesting single quote within double quotes, you can replace that construct with chr(34)

so its

Me!txtRootID & "'," and chr(34) & Me!Person_Responsible & chr(34) & " ," & Me!DTPicker9 etc

DATES!

now is your dtpicker9 really returning a date ie access date/time datatype because this will also give you a problem. You have to surround a date with # characters.

ie ...... & "#" & dtpicker9 & "#"

you may also have a further problem in that sql tries to impose US dates, whatever your regional settings, so you probably need to format the date to avoid ambiguities, if you are in UK

so the date needs to be
ie ...... & "#" & dformat(dtpicker9,"long date") & "#"

 

[jeremypaule]

i usually see chr(34) as chr$(34) .. is there a difference between the two?

 

[gemma-the-husky]

i dopn't think so - the $ suffix forces a vbnullstring, rather than a null, if applicable. As we have a char it wont matter

i suppose if the char number was a variable (say x) then

chr$(x) would produce a blank string if x was undefined but
chr(x) would produce a null, and the compiler might have trouble with it.

 

[Smart]

syntax

Thanks gemma the huskey
I dont have a problem with the date picker
but pdx man gave me the answer to removing the apostrophe as follows
& Replace(Me!Person_Responsible, "'", "''") &
works like a dream

thanks again for your advice