insert contents with ',' in it

  • Thread starter Thread starter Smiley
  • Start date Start date
S

Smiley

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.
 
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.
Click to expand...

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]
 
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]
Click to expand...

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
 
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 ?
 
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.
Click to expand...

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]
 
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
 
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
Click to expand...

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]
 
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 said:
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
Click to expand...

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]
Click to expand...
 
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.
Click to expand...

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 ?
Click to expand...

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.
Click to expand...

John W. Vinson [MVP]
 
Back
Top