Using SQL to change table values

[Quaterno]

I am trying to create a database for my dad's business and I need a button on a form that creates a query from a table of customers with just one record in (the customer that is currently selected on the form). It then needs to check the value of a field called numberofcalls and change the value of datetocall accordingly. The idea of this is that when the customer is called for the first time, the date to call them next is moved forward by two days. Then when they are called the second time, it is moved forward 4 days (end of the week) and then 7 days every time after that (so they are called weekly).

This is the code I have in my VB attached to the button I want to use, in the click event:

Dim SQL As String
SQL = "SELECT tbl_customers.CustID, tbl_customers.Fname, tbl_customers.Sname, tbl_customers.Numberofcalls, tbl_customers.Datetocall " & _
"FROM tbl_customers " & _
"WHERE (((tbl_customers.CustID)=me.[custid]));" & _
"UPDATE tbl_customers SET datetocall = datetocall + 2, numberofcalls = numberofcalls + 1" & _
"WHERE numberofcalls = 0;" & _
"UPDATE tbl_customers SET datetocall = datetocall + 5, numberofcalls = numberofcalls + 1" & _
"WHERE numberofcalls = 1;" & _
"UPDATE tbl_customers SET datetocall = datetocall + 7, numberofcalls = numberofcalls + 1" & _
"WHERE numberofcalls > 1;"
DoCmd.RunSQL SQL

When I run this I get an error: "Characters found after end of SQL statement"

I'm no SQL programmer and I need some help, any help anyone could give would be greatly appreciated.

 

[alvechurchdata]

"WHERE (((tbl_customers.CustID)=me.[custid]));" & _

Might be because you've buried several semicolons in the middle of the SQL.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[HarleyQuinn]

I imagine it's because that would probably be a valid script but not a single SQL statement, do a debug.print and look at it. I'd also imagine that if numberofcalls was 0 then you'd always end up with datetocall+7 because of the way the updates would be ordered.

Perhaps a select to get the number of calls and then a case statement to decide which update to run based on the numberofcalls value?

Just for the interested, the Debug.Print looks like this:

SELECT tbl_customers.CustID, tbl_customers.Fname, tbl_customers.Sname, tbl_customers.Numberofcalls, tbl_customers.Datetocall 
FROM tbl_customers WHERE (((tbl_customers.CustID)=me.[custid]));UPDATE tbl_customers SET datetocall = datetocall + 2, 
numberofcalls = numberofcalls + 1WHERE numberofcalls = 0;UPDATE tbl_customers SET datetocall = datetocall + 5, 
numberofcalls = numberofcalls + 1WHERE numberofcalls = 1;UPDATE tbl_customers SET datetocall = datetocall + 7, 
numberofcalls = numberofcalls + 1WHERE numberofcalls > 1;

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Quaterno]

I am unsure about how to make this work. Every time I try even simple bits of SQL statements it seems to come up with the error "characters found after end of SQL statement". The first SELECT statement needs a semi-colon at the end, correct? I really just don't know the syntax for SQL. If I try:

SQL = "SELECT tbl_customers.CustID, tbl_customers.Fname, tbl_customers.Sname, tbl_customers.Numberofcalls, tbl_customers.Datetocall " & _
    "FROM tbl_customers WHERE (((tbl_customers.CustID)=me.[custid]));" & _
    "UPDATE tbl_customers Datetocall = Datetocall + 2, " & _
    "Numberofcalls = Numberofcalls + 1WHERE Numberofcalls = 0;"

This doesn;t work and if I try it with just the first select statement it doesn't think that it's complete, where am I going wrong?

 

[HarleyQuinn]

The basic idea is using the method you are, you are only able to run one statement at a time (a stetment being ended by a semi-colon in this case).

What you would need to do is to run the SELECT on it's own, then run the UPDATE on it's own etc.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[jrbarnett]

In addition to HarleyQuinn's comment, when doing the update statement, put a space between the 1 and WHERE on the last line.

John

 

[Quaterno]

I tried that but it dooesn't seem to work. It keeps throwing up the error that the docmd.RunSQL statement needs to have an SQL statement to work properly.

I have figured out how to do this another way now within access itself so I think I am okay for the moment but it would still be interesting to see how this would work using SQL.

 

[dhookom]

You can't use RunSQL with a standard SELECT statement. RunSQL is reserved for action queries.

Access also doesn't support multiple SQL statements in a single RunSQL as mentioned earlier.

This will also not work since "Me" can't be resolved

    "WHERE (((tbl_customers.CustID)=me.[custid]));"

It would need to be something like:

    "WHERE tbl_customers.CustID=" & me.[custid]

I would also consider changing the table structure to remove all call information from the customers table. Calls are calls and customers are customers. I would create a call log table that stores the date and other information for each call to each customer (one record per call).


Duane
Hook'D on Access
MS Access MVP