SQL Problem

[Sash]

I have the following statement and the problem is that me!DESCR may contain a
single quote or comma which in turn throws off my statement. Can someone
help???

strsql = "insert into June_Location_ItemMaster (ITEM_NO, DESCR, MFR, MFRCat,
Inv_INV_QTY, PRICE, Inv_LOCATION, Inv_ID, DateCounted)VALUES('" & Me!ITEM_NO
& "', '" & Me!DESCR & "', '" & Me!MFR & "', '" & Me!MFRCat & "'," &
Me!INV_QTY & ", " & intPrice & ", '" & Me!LOCATION & "', " & Me!ID & ", '" &
Me!DateCounted & "');"

 

[Paolo]

Hi Sash,

Try in this way

strsql = "insert into June_Location_ItemMaster (ITEM_NO, DESCR, MFR, MFRCat,
Inv_INV_QTY, PRICE, Inv_LOCATION, Inv_ID, DateCounted)VALUES('" & Me!ITEM_NO
& "', """ & Me!DESCR & """, '" & Me!MFR & "', '" & Me!MFRCat & "'," &
Me!INV_QTY & ", " & intPrice & ", '" & Me!LOCATION & "', " & Me!ID & ", '" &
Me!DateCounted & "');"

I've substituted the single quote with two double quote and this should do
the trick

HTH Paolo

 

[Sash]

PERFECT!!! Thank you so much.

Hi Sash,

Try in this way

strsql = "insert into June_Location_ItemMaster (ITEM_NO, DESCR, MFR, MFRCat,
Inv_INV_QTY, PRICE, Inv_LOCATION, Inv_ID, DateCounted)VALUES('" & Me!ITEM_NO
& "', """ & Me!DESCR & """, '" & Me!MFR & "', '" & Me!MFRCat & "'," &
Me!INV_QTY & ", " & intPrice & ", '" & Me!LOCATION & "', " & Me!ID & ", '" &
Me!DateCounted & "');"

I've substituted the single quote with two double quote and this should do
the trick

HTH Paolo

 

[Sash]

What if the description contains a double quote??? This is driving me crazy.

 

[Douglas J. Steele]

See my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart
Access".

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html