update query Memo Field Question

[travismp]

I have a field [InternalComments] which is a memo field. Lots of data per customer

Can I make an update query to add data to the existing data without overriding the data currenty there?

Thanks

 

[pbaldy]

Sure:

SET FieldName = FieldName & " New stuff"

 

[travismp]

"SET Internal Comments"="Internal Comments" & " 2nd Request Sent"

Like this then?

 

[travismp]

Also is there a way to add a return ahead of that so it will get its own line?

Thanks so far.

 

[travismp]

[Internal Comments] & Date() & " 2nd Request Letter Sent"

This works well. The only thing I would like to add is a return line before the date is added. Can this be done? Thanks.

 

[boblarson]

[Internal Comments] & Date() & " 2nd Request Letter Sent"

This works well. The only thing I would like to add is a return line before the date is added. Can this be done? Thanks.

Add a

CHR(10) & CHR(13)

to give you a line break and carriage return

 

[travismp]

[Internal Comments] & Chr(13) & Chr(10) & Date() & " 2ND REQUEST LETTER SENT"

My final working code. THANK YOU SIR!

 

[boblarson]

Another couple to add to the "to remember list"

Chr(34) = "
Chr(9) = Tab

 

[travismp]

Chr(9) = Tab
I can really use this one. Very good to know, can use right away!


Chr(34) = "
What would someone use this for? I cannot see how this is helpful.

 

[boblarson]

Chr(34) = "
What would someone use this for? I cannot see how this is helpful.

Let's say you want an embedded quotation mark within a string you are passing. If you try to use:

strMyVariable = "Give me a "Break""

You will get an error because it only expects to see two " one at the beginning of the string and one at the end.

So, using:

strMyVariable = "Give me a " & Chr(34) & "Break" & Chr(34)

will do it for you.

 

[travismp]

I see. I am not ready for that yet, but still good to know. You are a life saver.

 

[Bigmo2u]

I know it's old

This is kind of the same problem I am having. I have been searching here and the web as this is as close as I have gotten to my problem.

I have memo field that is Called DN_Reason and i want to add text/comment to the end of the memo field. I have this attached to a button. here is what I have so far:

Private Sub cmdWebsite_Click()
Dim strSql As String

strSql = "UPDATE tblDNclaims SET DN_Reason = "DN_Reason" & "https://www.website.com/specificpage" _
& "WHERE ClaimNo = " & ClaimNo.Text & ""

DoCmd.RunSQL strSql

Can someone please help me.

Thank you in advance.

 

[pbaldy]

Try

strSql = "UPDATE tblDNclaims SET DN_Reason = DN_Reason & "https://www.website.com/specificpage" _
& " WHERE ClaimNo = " & ClaimNo.Text & ""

Though I suspect you'll get an error referring to the .Text property, which you don't need in this case.

 

[Bigmo2u]

where is the .Text at? I am not see it.

 

[pbaldy]

& " WHERE ClaimNo = " & ClaimNo.Text & ""

 

[gemma-the-husky]

instead of remembering chr(13) and chr(10) etc, vba wey kindly has constants

vbcrlf for cr + lf
vbtab for a tab

i often save the user and date with my notes in memo so i get

mymemo = mymemo & vbcrlf & "By: " & currentuser & " On: " & date & " " & newnotetext

 

[Bigmo2u]

here is what the code is now:

Private Sub cmdWebsite_Click()

Dim strSql As String
Dim g As String

g = Me.DN_Reason & " https://www.website.com/SpecificPage"

strSql = "UPDATE tblDNclaims SET DN_Reason = g" & "WHERE ClaimNo = " & ClaimNo.Text & ""

DoCmd.RunSQL strSql

End Sub

There error code is as follows:

Run time error 2185

you can't reference a property or method for a control unless the control has the focus.

I have tried to set the focus on the ClaimNo, but it tells me I can't do that.

when I hoover over g in the SQL statement all the info is there and the WHERE ClaimNo = " & ClaimNo.Text & "" holds the right information, but it will not execute.

 

[pbaldy]

I already told you it was going to error and to drop the .Text. You're also going to have a problem with no space between your string and the word WHERE. You must not have noticed that I added one in there.

 

[Bigmo2u]

& " WHERE ClaimNo = " & ClaimNo.Text & ""

Sorry about that. I have put it back to way you had it, but it was giving me a problem with "http://" so i changed it to a string.

code is as follows:

Private Sub cmdWebsite_Click()

Dim strSql As String
Dim g As String

g = " https://www.website.com/SpecificPage"

strSql = "UPDATE tblDNclaims SET DN_Reason = DN_Reason + g" _
& " WHERE ClaimNo = " & ClaimNo & """"


DoCmd.RunSQL strSql

End Sub

when hooving over everything, everything gives me what it should be holding it, but executing is still an issue.

error code: 3075

Syntax error in string in Query expression 'ClaimNo = #####'

#### is acually a number....

Pbaldy - I know I am driving you crazy, but thank you for your help...

 

[pbaldy]

I see 2 problems. First, you have to concatenate your variable, as it's only known to VBA. Second, you don't need all those quotes after the ClaimNo, presuming it's a numeric value. Try this:

strSql = "UPDATE tblDNclaims SET DN_Reason = DN_Reason & '" & g _
& "' WHERE ClaimNo = " & ClaimNo