Something like a $MID or $LEFT function ? 1

[kev100]

I'm needing to get a quick total (count) of the number of times a certain value appears in a field.

Am using a regular old VFP .dbf table with standard commands.

The deal is that I'm need to just count the values whose FIRST 2 Letters match a particular critera.

For example..the following is used to simply total up the number of records in a table that have GREEN for the value in the Field Color.

select sum(iif(Color = 'GREEN',1,0)) from c:\tables\style.dbf

I'm needing to total the number of records that have a value in the Color field that BEGINS WITH "GR"

So that...for example...the command would total up all records that have EITHER GRAY or GREEN in the Color field

Something like:

select sum(iif($left(Color,1,2) = 'GR',1,0)) from c:\tables\style.dbf

However....$left or $mid seem to cause errors.

Am I thinking to much VB here...or can FoxPro work with strings in this manner?

Thanks very much.

 

[KenWMurphy]

something like LIKE()??

Ken

 

[brigmar]

USE C:\TABLES\STYLE
COUNT TO m.ln_Green FOR LEFT(color,2)="GR

 

[Stella740pl]

Drop the $ sign, remove the middle argument, and it should work.

In VFP, $ is a completely different animal, and function calls are not preceded with it.

There is LEFT() function, you can use it as
LEFT(color,2)='GR', or probably even better would be
LEFT(UPPER(color),2)=='GR'.

An equivalent of $mid would be SUBSTR(color,1,2).

And, as was mentioned by Ken, you can use LIKE() function to compare your string to a wildcard, or SELECT-SQL's own LIKE.

 

[kev100]

Stella...STEEELLLAAAAA !!

Thanks very much....

select sum(iif((LEFT(UPPER(color),2)=='GR'),1,0)) from c:\tables\style.dbf

...works great.

The other options may work as well but the LEFT function was the one I most quickly understood.

That one item has save HOURS of work.

Thanks VERY much.
TR

 

[Stella740pl]

Glad it works for you.
Thanks for the star.