Syntax Error

[KLynch0803]

What is wrong with this besides the fact I tried writing it? :P I'm sure I'm overlooking something simple like I have no clue what I'm doing.

 
IF tbl_Customers.Matched = "0" Then
 
   UPDATE tbl_Customers SET 
   tbl_Customers.ServiceMatchedTo = tbl_Service_Providers.ServiceProviderID, " ", tbl_Service_Providers.Service_Company_Name AND tbl_Customers.MatchedDate = NOW() AND Matched = "-1"
WHERE (((tbl_Customers.[Matched])=0) AND ((tbl_Service_Providers.[CoverageArea])= tbl_Customers.[ZipCode]) AND ((tbl_Service_Providers.[Services_Provided])= tbl_Customers.[ServicesType]));
 
End If

[llkhoutx]

Build your query in the Query design window, then look at the SQL view of that query. Then emulate the SQL or cut and past it.

[KLynch0803]

Build your query in the Query design window, then look at the SQL view of that query. Then emulate the SQL or cut and past it.

I have been trying that... I can get it to run on first SET and First WHERE it changes all my Values in the table in Matched to -1... When I add the second to either it gives me that error..

Works

 
UPDATE tbl_Customers SET 
 Matched = "-1"
WHERE ((tbl_Customers.[Matched])=0);

Wont Work

 
UPDATE tbl_Customers SET 
 tbl_Customers.ServiceMatchedTo = tbl_Service_Providers.ServiceProviderID, AND tbl_Customers.MatchedDate = NOW() AND Matched = "-1"
