Syntax error in query expression

This is probably not the right newgroup for this question except that
I click a button on a form to get into trouble here...and you've been
so helpful in the past that I hope someone can help me now, or direct
me to a better newsgroup.

I have a form that lists all 50 states as checkboxes. They have a Tag
property of "State". The form includes a project number, and I test
each state to determine if there is an activity (a filing) has been
performed for that state and project number. If a box is checked but
no filing has been made, I put a solid border around the label that
shows the state name.

What I want to do now is give the users a button to click to create a
record for tblFilings if the state name has a solid border. I have
some example code from a data base someone else made, but I'm not sure
how good it is, and there are some differences.

Here is my code:
~~~~~~~~~~~~~~~
Dim ctl As Control
Dim newTitle As String, newLOB As String, newFilingType As String
Dim newState As String, newAnalyst As String, newPropDate As Date
Dim newProjectID As Long, strSQL As String

newProjectID = Me.ProjectID
newTitle = Me.strTitle
newLOB = Me.strLOBDescription
newFilingType = Me.strFilingType
newPropDate = Me.dteProposedEff

For Each ctl In Me.Controls

If ctl.Tag = "State" Then
Select Case ctl.ControlType
Case acLabel

'BorderStyle=1: no filing yet exists for same ProjectID and
state
If ctl.BorderStyle = 1 Then

'Extract state in form XX from control name in form
ctlXX_Label
newState = Mid(ctl.Name, 4, 2)

'Selection of analyst depends on state and LOB
newAnalyst = DLookup("[strAnalyst]", "qryAnalystMatrix", _
"[strLOBDescription] = '" & _
Me.strLOBDescription & "' AND [State] = '" & newState
& "'")

strSQL = "INSERT INTO tblFilings ([ProjectID], [strTitle],
[strLOBDescription], [strState], [strFilingType], [strAnalyst],
[dteISOProposedEff]) " & _
"VALUES (" & newProjectID & ",'" & newTitle & "','" &
_
newLOB & "','" & newState & "','" & newFilingType & _
"','" & newAnalyst & "','" & newPropDate & "')"

MsgBox strSQL

CurrentDb.Execute strSQL

With ctl
.BorderStyle = 0
.BorderColor = 0
.BorderWidth = 0
End With

End If

End Select

End If
Next ctl
~~~~~~~~~~~~~~~~~

my MsgBox informs me that my strSQL =

