If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Syntax error in query expression
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 |
#3
|
|||
|
|||
Syntax error in query expression
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 |
Thread Tools | |
Display Modes | |
|
|