WHERE (((tbl_Customers.[Matched])=0) AND ((tbl_Service_Providers.[CoverageArea])= tbl_Customers.[ZipCode]);

[Guus2005]

Try this one.

 
UPDATE tbl_Customers SET 
 tbl_Customers.ServiceMatchedTo = tbl_Service_Providers.ServiceProviderID, tbl_Customers.MatchedDate = NOW(), Matched = "-1"
WHERE (((tbl_Customers.[Matched])=0) AND ((tbl_Service_Providers.[CoverageArea])= tbl_Customers.[ZipCode]);

[KLynch0803]

Try this one.

Ok So I pasted in what you gave me and got a missing Syntax. I added one missing ")" and it gave me "No Value Given for one or more paremeters

 
UPDATE tbl_Customers SET 
 tbl_Customers.ServiceMatchedTo = tbl_Service_Providers.ServiceProviderID, tbl_Customers.MatchedDate = NOW(), Matched = "-1" 
WHERE (((tbl_Customers.[Matched])="0") AND ((tbl_Service_Providers.[CoverageArea])= tbl_Customers.[ZipCode]));

[Guus2005]

If that doesn't work, you are probably using more tables. I figured that much.

You need to connect tbl_Customers to tbl_Service_Provider probably by a key.
Put the result in a query and run it to view the result, not actually executing the query using the exclamation mark.

When executing the query you'll see the parameters you need to fill in. If you didn't mean to add parameters you propbably misspell a fieldname.

HTH

[KLynch0803]

If that doesn't work, you are probably using more tables. I figured that much.

You need to connect tbl_Customers to tbl_Service_Provider probably by a key.
Put the result in a query and run it to view the result, not actually executing the query using the exclamation mark.

When executing the query you'll see the parameters you need to fill in. If you didn't mean to add parameters you propbably misspell a fieldname.

HTH

I am reading more than one table (tbl_Customers & tbl_Service_Providers) but I'm only writing to tbl_Customers

[llkhoutx]

UPDATE tbl_Customers SET 
 tbl_Customers.ServiceMatchedTo = tbl_Service_Providers.ServiceProviderID, AND 
 tbl_Customers.MatchedDate = NOW() AND Matched = "-1"
WHERE (((tbl_Customers.[Matched])=0) AND 
((tbl_Service_Providers.[CoverageArea])= tbl_Customers.[ZipCode]); 

should be

UPDATE tbl_Customers SET
tbl_Customers.ServiceMatchedTo = tbl_Service_Providers.ServiceProviderID,
tbl_Customers.MatchedDate = NOW(), Matched = "-1"
WHERE (((tbl_Customers.[Matched])=0) AND
((tbl_Service_Providers.[CoverageArea])= tbl_Customers.[ZipCode]);

I suspect that Matched in a boolean field and should be updated with a mere -1, not "-1". -1 equates to True, "-1" does not.

If you'd followed my first post in this thread, you would have seen your errors.

I have not attempted to balance the parentheses in the SQL WHERE clause. I wouldn't use them unless absolutely necessary.

[llkhoutx]

The SQL in your original post in this thread

 UPDATE tbl_Customers SET 
   tbl_Customers.ServiceMatchedTo = tbl_Service_Providers.ServiceProviderID, 
   " ", tbl_Service_Providers.Service_Company_Name AND tbl_Customers.MatchedDate = NOW() AND Matched = "-1"
WHERE (((tbl_Customers.[Matched])=0) AND ((tbl_Service_Providers.[CoverageArea])= tbl_Customers.[ZipCode]) AND ((tbl_Service_Providers.[Services_Provided])= tbl_Customers.[ServicesType])); 

is grossly wrong.

" ", tbl_Service_Providers.Service_Company_Name 

makes no sense.

tbl_Service_Providers.Service_Company_Name AND tbl_Customers.MatchedDate = NOW() 

does not have the correct syntax.

[Guus2005]

I am reading more than one table (tbl_Customers & tbl_Service_Providers) but I'm only writing to tbl_Customers

The syntax you need looks something like:

update a set a.field1 = b.field1, a.field2 = b.field2 
from a inner join b on a.id = b.id
where a.field3 = "somevalue"

Like llkhoutx said before, if you don't know how, just use the QBE functionality.

HTH

[KLynch0803]

OK so the way it all looks I have a working Update Query but I someone told me I need an Array to hold the values and thats why I'm getting the message message no varialbel for requird parimeter? Is this true and how you write an array? Here is what I have..


Thank You Guus and llkHous for all your help for guiding me this far...

Can someone please dear ASP / ACCESS Gods help me finish getting this working I have no hair left LOL

Actual Hard Code:

'**** Line 1 through Line 20 
 
'Declare counter varibles
dim x
dim truerecordcount
dim rsrecordcount
'Delcare array to build buttons in listview
dim RsRecordArray
dim RsArray(5)
'Count the records
CountRecords()
'Declare array to hold one record
redim RsRecordArray(rsrecordcount)
'Declare MDB varibles to use
dim rs
dim sql
dim testsql
'testsql = "Test Message"
'Declare Counter Varibles to use with if, for loops
dim i
dim j
'Declare our button variables
dim DetectedButton
dim DetectedEditButton
dim DetectedDeleteButton
'Declare variables to detect which edit button was clicked
dim RecordButton
 
 
' LINE 25 Through 28
 
if Request.Form("ManuallyRetryMatchingRequests") <> "" then
 DetectedButton = Request.Form("ManuallyRetryMatchingRequests")
end if
 
'COUPLE 1000 LINES OF CODE
 
' LAST QUERY ON PAGE LINE 2571 thru 2622 
 
' Manually Retry Matching Request
 
 
 if DetectedButton = "Manually Retry Matching Requests" then
  'Prep the database to be accessed
  set conn=Server.CreateObject("ADODB.Connection")
  conn.Provider="Microsoft.Jet.OLEDB.4.0"
  conn.Open(Server.Mappath("/MYDBPATH.mdb"))
  set rs = Server.CreateObject("ADODB.recordset")
 
  'Format the Query to use against the database
  SQL = "UPDATE tbl_Customers SET "
  SQL = SQL & "tbl_Customers.[ServiceMatchedTo] = tbl_Service_Providers.[ServiceProviderID], "
  SQL = SQL & "tbl_Customers.[MatchedDate] = '" & NOW() & "', "
  SQL = SQL & "tbl_Customers.[Matched] = '-1' "
  SQL = SQL & "WHERE tbl_Customers.[Matched] LIKE '0' AND "
  SQL = SQL & "tbl_Customers.[ZipCode] LIKE '% tbl_Service_Providers.[CoverageArea] %' AND "
  SQL = SQL & "tbl_Customers.[ServiceType] LIKE '% tbl_Service_Providers.[Services_Provided]%'"
  'Now read the record into the array
  'i = 0
     for i = 0 to 18
     'each x in rs.Fields
        RsRecordArray(i) = ""
       next
 
'  rs.Open sql, conn
  Response.Write(sql)
 %>
 
  <table border="1" width="100%">
  <tr>
   <td width="50%" height="25">find this table and put your custom code 
   in its place</td>
   <td width="50%" height="25">&nbsp;</td>
   </tr>
  <tr>
   <td width="50%" height="25">&nbsp;</td>
   <td width="50%" height="25">&nbsp;</td>
   </tr>
  </table>
 
 
 <%
 end if
 %>

Output of Code:

 
UPDATE tbl_Customers SET tbl_Customers.[ServiceMatchedTo] = tbl_Service_Providers.[ServiceProviderID], tbl_Customers.[MatchedDate] = '2/3/2010 7:55:25 PM', tbl_Customers.[Matched] = '-1' WHERE tbl_Customers.[Matched] LIKE '0' AND tbl_Customers.[ZipCode] LIKE '% tbl_Service_Providers.[CoverageArea] %' AND tbl_Customers.[ServiceType] LIKE '% tbl_Service_Providers.[Services_Provided]%

[Guus2005]

ASP and Access are not the same eventhough they use the same SQL statements. I know nothing about ASP. In VB or VBA you don't need to use an array. You can use a recordset instead. Like the one you use to fill the array.

Another thing, you dimension all the variables but do not assign the type to them.

dim rs
 
instead of
 
dim rs as recordset

Which means in VB and VBA that all variables are of the type variant. This means a lot of overhead because a variant can be anything.