Invalid String in Update query

[Guest]

I am trying to do an update query to get my description field to be the
identical for each item entry.. I have many items in my DB and about 6
entries have one description and all others have another each item number.For
instance I want to update a description like what I have below so all will be
identical for that item number.

ItemNum ItemDesc
1234 1.5"X15' OTML DBL FCD SATIN

To

1234 1.5"X5YDS OTML DBL FCD SATIN

I have many item numbers and doing this manually would be tedious. I tried
an update query and I am getting syntax errors ( Invalid String)because of
the quote marks " and apostrophes'. I had no problem getting themm into the
DB originally. Now that I want to update it gives me syntax errors. Is there
a way to get around these syntax errors.

Regards

Chris

 

[Marshall Barton]

I am trying to do an update query to get my description field to be the
identical for each item entry.. I have many items in my DB and about 6
entries have one description and all others have another each item number.For
instance I want to update a description like what I have below so all will be
identical for that item number.

ItemNum ItemDesc
1234 1.5"X15' OTML DBL FCD SATIN

To

1234 1.5"X5YDS OTML DBL FCD SATIN

I have many item numbers and doing this manually would be tedious. I tried
an update query and I am getting syntax errors ( Invalid String)because of
the quote marks " and apostrophes'. I had no problem getting themm into the
DB originally. Now that I want to update it gives me syntax errors. Is there
a way to get around these syntax errors.

You need to double up on any interior quotes that are the
same as the enclosing quotes.

If you have a problem with a query, it's a lot easier for us
to help you if you post a Copy/Paste of the query's SQL
statement.

 

[Michel Walsh]

Hi,


Use DoCmd and FORMS!FormName!ControlName syntax:



DoCmd.RunSQL "UPDATE mytable SET ItemDesc = FORMS!FormNameHere!ItemDesc
WHERE itemNum= FORMS!FormNameHere!ItemNum"



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I am just wondering what the proper quotation marks would be. I am using the
update query in design iew Thanks

 

[Marshall Barton]

Using query design view is a little ambiguous in tricky
situations so most postings use the precise SQL view to
communicate.

Let's say your query's SET clause looks like:

SET ItemDesc = "1.5"X15' OTML DBL FCD SATIN"

It is obvious that the interior double quote is going to
confuse everything so the rule I posted earlier would be
used to eliminate the syntactic confusion:

SET ItemDesc = "1.5""X15' OTML DBL FCD SATIN"

On the other hand, Access (not VBA) allows you to use either
" or ' to enclose a string. So you could use this to get
the same result:

SET ItemDesc = '1.5"X15'' OTML DBL FCD SATIN'
where that's a single " mark before and two ' marks after
the X15.