Re: What is the Proper Syntax for Where Statement? 1

[jhabey01]

Hello,
I have the following docmd statement the I need to set the location Id to 0000002 where the Office is St. Margaret's Center

DoCmd.RunSQL "UPDATE CurrentDay_Template SET CurrentDay_Template.LocationID = '0000002' " _
& "Where (((CurrentDay_Template.GroupNo) = 'CFDS') And ((CurrentDay_Template.[Client Office]) = 'St. Margaret's Center')) "


I get a syntax error missing operator in query expression. The reason I think is becuase of the ' in Margaret's

I can not change the office name.

Thanks

 

[PHV]

And ((CurrentDay_Template.[Client Office]) = 'St. Margaret'[!]'[/!]s Center')) "

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

Do yourself a favor and consider this approach:

Dim strSQL As String

strSQL = "UPDATE CurrentDay_Template " _
& " SET CurrentDay_Template.LocationID = '0000002' " _
& " Where (((CurrentDay_Template.GroupNo) = 'CFDS') " _
& " And ((CurrentDay_Template.[Client Office]) = [blue]Replace([/blue]'St. Margaret's Center'[blue], "'", "''")[/blue])) "
[red]
Desbug.Print strSQL
[/red]
DoCmd.RunSQL strSQL

I use Replace like this any place where there is a possibility of '

This way you can SEE what SQL your DB gets

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[1DMF]

Same as Andy, I use the 'replace' approach dynamically on variables used in a SQL statement that are likely to have an apostrophe, especially forenames, company names and addresses!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music