works in stored query, not in vba

[splever]

I have a update query which updates correctly in the query window, but
not when executed in vba.

The VBA is
================================================== ====
strSQL = "UPDATE [tblReference_Ticket_PN_and_RootCause] " & _
"INNER JOIN [tblCurrentResults] " & _
"ON [tblReference_Ticket_PN_and_RootCause].[Ticket
Number]=[tblCurrentResults].[Ticket Number] " & _
"SET [tblReference_Ticket_PN_and_RootCause].[Product Name]= '" &
rstProductName("ProductName").Value & "', " & _

"[tblReference_Ticket_PN_and_RootCause].[ProductNameDrillDown]= '" &
rstProductName("ProductNameDrillDown").Value & "' " & _
"WHERE [tblCurrentResults].[Product Name] LIKE '%" &
rstProductName("ProductNameMatch").Value & "%' " & _
"AND nz([tblReference_Ticket_PN_and_RootCause].[Product
Name],'')='';"

Debug.Print strSQL

DoCmd.RunSQL strSQL
================================================== ====

strSQL captured from the immediate window (with a couple of carriage
returns thrown in)

================================================== ====
UPDATE [tblReference_Ticket_PN_and_RootCause]
INNER JOIN [tblCurrentResults]
ON [tblReference_Ticket_PN_and_RootCause].[Ticket
Number]=[tblCurrentResults].[Ticket Number]
SET [tblReference_Ticket_PN_and_RootCause].[Product Name]= 'P2P',
[tblReference_Ticket_PN_and_RootCause].[ProductNameDrillDown]= 'P2P'
WHERE [tblCurrentResults].[Product Name] LIKE '%P2P%'
AND nz([tblReference_Ticket_PN_and_RootCause].[Product Name],'')='';
================================================== ====

When I copy the sql from the immediate window, paste it in a query
window, and replace the "%" with "*", it works (I update 5000+ rows).
After resetting the data, the VBA version runs, but updates 0 rows.
I thought I found the answer with the "%" to "*" switch, but that
doesn't seem to be it. I'm stumped; any help out there?

Thanks,
Steve

[Rick Brandt]

sp*****@gmail.com wrote:

I have a update query which updates correctly in the query window, but
not when executed in vba.

The VBA is
================================================== ====
strSQL = "UPDATE [tblReference_Ticket_PN_and_RootCause] " & _
"INNER JOIN [tblCurrentResults] " & _
"ON [tblReference_Ticket_PN_and_RootCause].[Ticket
Number]=[tblCurrentResults].[Ticket Number] " & _
"SET [tblReference_Ticket_PN_and_RootCause].[Product Name]= '" &
rstProductName("ProductName").Value & "', " & _

"[tblReference_Ticket_PN_and_RootCause].[ProductNameDrillDown]= '" &
rstProductName("ProductNameDrillDown").Value & "' " & _
"WHERE [tblCurrentResults].[Product Name] LIKE '%" &
rstProductName("ProductNameMatch").Value & "%' " & _
"AND nz([tblReference_Ticket_PN_and_RootCause].[Product
Name],'')='';"

Debug.Print strSQL

DoCmd.RunSQL strSQL
================================================== ====

strSQL captured from the immediate window (with a couple of carriage
returns thrown in)

================================================== ====
UPDATE [tblReference_Ticket_PN_and_RootCause]
INNER JOIN [tblCurrentResults]
ON [tblReference_Ticket_PN_and_RootCause].[Ticket
Number]=[tblCurrentResults].[Ticket Number]
SET [tblReference_Ticket_PN_and_RootCause].[Product Name]= 'P2P',
[tblReference_Ticket_PN_and_RootCause].[ProductNameDrillDown]= 'P2P'
WHERE [tblCurrentResults].[Product Name] LIKE '%P2P%'
AND nz([tblReference_Ticket_PN_and_RootCause].[Product Name],'')='';
================================================== ====

When I copy the sql from the immediate window, paste it in a query
window, and replace the "%" with "*", it works (I update 5000+ rows).
After resetting the data, the VBA version runs, but updates 0 rows.
I thought I found the answer with the "%" to "*" switch, but that
doesn't seem to be it. I'm stumped; any help out there?

Thanks,
Steve

In VBA you also need to use * instead of % unless you are using ADO.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Terry Kreft]

Well the following certainly works
strSQL = "UPDATE tblReference_Ticket_PN_and_RootCause AS rc " _
& "INNER JOIN tblCurrentResults AS cr " _
& "ON rc.[Ticket Number] = cr.[Ticket Number] " _
& "SET rc.[Product Name] = 'P2P', " _
& "rc.ProductNameDrillDown = 'P2P' " _
& "WHERE cr.[Product Name] Like '*P2P*' " _
& "AND nz([rc].[Product Name],'')='';"

currentdb.execute strSQL

Note * not %

--

Terry Kreft
<sp*****@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...

I have a update query which updates correctly in the query window, but
not when executed in vba.

The VBA is
================================================== ====
strSQL = "UPDATE [tblReference_Ticket_PN_and_RootCause] " & _
"INNER JOIN [tblCurrentResults] " & _
"ON [tblReference_Ticket_PN_and_RootCause].[Ticket
Number]=[tblCurrentResults].[Ticket Number] " & _
"SET [tblReference_Ticket_PN_and_RootCause].[Product Name]= '" &
rstProductName("ProductName").Value & "', " & _

"[tblReference_Ticket_PN_and_RootCause].[ProductNameDrillDown]= '" &
rstProductName("ProductNameDrillDown").Value & "' " & _
"WHERE [tblCurrentResults].[Product Name] LIKE '%" &
rstProductName("ProductNameMatch").Value & "%' " & _
"AND nz([tblReference_Ticket_PN_and_RootCause].[Product
Name],'')='';"

Debug.Print strSQL

DoCmd.RunSQL strSQL
================================================== ====

strSQL captured from the immediate window (with a couple of carriage
returns thrown in)

================================================== ====
UPDATE [tblReference_Ticket_PN_and_RootCause]
INNER JOIN [tblCurrentResults]
ON [tblReference_Ticket_PN_and_RootCause].[Ticket
Number]=[tblCurrentResults].[Ticket Number]
SET [tblReference_Ticket_PN_and_RootCause].[Product Name]= 'P2P',
[tblReference_Ticket_PN_and_RootCause].[ProductNameDrillDown]= 'P2P'
WHERE [tblCurrentResults].[Product Name] LIKE '%P2P%'
AND nz([tblReference_Ticket_PN_and_RootCause].[Product Name],'')='';
================================================== ====

When I copy the sql from the immediate window, paste it in a query
window, and replace the "%" with "*", it works (I update 5000+ rows).
After resetting the data, the VBA version runs, but updates 0 rows.
I thought I found the answer with the "%" to "*" switch, but that
doesn't seem to be it. I'm stumped; any help out there?

Thanks,
Steve