how to convert this SQL statement to use in vba?

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I create the following update query and show it in SQL view. I want to copy
this to use in a sub routine and like to change some of the static Text to
variable. Thanks

UPDATE Tbl_Search_Result SET Tbl_Search_Result.ProjectTitleKeywords =
IIf(IsNull(Tbl_Search_Result!ProjectTitleKeywords),"Cancer",Tbl_Search_Result!ProjectTitleKeywords
& "; " & "Cancer")
WHERE (((Tbl_Search_Result.ProjectTitle) Like "*Cancer*"));


"*Cancer*" will become a variable called strSearch
 
Paul;

Dim strSQL as String
strSQL = "xxxxx"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

For the xxxxx, copy / paste the SQL from the query (noting that there is a
single blank space at the end of each of the segments except the last which
ends in a semi-colon).

Works for me!

Bob (@Martureo.Org)
 
Paul said:
I create the following update query and show it in SQL view. I want to copy
this to use in a sub routine and like to change some of the static Text to
variable. Thanks

UPDATE Tbl_Search_Result SET Tbl_Search_Result.ProjectTitleKeywords =
IIf(IsNull(Tbl_Search_Result!ProjectTitleKeywords),"Cancer",Tbl_Search_Result!ProjectTitleKeywords
& "; " & "Cancer")
WHERE (((Tbl_Search_Result.ProjectTitle) Like "*Cancer*"));


"*Cancer*" will become a variable called strSearch
Click to expand...

Dim strSQL as String

strSQL = "UPDATE Tbl_Search_Result " & _
"SET ProjectTitleKeywords = " & _
"IIf(IsNull(ProjectTitleKeywords),"Cancer", ProjectTitleKeywords &
"; " & strSearch) " & _
"WHERE ProjectTitle Like '*" & strSearch & "*';"

CurrentDB.Execute strSQL, dbFailOnError
 
Thank you for the reply. I'm still getting the error when running the
following SQL string in vba.

'Update matching keywords to Tbl_CYSN_Search_Result
strSQL = "UPDATE Tbl_CYSN_Search_Result SET
Tbl_CYSN_Search_Result.ProjectTitleKeywords =
IIf(IsNull(Tbl_CYSN_Search_Result!ProjectTitleKeywords)," &
[rst1!CYSNKeyword] & ",Tbl_CYSN_Search_Result!ProjectTitleKeywords" & ", " &
[rst1!CYSNKeyword] & ") WHERE (((Tbl_CYSN_Search_Result.ProjectTitle) Like "
& strSearch & "));"
DoCmd.RunSQL (strSQL)
The error message says "Syntax error (missing operator) in query expression
'IIF(IsNull(Tbl_CYSN_Search_Result!projectTitleKeywords),*Bielschowsky............


Thanks
 
Back
Top