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
|
|||
|
|||
insert contents with ',' in it
Tried to duplicate the contents of a field from one table to another in an
update statement. However, due to the contents has a ','. Hence I got the runtime error 3075 Syntax eror (misssing operator) in query expression. The field is of type 'memo' in both table. When certain condition met, I copy the contents of the field from one table and duplicate it on another table. BUT how do I over comes the ',' problem in the field. |
#2
|
|||
|
|||
insert contents with ',' in it
What process are you using?
-- KARL DEWEY Build a little - Test a little "Smiley" wrote: Tried to duplicate the contents of a field from one table to another in an update statement. However, due to the contents has a ','. Hence I got the runtime error 3075 Syntax eror (misssing operator) in query expression. The field is of type 'memo' in both table. When certain condition met, I copy the contents of the field from one table and duplicate it on another table. BUT how do I over comes the ',' problem in the field. |
#3
|
|||
|
|||
insert contents with ',' in it
On Thu, 20 Dec 2007 17:12:57 -0000, "Smiley"
wrote: Tried to duplicate the contents of a field from one table to another in an update statement. However, due to the contents has a ','. Hence I got the runtime error 3075 Syntax eror (misssing operator) in query expression. The field is of type 'memo' in both table. When certain condition met, I copy the contents of the field from one table and duplicate it on another table. BUT how do I over comes the ',' problem in the field. Enlose the text string in quote marks. You need to do so anyway! Perhaps you could post the SQL of your query. John W. Vinson [MVP] |
#4
|
|||
|
|||
insert contents with ',' in it
On Dec 20, 11:42 am, John W. Vinson
wrote: On Thu, 20 Dec 2007 17:12:57 -0000, "Smiley" wrote: Tried to duplicate the contents of a field from one table to another in an update statement. However, due to the contents has a ','. Hence I got the runtime error 3075 Syntax eror (misssing operator) in query expression. The field is of type 'memo' in both table. When certain condition met, I copy the contents of the field from one table and duplicate it on another table. BUT how do I over comes the ',' problem in the field. Enlose the text string in quote marks. You need to do so anyway! Perhaps you could post the SQL of your query. John W. Vinson [MVP] I agree with John - also you need some sort of wrapper function that handles the unholiness of ' and ", or alternatively, do it the good ol' recordset way that will handle all this badness. -- James |
#5
|
|||
|
|||
insert contents with ',' in it
Hi there,
I don't think my basic query has any problem. Anyway here they are : 'DoCmd.RunSQL "INSERT INTO TProjectItems (ProjectID, ItemID, Unit, Qty, Item) values (Forms!FProjectItems.ProjectID," & Me!ItemID & ", tUnit, tQty, tItem);" DoCmd.RunSQL "INSERT INTO TProjectItems (ProjectID, ItemID, itemdesc, Unit, Qty, Item) values (Forms!FProjectItems.ProjectID," & Me!ItemID & "," & Me!ItemDesc & ", tUnit, tQty, tItem);" xtemp = DLookup("[ProjItemID]", "TProjectitems", "[ProjectID] = '" & Forms!FProjectItems!ProjectID & "' AND [ItemID] =" & Me!ItemID) DoCmd.RunSQL "UPDATE TProjectItems SET ItemDesc=" & Me!ItemDesc & " WHERE ProjectItemID = " & xtemp The first docmd was comment out, if you carry on reading you will know what I was doing. The next one was the one which I want to do but getting the run time error as the field ItemDesc is a memo field from Product table and the content was like "the plant, pot, flower etc are to be in the shed. The corner, edges are to be in far end of garden.". Since the content contains , which cause the problem. Next, I commented out the 2nd docmd and uncomment the first one, changed the ItemDesc field to text and run the 1st, 3rd and 4th line of statement and still getting the same error. Is there anyway which I would add this field into table TProjectItems. Also any other way to get the last record autonumber. I don't know how to get the last used autonumber so I used the Dlookup to get it for the UPDATE statement above. Anyone would give this as my X'mas presie ? "Minton M" wrote in message ... On Dec 20, 11:42 am, John W. Vinson wrote: On Thu, 20 Dec 2007 17:12:57 -0000, "Smiley" wrote: Tried to duplicate the contents of a field from one table to another in an update statement. However, due to the contents has a ','. Hence I got the runtime error 3075 Syntax eror (misssing operator) in query expression. The field is of type 'memo' in both table. When certain condition met, I copy the contents of the field from one table and duplicate it on another table. BUT how do I over comes the ',' problem in the field. Enlose the text string in quote marks. You need to do so anyway! Perhaps you could post the SQL of your query. John W. Vinson [MVP] I agree with John - also you need some sort of wrapper function that handles the unholiness of ' and ", or alternatively, do it the good ol' recordset way that will handle all this badness. -- James |
#6
|
|||
|
|||
insert contents with ',' in it
On Fri, 21 Dec 2007 12:44:40 -0000, "Smiley"
wrote: The first docmd was comment out, if you carry on reading you will know what I was doing. The next one was the one which I want to do but getting the run time error as the field ItemDesc is a memo field from Product table and the content was like "the plant, pot, flower etc are to be in the shed. The corner, edges are to be in far end of garden.". Since the content contains , which cause the problem. The solution to the problem is to incorporate " delimiters surrounding the ItemDesc field value that you're inserting. That's required ANYWAY for inserting a text value into a Text or Memo field, with or without commas! Try changing the second DoCmd line to DoCmd.RunSQL "INSERT INTO TProjectItems (ProjectID, ItemID, itemdesc, Unit, Qty, Item) values (Forms!FProjectItems.ProjectID," & Me!ItemID & ",""" & Me!ItemDesc & """, tUnit, tQty, tItem);" The triple-doublequotes are there to put two doublequotes inside the doublequote delimited string; a double doublequote is seen as a doublequote (now how's THAT for doubletalk!) John W. Vinson [MVP] |
#7
|
|||
|
|||
insert contents with ',' in it
Hi there,
Wishing you all a happy new year and hoped you had a relaxing holidays. Thanks for your advises. Here are the sql 1. DoCmd.RunSQL "INSERT INTO TProjectItems (ProjectID, ItemID, Unit, Qty, Item) values (Forms!FProjectItems.ProjectID," & Me!ItemID & ", tUnit, tQty, tItem);" 2. 'DoCmd.RunSQL "INSERT INTO TProjectItems (ProjectID, ItemID, itemdesc, Unit, Qty, Item) values (Forms!FProjectItems.ProjectID," & Me!ItemID & "," & Me!ItemDesc & ", tUnit, tQty, tItem);" 3. xtemp = DLookup("[ProjItemID]", "TProjectitems", "[ProjectID] = '" & Forms!FProjectItems!ProjectID & "' AND [ItemID] =" & Me!ItemID) DoCmd.RunSQL "UPDATE TProjectItems SET ItemDesc=" & Me!ItemDesc & " WHERE ProjectItemID = " & xtemp Else 4. xtemp = DLookup("[ProjItemID]", "TProjectitems", "[ProjectID] = '" & Forms!FProjectItems!ProjectID & "' AND [ItemID] =" & Me!ItemID) DoCmd.RunSQL "DELETE * FROM TProjectitems WHERE ProjectID = '" & Forms!FProjectItems.ProjectID & "' AND [ItemID]=" & Me!ItemID End If I have labelled the line as 1 - 4 Originally I only has line 2 but due to the error which I encountered. I tried the line 1 together with line 3 and 4 but what ever way I tried, once it comes to insert the description which inadvertly has ',' or '.' in the contents and those ',' and '.' could occurred more then once. Look forward to hear from your advice "Minton M" wrote in message ... On Dec 20, 11:42 am, John W. Vinson wrote: On Thu, 20 Dec 2007 17:12:57 -0000, "Smiley" wrote: Tried to duplicate the contents of a field from one table to another in an update statement. However, due to the contents has a ','. Hence I got the runtime error 3075 Syntax eror (misssing operator) in query expression. The field is of type 'memo' in both table. When certain condition met, I copy the contents of the field from one table and duplicate it on another table. BUT how do I over comes the ',' problem in the field. Enlose the text string in quote marks. You need to do so anyway! Perhaps you could post the SQL of your query. John W. Vinson [MVP] I agree with John - also you need some sort of wrapper function that handles the unholiness of ' and ", or alternatively, do it the good ol' recordset way that will handle all this badness. -- James |
#8
|
|||
|
|||
insert contents with ',' in it
On Mon, 7 Jan 2008 09:10:58 -0000, "Smiley"
wrote: Hi there, Wishing you all a happy new year and hoped you had a relaxing holidays. Thanks for your advises. Here are the sql 1. DoCmd.RunSQL "INSERT INTO TProjectItems (ProjectID, ItemID, Unit, Qty, Item) values (Forms!FProjectItems.ProjectID," & Me!ItemID & ", tUnit, tQty, tItem);" 2. 'DoCmd.RunSQL "INSERT INTO TProjectItems (ProjectID, ItemID, itemdesc, Unit, Qty, Item) values (Forms!FProjectItems.ProjectID," & Me!ItemID & "," & Me!ItemDesc & ", tUnit, tQty, tItem);" 3. xtemp = DLookup("[ProjItemID]", "TProjectitems", "[ProjectID] = '" & Forms!FProjectItems!ProjectID & "' AND [ItemID] =" & Me!ItemID) DoCmd.RunSQL "UPDATE TProjectItems SET ItemDesc=" & Me!ItemDesc & " WHERE ProjectItemID = " & xtemp Else 4. xtemp = DLookup("[ProjItemID]", "TProjectitems", "[ProjectID] = '" & Forms!FProjectItems!ProjectID & "' AND [ItemID] =" & Me!ItemID) DoCmd.RunSQL "DELETE * FROM TProjectitems WHERE ProjectID = '" & Forms!FProjectItems.ProjectID & "' AND [ItemID]=" & Me!ItemID End If AGAIN: If you try to insert any literal text at all into a field of Text or Memo datatype, what you're inserting must be - NO OPTIONS! - delimited, either with ' or " as a text string delimiter. It doesn't matter whether the text you're inserting contains commas; if it contains ' you can use " as the delimiter; if it contains " you can use ' as the delimiter; or you can put two consecutive " marks within the string delimited by " in order to insert just one. You also need to include the syntactically required commas and other punctuation as text strings in building your SQL string. You're freely mixing VBA variables (tUnit, tQty, tItem) and SQL - that will NOT work, since the SQL engine has no way to see the values of these variables. Just as an example, you could change (2) to Dim strSQL As String strSQL = "INSERT INTO TProjectItems (ProjectID, ItemID, " _ & "itemdesc, Unit, Qty, Item) Values (Forms!FProjectItems.ProjectID," _ & Me!ItemID & ",'" & Me!ItemDesc & "', '" & tUnit & "'," & tQty & ","_ & tItem & ");" DoCmd.RunSQL strSQL If Me!ItemDesc contains the text "Lions, Tigers and Bears, oh my!" the resulting SQL statement will resemble INSERT INTO tProjectItems (ProjectID, ItemID, itemdesc, Unit, Qty, Item) VALUES (Forms!FProjectItems.ProjectID,251,'Lions, Tigers and Bears, oh my!','Each', 1, 2545); Setting the value of a SQL string will help in debugging as you can actually look at the SQL command which will be executed. You have not (as requested) indicated the datatypes of these fields, but I'm guessing that ItemDesc is a text or memo field. Any others? Which control contains the commas causing the problem? And... more basically - WHY are you doing this the hard way? Any major objections to just using a bound form, with no code at all? I admit, sometimes you must, and sometimes it's better - but it's not obvious in this case that you gain anything by programmatically doing what Access does for you. John W. Vinson [MVP] |
#9
|
|||
|
|||
insert contents with ',' in it
Hi there,
thank you for the respond. It was user requirement that certain 'field' can be changed. I have a table which is a 'template' and then user would changed the values or contents of various fields. Yes, you are right, the ItemDesc is a memo field. As it is free text and using the max 255 in a text field may not be enough hence memo. Any other way that I would achieve the 'free text' input ? I need to digest what you have wrrten and will get back to you. "John W. Vinson" wrote in message ... On Mon, 7 Jan 2008 09:10:58 -0000, "Smiley" wrote: Hi there, Wishing you all a happy new year and hoped you had a relaxing holidays. Thanks for your advises. Here are the sql 1. DoCmd.RunSQL "INSERT INTO TProjectItems (ProjectID, ItemID, Unit, Qty, Item) values (Forms!FProjectItems.ProjectID," & Me!ItemID & ", tUnit, tQty, tItem);" 2. 'DoCmd.RunSQL "INSERT INTO TProjectItems (ProjectID, ItemID, itemdesc, Unit, Qty, Item) values (Forms!FProjectItems.ProjectID," & Me!ItemID & "," & Me!ItemDesc & ", tUnit, tQty, tItem);" 3. xtemp = DLookup("[ProjItemID]", "TProjectitems", "[ProjectID] = '" & Forms!FProjectItems!ProjectID & "' AND [ItemID] =" & Me!ItemID) DoCmd.RunSQL "UPDATE TProjectItems SET ItemDesc=" & Me!ItemDesc & " WHERE ProjectItemID = " & xtemp Else 4. xtemp = DLookup("[ProjItemID]", "TProjectitems", "[ProjectID] = '" & Forms!FProjectItems!ProjectID & "' AND [ItemID] =" & Me!ItemID) DoCmd.RunSQL "DELETE * FROM TProjectitems WHERE ProjectID = '" & Forms!FProjectItems.ProjectID & "' AND [ItemID]=" & Me!ItemID End If AGAIN: If you try to insert any literal text at all into a field of Text or Memo datatype, what you're inserting must be - NO OPTIONS! - delimited, either with ' or " as a text string delimiter. It doesn't matter whether the text you're inserting contains commas; if it contains ' you can use " as the delimiter; if it contains " you can use ' as the delimiter; or you can put two consecutive " marks within the string delimited by " in order to insert just one. You also need to include the syntactically required commas and other punctuation as text strings in building your SQL string. You're freely mixing VBA variables (tUnit, tQty, tItem) and SQL - that will NOT work, since the SQL engine has no way to see the values of these variables. Just as an example, you could change (2) to Dim strSQL As String strSQL = "INSERT INTO TProjectItems (ProjectID, ItemID, " _ & "itemdesc, Unit, Qty, Item) Values (Forms!FProjectItems.ProjectID," _ & Me!ItemID & ",'" & Me!ItemDesc & "', '" & tUnit & "'," & tQty & ","_ & tItem & ");" DoCmd.RunSQL strSQL If Me!ItemDesc contains the text "Lions, Tigers and Bears, oh my!" the resulting SQL statement will resemble INSERT INTO tProjectItems (ProjectID, ItemID, itemdesc, Unit, Qty, Item) VALUES (Forms!FProjectItems.ProjectID,251,'Lions, Tigers and Bears, oh my!','Each', 1, 2545); Setting the value of a SQL string will help in debugging as you can actually look at the SQL command which will be executed. You have not (as requested) indicated the datatypes of these fields, but I'm guessing that ItemDesc is a text or memo field. Any others? Which control contains the commas causing the problem? And... more basically - WHY are you doing this the hard way? Any major objections to just using a bound form, with no code at all? I admit, sometimes you must, and sometimes it's better - but it's not obvious in this case that you gain anything by programmatically doing what Access does for you. John W. Vinson [MVP] |
#10
|
|||
|
|||
insert contents with ',' in it
On Mon, 7 Jan 2008 17:58:27 -0000, "Smiley"
wrote: Hi there, thank you for the respond. It was user requirement that certain 'field' can be changed. I have a table which is a 'template' and then user would changed the values or contents of various fields. I do not understand what you mean by a "template". Is the user updating your template table? or some other table? How are the tables related? Yes, you are right, the ItemDesc is a memo field. As it is free text and using the max 255 in a text field may not be enough hence memo. Any other way that I would achieve the 'free text' input ? Well.... Yes. Base a Form on the table, or on a query referencing the table. Bind the ItemDesc field to a textbox on that form. Let the user type free text... anything they like, including commas, apostrophes, quotes, etc. No code needed. I need to digest what you have wrrten and will get back to you. John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|