Prev: 2010: Essential Diffs?
Next: 'Time Picker'
From: kyle on 9 Apr 2010 12:24
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

From: paii, Ron on 9 Apr 2010 13:44

"kyle" <kylek(a)jsav.com> wrote in message
news:78a8065f-af7f-4804-bc55-51a935e3e1d4(a)i25g2000yqm.googlegroups.com...
> 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
>

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!frmInventorySub.Form!
AVID,"';,""","")));


 | 
Pages: 1
Prev: 2010: Essential Diffs?
Next: 'Time Picker'