Error using Replace Command in VFP 9.0

[CPJ1]

I am getting the Operator/operand type mismatch when I try this command:

REPLACE ccoddate WITH "12/14/2020" FOR ccoddate="12/07/2020" ALL

Can someone help me with this. Basically, I am trying to change the date in a column named ccoddate from 12/07/2020 to 12/14/2020. Thanks in advance

 

[Tom Borgmann]

I use a slightly other syntax for that. Usually I name the target cursor when using REPLACE and ALL is placed right behind the keyword.

REPLACE [b]ALL[/b] ccoddate WITH ccodedate - 7 FOR ccodedate = "12/07/2020" [b]IN myCursor[/b]

However, as I use another date format your format might as well be a problem too.

JM2C

-Tom

https://www.blogger.com/profile/08903165976787522012

 

[Mike Lewis]

This is probably because ccoddate is a Date field rather than a Character field.

If that's right, you need to do this:

REPLACE ccoddate WITH DATE(2020, 12, 14) FOR ccoddate=DATE(2020, 7, 12)

And, in this case, you don't need the ALL (as for FOR overrides it).

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[CPJ1]

Thanks, Tom.

That did not work. I got an error that myCursor does not exist. Than I got a operand/mismatch when I ran without the myCursor variable. I have a table with a few thousand records. The table has a field called "ccoddate". The date in that field is wrong. It is currently 12/07/2020. It needs to be 12/14/2020. I am using the command prompt to attempt to change the date. Thank you.

 

[Mike Lewis]

Did you try my suggestion? If my assumption about the data type is correct, I think you will find that it works.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tom Borgmann]

As I don't know the name of your table/cursor a to 'myCursor' as a placeholder. You should use the correct name

@Mike
wasn't the a date version with curly brackets... {mm/dd/yyyy}

-Tom

https://www.blogger.com/profile/08903165976787522012

 

[Tom Borgmann]

Additional Info: I rarely use VFP date formats. I have to use the format that my ODBC driver offers. In Informix quotation marks and apostrophes are available only.

-Tom

https://www.blogger.com/profile/08903165976787522012

 

[CPJ1]

Thank you. It's working now. My senior programmer is on medical leave for a long time and he did all the VFP programming. It's been awhile since I have to get in there and program. I will need to find a VFP programmer I guess. most of our main programs run JAVA and VFP. Thanks, again.

 

[Mike Lewis]

Thank you. It's working now.

It would be helpful if you could tell us how you got it working - if only for the benefit of anyone else who has the same problem.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

wasn't the a date version with curly brackets... {mm/dd/yyyy}

Yes, there was. But it's no longer recommended, and by default is not supported. It is the old "ambiguous date" format. The problem was a date such as {10/12/20} could have been interpreted as 10 December 2020, or 12 October 2020, or 10 December 1920 or 12 October 1920.

The preferred format is {^2020-10-12}, which is always 12 October 2020. Personally, I prefer the DATE() function, but that's just me.

One other point, Tom. You mentioned putting the ALL immediately after the REPLACE. I do that as well, but it is not at all significant. It makes no difference to the functioning of the command, and in this instance, it has no effect anyway because the FOR takes priority.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[CPJ1]

Sure, here's what I did:

replace ccoddate with ctod('12/14/2020') with CTOD ('12/14/2020') ALL;

Now I need to delete records with the 12/07/2020 data from an archive table. Should I use the same command but put delete instead? Thanks, again

 

[CPJ1]

I am sorry I typed the wrong thing.
replace ccoddate with CTOD('12/14/2020') all'

 

[CPJ1]

Hi,

I am so sorry to come back with the same issue but I am baffled of why the same commands I did on another computer that worked with your help is not working on another computer..

I hope you can help me with this. I am on another computer and using the same VFP command to replace a date in a field with another date. It's not working but worked on the other computer. I have a table with thousands of records and I am simply trying to replace the date in one of the fields. I am using the command window prompt. Here's what I have done:

set defa to f:\database
use news_ccod2014.dbf
REPLACE issuedate with CTOD('12/14/2020') FOR issuedate=CTOD('12/07/2020');

It goes to the EOF but no records are changed. Thanks in advance.

 

[Mike Lewis]

I've already suggested a solution - twice.

REPLACE ccoddate WITH DATE(2020, 12, 14) FOR ccoddate=DATE(2020, 7, 12)

The point is that expressing a data as, for example ('12/14/2020'), is ambiguous, for the reasons I explained in some detail above. Using the DATE() function avoids that issue.

By the way, in your first post, the name of the field was ccodate. Now it seems to be issuedate. Could that be causing a problem?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

As other computers can have other default date format (BRITISH, FRENCH, GERMAN,..) you better do as Mike suggests. Just a little correction, DATE(y,m,d) will be DATE(2020,12,7) for 7th december 2020 and not DATE(2020,7,12), which is for 12th July.

And thanks for refreshing my memory about the curly brackets format. It's one of the things, where VFP goes another route as all other databases, which usually take a date in some string format. Unfortunately there also are differences and no common format. You have to be very cautious about settings of the different IDEs, it can really change between MDY or DMY, if not even YMD, The DATE() function always needs parameters in the order of YMD, year, month, day. And today is DATE().

So shifting today to next week can be done by using REPLACE field WITH DATE()+7 FOR field = DATE() and shifting last week to today is REPLACE field WITH DATE() FOR field=DATE()-7.

Chriss

PS: You might want to ensure the FOR condition is sufficient, because, in a table as large as you describe, it might as well be you only want to shift some of the records to next week, for example depending on a status field.

 

[CPJ1]

Thanks everyone for your help. I did get this solved with a combination of your inputs. I cannot remember exactly what I used but it worked and I moved on. Thanks, again!