SELECT and REPLACE 1

[tforr]

Hi I am attempting to select specific data from a table and replace a field with todays date. Can anyone help. I have successfully selected the data from the table in qestion but dont know how to replace.

The code I have written is as follows:

SELECT Z_SNOML.SX_DATE, Z_SNOML.SX_DONE;
FROM COMP_Z!SNOML Z_SNOML;
WHERE Z_SNOML.SX_DATE > DATE() AND Z_SNOML.SX_DONE <> &quot;A&quot;
REPLACE WITH DATE()


Hope you can help.

Regards,

Tom

 

[jimoo]

Tom, you example puts the output into a read only cursor. Also, the replace command needs a field name.

I suspect what you are trying to do is replace the data in the original table. If so, try this.

REPLACE myDATE WITH DATE FOR Z_SNOML.SX_DATE > DATE() AND Z_SNOML.SX_DONE <> &quot;A&quot;

Let me know if this helps or if you are trying to do something other than update the original table.



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[craigsboyd]

SELECT Z_SNOML.SX_DATE, Z_SNOML.SX_DONE;
FROM COMP_Z!SNOML Z_SNOML;
WHERE Z_SNOML.SX_DATE > DATE() AND Z_SNOML.SX_DONE <> &quot;A&quot; into cursor crsTemp READWRITE

REPLACE crsTemp.SX_Date WITH DATE() ALL


...or just...

SELECT date() as SX_DATE, Z_SNOML.SX_DONE;
FROM COMP_Z!SNOML Z_SNOML;
WHERE Z_SNOML.SX_DATE > DATE() AND Z_SNOML.SX_DONE <> &quot;A&quot; into cursor crsTemp

...not sure which field you were trying to replace I just assumed it was SX_Date, if not at least you get the idea



Slighthaze = NULL
_{craig1442@mchsi.com}

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​

 

[StewartUK]

Tom,

Is it the field SX_DATE that you want to replace with DATE()?

If so you can rewrite your statement saying:

SELECT DATE() AS SX_DATE

Hope that helps, sorry if I've missed the point.

Stewart

 

[KeithTrangmar]

Another approach similar to Jim's suggestion would be:

UPDATE comp_z!snoml SET sx_date=DATE() ;
WHERE sx_date > DATE() ;
AND sx_done <> &quot;A&quot;

This is the &quot;proper&quot; SQL syntax (not that there's anything wrong with using REPLACE), and it has the advantage/disadvantage (depending on how you look at it) that it doesn't need to FLOCK() the file to work.

Keith

 

[tforr]

Ok I now have this working. I have been using the command window to carryout this procedure. I want this to happen when the user hits a button on a form.

I have put the following 2 lines of code in the click event:

USE &quot;c:\opera ii\data\z_snoml.dbf&quot; SHARED
REPLACE all Z_SNOML.SX_DATE WITH DATE() FOR Z_SNOML.SX_DATE > DATE() AND Z_SNOML.SX_DONE <> &quot;A&quot;

Is this all that is needed or do I have to add any other bits of code.

Also I would like to show the user that something has happened behind the scene because when the button is selected the user will not know that anything has happened.
Again if anyone can help then this will be great. A progress bar would be good or maybe just a message. I won't know what to do so be patient.

Thanks again,

Tom

 

[KeithTrangmar]

Try

WAIT WINDOW &quot;Working...&quot; NOWAIT NOCLEAR

just before you start and

WAIT CLEAR

at the end. You could use a MessageBox() call to display an &quot;Update complete&quot; message - sorry I don't remember the syntax as I don't actually use it myself.

Keith