Query Nightmare!!!

  • Thread starter Thread starter Vivista Eastbourne
  • Start date Start date
V

Vivista Eastbourne

I have the following query!!!

STRSQL1 = "UPDATE tblMailingList SET tblMailingList.SelectForPrint = -1,
tblMailingList.LetrefNotes = " & "LetrefNotes " & " & Now() " & vbCrLf & "
WHERE (((tblMailingList.SelectForView)=-1));"

for some reason, the vbCrLf does not create a carriage return, although no
errors are passed on

also, how and where could I add the text "Added on " as every time I try to
add the text I get an error

Many Thanks
Andy
 
Vivista,

I'm not sure vbCrLf will work in SQL; it is a VBA constant as it begins with
the letters "vb" and will probably only work in pure VBA code.

Be that as it may, though, you need to use '' (that is, two single quotes)
and ## to delineate string and date data in a SQL string. Hence, change your
string to

STRSQL1 = "UPDATE tblMailingList SET tblMailingList.SelectForPrint = -1,
tblMailingList.LetrefNotes = '" & LetrefNotes & "' #" & Now() &"#" & vbCrLf
& "
WHERE (((tblMailingList.SelectForView)=-1));"

I suggest you copy-and-paste the above in your Immediate window in order to
display all the characters that were added and to see where characters were
deleted as well.

Sam
 
Also, you can try the words "Added on" in the same way and try it out.

Sam
Vivista,

I'm not sure vbCrLf will work in SQL; it is a VBA constant as it begins with
the letters "vb" and will probably only work in pure VBA code.

Be that as it may, though, you need to use '' (that is, two single quotes)
and ## to delineate string and date data in a SQL string. Hence, change your
string to

STRSQL1 = "UPDATE tblMailingList SET tblMailingList.SelectForPrint = -1,
tblMailingList.LetrefNotes = '" & LetrefNotes & "' #" & Now() &"#" & vbCrLf
& "
WHERE (((tblMailingList.SelectForView)=-1));"

I suggest you copy-and-paste the above in your Immediate window in order to
display all the characters that were added and to see where characters were
deleted as well.

Sam
I have the following query!!!
Click to expand...
[quoted text clipped - 10 lines]
Many Thanks
Andy
Click to expand...
Click to expand...
 
You will need to use Chr(13) & Chr(10)

I think what you are trying to do is

STRSQL1 = "UPDATE tblMailingList " & _
"SET tblMailingList.SelectForPrint = -1, " & _
"tblMailingList.LetrefNotes = [LetrefNotes] & Now() & Chr(13) & Chr(10)
& """Added On"" " & _
" WHERE tblMailingList.SelectForView=-1"
 
Back
Top