I have an append query that works fine unless one of the records
includes an apostrophe.

INSERT INTO [tbInvItems] ( ItemAbb, ItemName, AVIDNO )
SELECT DISTINCT [tbInventory].AVName, [tbInventory].AVDescription,
[tbInventory].AVID
FROM [tbInventory], [tbInvItems]
WHERE ((([tbInventory].AVID)=Forms!frmInventoryEntry!
frmInventorySub.Form!AVID));

I get error 3075: Syntax Error (missing operator) in query expression
'ItemAbb = '25' Cable".

Is there a workaround for this for Append Queries?

Thanks,
Kyle

"kyle" <kylek (AT) jsav (DOT) com> wrote

If you can run it from code; add a parameter to the query then assign the
form value to that.

PARAMETERS AVIDValue Text;
INSERT INTO [tbInvItems] ( ItemAbb, ItemName, AVIDNO )
SELECT DISTINCT [tbInventory].AVName, [tbInventory].AVDescription,
[tbInventory].AVID
FROM [tbInventory], [tbInvItems]
WHERE ((([tbInventory].AVID)=AVIDValue));

See Access help to set the parameter and run the query

Otherwise add a function inside the query to strip-off the offending
characters. You can have the same problem with commas, quotes and some SQL
keywords.

INSERT INTO [tbInvItems] ( ItemAbb, ItemName, AVIDNO )
SELECT DISTINCT [tbInventory].AVName, [tbInventory].AVDescription,
[tbInventory].AVID
FROM [tbInventory], [tbInvItems]
WHERE
((([tbInventory].AVID)=Replace(Forms!frmInventoryEntry!frmInventor ySub.Form!
AVID,"';,""","")));