INSERT INTO tblFilings ([ProjectID], [strTitle], [strLOBDescription],
[strState], [strFilingType], [strAnalyst], [dteISOProposedEff]) VALUES
(648,'Multistate rules and forms revision Add'l Insd.','General
Liability','TX','Rule / Form','Smith,Jane','6/1/2005')

I get an error that states:
Syntax error (missing operator) in query expression "Multistate rules
and forms revision Add'l Insd.','General liability','TX','Rule /
Form','Smith',

I suspect that the problem might come from either (or both) the
apostrophy in the string title (Add'l) or the comma in the strAnalyst
(Smith,Jane) .

There are also two fields that aren't strings: ProjectID, which is
being sent with no apostrophies and newPropDate which is being sent
like a string but it's actually a date.

I am also not sure how the "CurrentDb.Execute strSQL" fits in. I
pulled it from similar code in another data base where it seems to
work, but if my research is correct, I should be using "DoCmd.RunSQL
strSQL".

I have changed and tweaked everything I can think of, but am still
getting the error messages. I would greatly appreciate any help or
suggestions anyone has to offer.

Thanks!
Alice

The problem is that you're using single quotes as the text delimiter, and
you've got an apostrophe in Multistate rules and forms revision Add'l Insd.

Try:

strSQL = "INSERT INTO tblFilings ([ProjectID], [strTitle], " & _
"[strLOBDescription], [strState], [strFilingType], [strAnalyst], " & _
"[dteISOProposedEff]) " & _
"VALUES (" & newProjectID & ",'" & Replace(newTitle, "'", "''") & _
"','" &Replace(newLOB, "'", "''") & "','" & Replace(newState, "'", "''")
& _
"','" & Replace(newFilingType, "'", "''") & "','" & _
Replace(newAnalyst, "'", "''") & "','" & newPropDate & "')"

Is dteISOProposedEff a date field, or is it a text field? If it's a date
field, that last line should be

Replace(newAnalyst, "'", "''") & "'," & _
Format(newPropDate, "\#yyyy\-mm\-dd\#") & ")"

Just for clarity, each of those Replace statements is

Replace(VariableName, " ' ", " ' ' " )

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"night_writer" wrote in message
...
This is probably not the right newgroup for this question except that
I click a button on a form to get into trouble here...and you've been
so helpful in the past that I hope someone can help me now, or direct
me to a better newsgroup.

I have a form that lists all 50 states as checkboxes. They have a Tag
property of "State". The form includes a project number, and I test
each state to determine if there is an activity (a filing) has been
performed for that state and project number. If a box is checked but
no filing has been made, I put a solid border around the label that
shows the state name.

What I want to do now is give the users a button to click to create a
record for tblFilings if the state name has a solid border. I have
some example code from a data base someone else made, but I'm not sure
how good it is, and there are some differences.

Here is my code:
~~~~~~~~~~~~~~~
Dim ctl As Control
Dim newTitle As String, newLOB As String, newFilingType As String
Dim newState As String, newAnalyst As String, newPropDate As Date
Dim newProjectID As Long, strSQL As String

newProjectID = Me.ProjectID
newTitle = Me.strTitle
newLOB = Me.strLOBDescription
newFilingType = Me.strFilingType
newPropDate = Me.dteProposedEff

For Each ctl In Me.Controls

If ctl.Tag = "State" Then
Select Case ctl.ControlType
Case acLabel

'BorderStyle=1: no filing yet exists for same ProjectID and
state
If ctl.BorderStyle = 1 Then

'Extract state in form XX from control name in form
ctlXX_Label
newState = Mid(ctl.Name, 4, 2)

'Selection of analyst depends on state and LOB
newAnalyst = DLookup("[strAnalyst]", "qryAnalystMatrix", _
"[strLOBDescription] = '" & _
Me.strLOBDescription & "' AND [State] = '" & newState
& "'")

strSQL = "INSERT INTO tblFilings ([ProjectID], [strTitle],
[strLOBDescription], [strState], [strFilingType], [strAnalyst],
[dteISOProposedEff]) " & _
"VALUES (" & newProjectID & ",'" & newTitle & "','" &
_
newLOB & "','" & newState & "','" & newFilingType & _
"','" & newAnalyst & "','" & newPropDate & "')"

MsgBox strSQL

CurrentDb.Execute strSQL

With ctl
.BorderStyle = 0
.BorderColor = 0
.BorderWidth = 0
End With

End If

End Select

End If
Next ctl
~~~~~~~~~~~~~~~~~

my MsgBox informs me that my strSQL =

INSERT INTO tblFilings ([ProjectID], [strTitle], [strLOBDescription],
[strState], [strFilingType], [strAnalyst], [dteISOProposedEff]) VALUES
(648,'Multistate rules and forms revision Add'l Insd.','General
Liability','TX','Rule / Form','Smith,Jane','6/1/2005')

I get an error that states:
Syntax error (missing operator) in query expression "Multistate rules
and forms revision Add'l Insd.','General liability','TX','Rule /
Form','Smith',

I suspect that the problem might come from either (or both) the
apostrophy in the string title (Add'l) or the comma in the strAnalyst
(Smith,Jane) .

There are also two fields that aren't strings: ProjectID, which is
being sent with no apostrophies and newPropDate which is being sent
like a string but it's actually a date.

I am also not sure how the "CurrentDb.Execute strSQL" fits in. I
pulled it from similar code in another data base where it seems to
work, but if my research is correct, I should be using "DoCmd.RunSQL
strSQL".

I have changed and tweaked everything I can think of, but am still
getting the error messages. I would greatly appreciate any help or
suggestions anyone has to offer.

Thanks!
Alice

On Nov 17, 4:42*pm, "Douglas J. Steele"
wrote:
The problem is that you're using single quotes as the text delimiter, and
you've got an apostrophe in Multistate rules and forms revision Add'l Insd.

Try:

* strSQL = "INSERT INTO tblFilings ([ProjectID], [strTitle], " & _
* * "[strLOBDescription], [strState], [strFilingType], [strAnalyst], " & _
* * "[dteISOProposedEff]) " & _
* * "VALUES (" & newProjectID & ",'" & Replace(newTitle, "'", "''") & _
* * "','" &Replace(newLOB, "'", "''") & "','" & Replace(newState, "'", "''")
& _
* * "','" & Replace(newFilingType, "'", "''") & "','" & _
* * Replace(newAnalyst, "'", "''") & "','" & newPropDate & "')"

Is dteISOProposedEff a date field, or is it a text field? If it's a date
field, that last line should be

* * Replace(newAnalyst, "'", "''") & "'," & _
* * Format(newPropDate, "\#yyyy\-mm\-dd\#") & ")"

Just for clarity, each of those Replace statements is

* Replace(VariableName, " ' ", " ' ' " )

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

"night_writer" wrote in message

...



This is probably not the right newgroup for this question except that
I click a button on a form to get into trouble here...and you've been
so helpful in the past that I hope someone can help me now, or direct
me to a better newsgroup.

I have a form that lists all 50 states as checkboxes. They have a Tag
property of "State". The form includes a project number, and I test
each state to determine if there is an activity (a filing) has been
performed for that state and project number. If a box is checked but
no filing has been made, I put a solid border around the label that
shows the state name.

What I want to do now is give the users a button to click to create a
record for tblFilings if the state name has a solid border. I have
some example code from a data base someone else made, but I'm not sure
how good it is, and there are some differences.

Here is my code:
~~~~~~~~~~~~~~~
Dim ctl As Control
Dim newTitle As String, newLOB As String, newFilingType As String
Dim newState As String, newAnalyst As String, newPropDate As Date
Dim newProjectID As Long, strSQL As String

newProjectID = Me.ProjectID
newTitle = Me.strTitle
newLOB = Me.strLOBDescription
newFilingType = Me.strFilingType
newPropDate = Me.dteProposedEff

For Each ctl In Me.Controls

If ctl.Tag = "State" Then
* *Select Case ctl.ControlType
* * * *Case acLabel

* * * *'BorderStyle=1: no filing yet exists for same ProjectID and
state
* * * *If ctl.BorderStyle = 1 Then

* * * * * *'Extract state in form XX from control name in form
ctlXX_Label
* * * * * *newState = Mid(ctl.Name, 4, 2)

* * * * * *'Selection of analyst depends on state and LOB
* * * * * *newAnalyst = DLookup("[strAnalyst]", "qryAnalystMatrix", _
* * * * * * * *"[strLOBDescription] = '" & _
* * * * * * * *Me.strLOBDescription & "' AND [State] = '" & newState
& "'")

* * * * * *strSQL = "INSERT INTO tblFilings ([ProjectID], [strTitle],
[strLOBDescription], [strState], [strFilingType], [strAnalyst],
[dteISOProposedEff]) " & _
* * * * * * * *"VALUES (" & newProjectID & ",'" & newTitle & "','" &
_
* * * * * * * *newLOB & "','" & newState & "','" & newFilingType & _
* * * * * * * *"','" & newAnalyst & "','" & newPropDate & "')"

* * * * * *MsgBox strSQL

* * * * * *CurrentDb.Execute strSQL

* * * * * *With ctl
* * * * * *.BorderStyle = 0
* * * * * *.BorderColor = 0
* * * * * *.BorderWidth = 0
* * * * * *End With

* * * *End If

* *End Select

End If
Next ctl
~~~~~~~~~~~~~~~~~

my MsgBox informs me that my strSQL =

INSERT INTO tblFilings ([ProjectID], [strTitle], [strLOBDescription],
[strState], [strFilingType], [strAnalyst], [dteISOProposedEff]) VALUES
(648,'Multistate rules and forms revision Add'l Insd.','General
Liability','TX','Rule / Form','Smith,Jane','6/1/2005')

I get an error that states:
Syntax error (missing operator) in query expression "Multistate rules
and forms revision Add'l Insd.','General liability','TX','Rule /
Form','Smith',

I suspect that the problem might come from either (or both) the
apostrophy in the string title (Add'l) or the comma in the strAnalyst
(Smith,Jane) .

There are also two fields that aren't strings: ProjectID, which is
being sent with no apostrophies and newPropDate which is being sent
like a string but it's actually a date.

I am also not sure how the "CurrentDb.Execute strSQL" fits in. I
pulled it from similar code in another data base where it seems to
work, but if my research is correct, I should be using "DoCmd.RunSQL
strSQL".

I have changed and tweaked everything I can think of, but am still
getting the error messages. I would greatly appreciate any help or
suggestions anyone has to offer.

Thanks!
Alice- Hide quoted text -

- Show quoted text -

Thank you very much! The "replace" took care of my title problem. I
need to spend a little more time with the date, but this is a huge
step forward. Thanks again!

Alice