SQL - HELP

G

Garry

I am trying to run an SQL statement in VBA. I created an update query,
changed to SQL view and copied the SQL to the code. The relevant portions of
the code follow

_____________________________________________________________________
Dim strSQL As String
Dim strItemNo As String

strItemNo =recDefect(“Item_Noâ€)

strSQL = "UPDATE tblProduct_List " _
& "SET tblProduct_List.Qty_On_Hand = [tblProduct_List]![Qty_On_Hand] + 1 " _
& " WHERE (((tblProduct_List.Item_No)= " & strItemNo & "));"

DoCmd.RunSQL strSQL
_________________________________________________________________________

tblProductList fields:
Qty_On_Hand is an integer
Item_No is a text field of length 10, and is the primary key
recDefect is a recordset based on a table, and Item_No is a text field of
length 10.

The only change I made is to the criteria, it was a parameter in the query
so I changed “parm1†to the variable strItemNo and adjusted the quotes in the
WHERE part.
No matter what I try, I get error 3464, data type mismatch in criteria
section.
I tried several modifications including changing …& strItemNo &
TO …& recDefect(“Item_Noâ€) &
and TO …& “99994†& -- (99994 is an Item_No in tblProductsList)

I keep getting the type mismatch error when the code executes and Debug
points to the DoCmd.RunSQL statement – no help at all.

I am running Access 2000 and using DAO and fairly new to Access and SQL is a
foreign language.
Any suggestions would be greatly appreciated.

Thanks in advance -- Garry Gross
 
D

Dirk Goldgar

Garry said:
I am trying to run an SQL statement in VBA. I created an update
query, changed to SQL view and copied the SQL to the code. The
relevant portions of the code follow

_____________________________________________________________________
Dim strSQL As String
Dim strItemNo As String

strItemNo =recDefect("Item_No")

strSQL = "UPDATE tblProduct_List " _
& "SET tblProduct_List.Qty_On_Hand = [tblProduct_List]![Qty_On_Hand]
+ 1 " _ & " WHERE (((tblProduct_List.Item_No)= " & strItemNo & "));"

DoCmd.RunSQL strSQL
________________________________________________________________________
_

tblProductList fields:
Qty_On_Hand is an integer
Item_No is a text field of length 10, and is the primary key
recDefect is a recordset based on a table, and Item_No is a text
field of length 10.

The only change I made is to the criteria, it was a parameter in the
query so I changed "parm1" to the variable strItemNo and adjusted the
quotes in the WHERE part.
No matter what I try, I get error 3464, data type mismatch in criteria
section.
I tried several modifications including changing .& strItemNo &
TO .& recDefect("Item_No") &
and TO .& "99994" & -- (99994 is an Item_No in tblProductsList)

I keep getting the type mismatch error when the code executes and
Debug points to the DoCmd.RunSQL statement - no help at all.

I am running Access 2000 and using DAO and fairly new to Access and
SQL is a foreign language.
Any suggestions would be greatly appreciated.

Thanks in advance -- Garry Gross
Click to expand...

If Item_No is a text field, you need to incorporate quotes -- single or
double, whichever is most convenient -- around the value of strItemNo
that you are building into your SQL string. Also, the expression
"[tblProduct_List]![Qty_On_Hand]" should be
"[tblProduct_List].[Qty_On_Hand]" (and you don't need the [] brackets in
this case, though Access puts them in just in case. Try this somewhat
simplified version:

strSQL = _
"UPDATE tblProduct_List " & _
"SET Qty_On_Hand = Qty_On_Hand + 1 " & _
"WHERE .Item_No = '" & strItemNo & "';"

That ought to work so long as strItemNo doesn't contain the single-quote
character (').
 
G

Garry

Dirk,
Thank-You very much. Works fine.
Garry

Dirk Goldgar said:
Garry said:
I am trying to run an SQL statement in VBA. I created an update
query, changed to SQL view and copied the SQL to the code. The
relevant portions of the code follow

_____________________________________________________________________
Dim strSQL As String
Dim strItemNo As String

strItemNo =recDefect("Item_No")

strSQL = "UPDATE tblProduct_List " _
& "SET tblProduct_List.Qty_On_Hand = [tblProduct_List]![Qty_On_Hand]
+ 1 " _ & " WHERE (((tblProduct_List.Item_No)= " & strItemNo & "));"

DoCmd.RunSQL strSQL
________________________________________________________________________
_

tblProductList fields:
Qty_On_Hand is an integer
Item_No is a text field of length 10, and is the primary key
recDefect is a recordset based on a table, and Item_No is a text
field of length 10.

The only change I made is to the criteria, it was a parameter in the
query so I changed "parm1" to the variable strItemNo and adjusted the
quotes in the WHERE part.
No matter what I try, I get error 3464, data type mismatch in criteria
section.
I tried several modifications including changing .& strItemNo &
TO .& recDefect("Item_No") &
and TO .& "99994" & -- (99994 is an Item_No in tblProductsList)

I keep getting the type mismatch error when the code executes and
Debug points to the DoCmd.RunSQL statement - no help at all.

I am running Access 2000 and using DAO and fairly new to Access and
SQL is a foreign language.
Any suggestions would be greatly appreciated.

Thanks in advance -- Garry Gross
Click to expand...

If Item_No is a text field, you need to incorporate quotes -- single or
double, whichever is most convenient -- around the value of strItemNo
that you are building into your SQL string. Also, the expression
"[tblProduct_List]![Qty_On_Hand]" should be
"[tblProduct_List].[Qty_On_Hand]" (and you don't need the [] brackets in
this case, though Access puts them in just in case. Try this somewhat
simplified version:

strSQL = _
"UPDATE tblProduct_List " & _
"SET Qty_On_Hand = Qty_On_Hand + 1 " & _
"WHERE .Item_No = '" & strItemNo & "';"

That ought to work so long as strItemNo doesn't contain the single-quote
character (').

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Top