RunSQL store 2 lines in table (vbCrLf)

[Malcolm17]

Hi,

I am trying to use RunSQL update and I want to use 2 lines for 2 sentences to store. I am trying to use vbCrLf, can I use this in the SQL Line? (eg & vbCrLf &)
This works fine is MgsBox etc.

If not is there another way I can do this please?

Thank you

DoCmd.RunSQL "UPDATE HealthSafetyKeyDates SET UpdateNote = Forms.HealthSafety.cbxFireHeading & ' updated by '  & Forms.Menu.UserDescription & ' on ' & date() & '.' & vbCrLf & Forms.HealthSafety.txtcount & ' used was ' WHERE Description = Forms.HealthSafety.cbxFireHeading"

 

[The_Doc_Man]

So far as I recall, vbCrLf is legal in strings for Access SQL. I recall having used it. Note, however, that not all utility programs use vbCrLf as a line terminator. For some programs, the LF is the terminator. For others, the CR. Still others use an ASCII NUL character as a line-ender. Using the Access constant gives you CR and LF in order. There is a vbCR and a vbLF, and if in doubt, you can ALWAYS use CHR$( number ) to select an ASCII character. Folks use the latter case quite for inserting literal quotes with CHR$(34). So go ahead and include your desired carriage control sequence. Just remember that not all utilities see it in the same way.

 

[jdraw]

Not understanding your requirement. Here is info on Update query.

 

[plog]

 "UPDATE HealthSafetyKeyDates SET UpdateNote = Forms.HealthSafety.cbxFireHeading & ' updated by '  & Forms.Menu.UserDescription & ' on ' & date() & '.' & vbCrLf & Forms.HealthSafety.txtcount & ' used was ' WHERE Description = Forms.HealthSafety.cbxFireHeading"

You are not escaping your string/variables correctly. Once you establish that you are using double quotes to declare your strings, you can't use single quotes to get out of those strings--you need to use double quotes to get out of them.

Var1 ="Plog Rules"
Var2="If I use single quotes inside double quotes and then an ampersand it will literally appear like its typed: ' & Var1 &'"
Var3="To get my value from Var1 to appear inside this I need to use double quotes to end the literal string, then an ampersand. " & Var1 & "."

Var2 does not contain the text 'Plog Rules'. It quite literally contains 2 single quotes, 2 ampersands and the text Var1. You can't go in and out of text strings by mix and hoping to match single and double quotes.

 

[June7]

I did a test using this syntax with RunSQL and it works without the vbCrLf. If you want to use vbCrLf, have to concatenate outside quote marks. Otherwise use:

Chr(13) & Chr(10)

DoCmd.RunSQL "UPDATE HealthSafetyKeyDates SET UpdateNote = " & _
    "Forms.HealthSafety.cbxFireHeading & ' updated by '  & Forms.Menu.UserDescription" & _
    "' on ' & date() & '.' & Chr(13) & Chr(10) & Forms.HealthSafety.txtcount & ' used was '" & _
    " WHERE Description = Forms.HealthSafety.cbxFireHeading"

RunSQL will trigger Warning message. If you want to prevent, use SetWarnings or CurrentDb.Execute. CurrentDb.Execute would require concatenating form references outside quote marks.

 

[jdraw]

I mocked up a table and adjusted an Update query (approximating your example).

vbCrLf was not recognized by the query, but would work with vba.

I did not try using vba which is what your post was using.

Here is my example Update query that updated a record nor did I build forms.
I hope it's useful.

UPDATE HealthSafetyKeyDates SET updateNote =
"mySampleHeading" & " updated by " & " Barb Dwyer " & " on " & date() & '.'
& chr(13) & chr(10) & "250 lines of text " & " used was " WHERE Description =  "sample" ;