How to: Delete within a scope with character field and other character 2

[keepingbusy]

Hi all

Can any please suggest what's wrong here?

I have the following code:

STORE SPACE(7) TO mprice1, mprice2
STORE "0,74" TO mprice1
STORE "1,00" TO mprice2

As you can see the prices are seperated by a comma and not a full stop (This is due to the fact its a French project and the Euros have to be seperated by a comma ,)

For this reason I convert the two variables replacing the comma with a full stop:

STORE strtran(mprice1,[,],[.]) TO mprice1
STORE strtran(mprice2,[,],[.]) TO mprice2

I then tried:

DELETE FOR VAL(SELL)=>VAL(mprice1) AND VAL(SELL)<=VAL(mprice1)

and

DELETE FOR SELL=>VAL(mprice1) AND SELL<=VAL(mprice1)

and

DELETE FOR VAL(SELL)=>mprice1 AND VAL(SELL)<=mprice1

None of the above work can anyone suggest why?

The Table field SELL is also a character field and the amounts stored are originally , but converted as above before the DELE FOR process.

(VFP Version 9 this project)

Hope that makes sense
Many thanks
Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[dbMark]

First, all comparisons should be done using the same data type, so of the 3 examples, only the first should work. Character strings should be converted to numeric type if comparing it against numeric type, or vice versa.

Next, in the examples above you're comparing against the same field, so the range test is useless unless you have an exact match. Why not change the second part to mprice2 and see if that works as you expect?

DELETE FOR VAL(SELL)=>VAL(mprice1) AND VAL(SELL)<=VAL(mprice[b][u]2[/u][/b])

Have you tried the BETWEEN function?

* Note: all 3 vars/fields are character strings
DELETE FOR BETWEEN(VAL(sell),VAL(mPrice1),VAL(mPrice2))

 

[dbMark]

Oh, another question, will mPrice1 ever be greater than mPrice2? If so, all the above examples would fail their tests, so be aware of that.

 

[keepingbusy]

Hi Mark

There was a typo error on my part with

DELETE FOR VAL(SELL)=>mprice1 AND VAL(SELL)<=mprice1

Oh, another question, will mPrice1 ever be greater than mPrice2? If so, all the above examples would fail their tests, so be aware of that.

No. mprice1 will always be < mprice2

I ran the suggestion you posted and this appears to work.
Not sure whether I'm having a problem with the index on the field SELL.

Index On VAL(SELL) Tag SELL

Is the above correct?

Thanks dbmark
Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[wgcs]

The statements:

DELETE FOR VAL(SELL)=>VAL(mprice1) AND VAL(SELL)<=VAL(mprice1)
DELETE FOR SELL=>VAL(mprice1) AND SELL<=VAL(mprice1)
DELETE FOR VAL(SELL)=>mprice1 AND VAL(SELL)<=mprice1

each have a typo in them... "Greater than or equal to" is ">=" , not "=>"

try these:

DELETE FOR VAL(SELL)>=VAL(mprice1) AND VAL(SELL)<=VAL(mprice1)
DELETE FOR SELL>=VAL(mprice1) AND SELL<=VAL(mprice1)
DELETE FOR VAL(SELL)>=mprice1 AND VAL(SELL)<=mprice1

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[dbMark]

Keepingbusy, make sure the order is set. If you closed the table after indexing, did you set the order after the table was reopened?

INDEX ON VAL(sell) TAG sell
SET ORDER TO sell

Bill, I thought => and >= worked equally well (pun intended), same for =< and <=. Is this another case of VFP being very forgiving and letting us be dyslexic?

dbMark

 

[dbMark]

Bill, I meant to say that the operators work either way, but I do agree that putting the equal sign first is undocumented and therefore could disappear in a future version, if any.

 

[keepingbusy]

Bill

Thank you for your response. I tried as suggested:

DELETE FOR VAL(SELL)>=VAL(mprice1) AND VAL(SELL)<=VAL(mprice2)

(and changed the mprice1 to mprice2 - that's two of us now with typo errors)
This worked fine and deleted the appropriate records

dbMark

make sure the order is set. If you closed the table after indexing, did you set the order after the table was reopened?

Yes. In the click event of a grid's column header I have the following code:

SET ORDER TO SELL
GO TOP
THISFORM.REFRESH
THISFORM.GRID1.SETFOCUS()

The order is not shown in numerical order for example:
4,99
4,49
4,99
4,49
4,15
4,75..... and continues like this increasing in value such as:
13,75
13,15
13,85

This is very strange but I'm blaming the , in the price structure.

As far as the index tag goes, I've tried different combinations:

INDEX ON VAL(sell) TAG sell

This shows the behaviour as above

INDEX ON sell TAG sell

Completely throws the order out all together

I'm sure its just something simple to resolve this issue, any suggestions?

Thanks again both for posting
Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[dbMark]

The indexing is working correctly.

INDEX ON sell TAG sell places everything in character precedence order, which is unreliable if the decimal points/commas aren't lined up identically in every record.

INDEX ON VAL(sell) TAG sell indexes the number strings after they've been converted to numeric values. Did you notice that doing to throws out all the decimal places? That's why they were only partially ordered. "4,99" and "4,15" both converted to numeric 4.

Try this pattern in your index:

INDEX ON VAL(STRTRAN(sell,",",".")) TAG sell

Don't try to use SET POINT TO "," since Help says: Use SET POINT to change the decimal point from the default, which is a period (.). Issue SET POINT TO without cDecimalPointCharacter to reset the decimal point to a period. Although you can set the displayed decimal point to a different character, you must use a period as the decimal point in calculations.

 

[Olaf Doschke]

Hi all,

Val() depends on SET POINT TO, which can depend on the sysformats, so you should do all storing and comparing with Number or Currency fields, only format with "," instead of "." for display.

Then all troubles will end.

Bye, Olaf.

 

[keepingbusy]

dbmark

Thanks for posting back. In the end we had to resolve our issue by changing the table structure back to numeric field as there were other issues with other functions within the APP.

When the user wished to create an XLS file from the table in French format (which was what this project was all about) we reverted to using the below code at the last point to produce the desired effect:

DO WHILE NOT EOF()
  myourp=""
  myourp=SELL
  REPLACE SELL WITH strtran(myourp,[.],[,])
  SKIP
ENDDO

In any case, thank you for taking the time to post on this thread

OlafDoschke
Thank you as well

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk