How to port over an Access/SQL update query for VFP8 1

[miketheitguy]

Hey Gang,

I'm not writing some huge database here, not even really writing a good ammount of code in Foxpro, just have an issue that's hanging me up and I hope to get the down and dirty about it.

Here's the scenario. I work for a company that uses a pretty standard MS-SQL accounting, production, sales etc. database. I've got it pretty well all in hand, here's where the foxpro comes in, we're using a handheld device to track our route sales and to get the data from SQL to the palm device and vice versa we hired (before I was here) a 3rd party to modify some of their existing code and the result is a set of foxpro tables. They're not part of a database, they're just free tables sitting in a network share, they get updated when we run a script from within our accounting package. The problem is that the code is written badly and our route users often don't use the system correctly and I get bad data in the tables, if I import bad data I get HUGE headaches replicated throughout the SQL data, so I go in and clean the foxpro tables manually, wich can take anywhere from 10 minutes to 3 hours depending on the day and how badly the system went kerflooey, the errors are standard and I had written a simple database in Access 2002 to import the tables, run a few update queries to clean the data and then export the tables back, unfortunatly there is an issue with the exported data having an invalid table reference or key and although it looks perfect and the tables match perfectly when I go into the properties view, the system won't import the table correctly. So now I'm left trying to convert my simple access query to something in foxpro that I can run unattended.

Here's a sample of one of the SQL / Access queries I run...
The table is imticket.dbf and what I am trying to do is add the letters WC to any instance in the field cschedid where the driver value is less than 20 and the value of cschedid is the first two letters of any day of the week. It's hamhanded perhaps and unelegant but it works perfectly in Access. Any ideas??
{SAMPLE QUERY FOLLOWS}

UPDATE imticket SET imticket.cschedid = "WC" & [cschedid]
WHERE ((imticket.driver)<"20") AND Left([cschedid],2)="MO" OR Left([cschedid],2)="TU" OR Left([cschedid],2)="WE" OR Left([cschedid],2)="TH" OR Left([cschedid],2)="FR" OR Left([cschedid],2)="SA" OR Left([cschedid],2)="SU";

This thing won't even begin to run in VFP 8.0. Any ideas, help, hints or hey even to tell my I'm a loon chasing the golden fleece will be greatly appreciated...

Mike.. the IT Guy

 

[rgbean]

Mike,
Other than changing the concatenation "&" to a "+", losing all the "[" "]" characters and killing the ";" on the end, it should work in VFP. (To make it clearer I'll use some ";"s, which in FP is a line continuation.) i.e.

UPDATE imticket SET imticket.cschedid = "WC" + cschedid ;
 WHERE (imticket.driver<"20") AND Left(cschedid,2)="MO" ;
   OR Left(cschedid,2)="TU" OR Left(cschedid,2)="WE" ;
   OR Left(cschedid,2)="TH" OR Left(cschedid,2)="FR" ;
   OR Left(cschedid,2)="SA" OR Left(cschedid,2)="SU"

Or a bit simpler:

UPDATE imticket SET imticket.cschedid = "WC" + cschedid ;
 WHERE imticket.driver<"20" AND ;
   INLIST(Left(cschedid,2),"MO","TU","WE","TH","FR","SA","SU")

Rick

 

[miketheitguy]

Hallejulah, I knew it had to be something as simple as basic syntax, thank you a TON!.

Mike