help with my code please

[rclarke]

I am not good at this but could somebody who is have alook at my code and tell me if it is total crap or what

what I want it to do is look at the farmer find his field details and add up the total hectares he has providing that the field is planed to be used that season (there is a seven year cycle)I can tell this through the field seasonal details table.

Answer=0
totals=0
GOTO TOP In Field_Plot_Seasonal
Do WHILE NOT EOF()
IF field_plot_seasonal.fps_sorg = thisform.combo1.value
IF field_plot_seasonal.fps_season = thisform.distinct_values_combo2.Value
GOTO TOP IN Field_plot_static
Do WHile NOT EOF()
If Field_plot_static.Fpd_sorg = Thisform.combo1.value
Answer=Field_plot_static.fpd_farea
Totals = totals+answer
SKIP
ENDI
ENDDO
ENDI
ENDIF
SKIP
ENDDo

It does different things every time I run it.

SOme times it appears to put up a number other times it makes all the fields on the form blank and greyed out.

AS is blatently obvious by this I have not done much coding before.

Please help me

Rachel

 

[rajeevnandanmishra]

Hi Rache,
Try to run the following code in place of your code
--------------------------

Answer=0
totals=0
SELECT Field_Plot_Seasonal
GOTO TOP
Do WHILE NOT EOF()
IF fps_sorg = thisform.combo1.value
IF fps_season = thisform.distinct_values_combo2.Value
SELECT Field_plot_static
GOTO TOP
Do WHile NOT EOF()
If Fpd_sorg = Thisform.combo1.value
Answer = fpd_farea
Totals = totals+answer
ENDI
SKIP
ENDDO
ENDI
ENDIF
SELECT Field_Plot_Seasonal
SKIP
ENDDo

--------------------------
Copy the code as it is.

If you still face problem, Please give us some sample data of both tables and what result you expect from that.

 

[rclarke]

Thank You rajeevnandmishra

I do get a result now which is good however it is not quite the result I want

I get two times the number of ALL fields that farmer has.

I.E Bawtry farms has 4 fields with areas 50, 32, 16, 11

total all them together I get 109 the value I get in the box is 218

what I would like is

Table: field plot static (this is a list of all fields that farmer has)

FARM NAME FIELD NAME AREA
Bawtry farm Hanger 50
Bawtry Farm 32 Acre 32
Bawtry Farm 16 Acre 16
Bawtry Farm 11 Acre 11

Table: field_plot_Seasonal (this table says which fields are being used in which season)

FARM NAME FIELD NAME SEASON
Bawtry Farm Hanger 2000/2001
Bawtry Farm 32Acre 2003/2004
Bawtry Farm 16Acre 2002/2003
Bawtry Farm 11Acre 2000/2001

So when I put in Bawtry Farms 2000/2001 the total hectares for that season is 61

Hope this explains my situation a little bit clearer

Thank you for you help

Rachel

 

[rajeevnandanmishra]

Try to use this code
---------------------------

DIME Totals(1)
Totals(1)=0
SELECT sum(a.area) ;
FROM field_plot_static a, field_plot_Seasonal b ;
WHERE a.FARM_NAME = b.FARM_NAME AND ;
A.FIELD_NAME = B.FIELD_NAME AND ;
A.FARM_NAME = thisform.combo1.value ;
AND ALLT(b.SEASON)=thisform.distinct_values_combo2.Value ;
INTO ARRAY Totals

?Totals(1)

---------------------------
This will reduce your code and make it faster also.

 

[ChrisRChamberlain]

Rachel

The following code will not improve your result but will improve your coding style by using SCAN...ENDSCAN.

Answer = 0
totals = 0
SELECT Field_Plot_Seasonal
SCAN FOR fps_sorg = thisform.combo1.value AND fps_season = thisform.distinct_values_combo2.Value
[tab]SELECT Field_plot_static
[tab]SCAN FOR Fpd_sorg = Thisform.combo1.value
[tab][tab]Answer = fpd_farea
[tab][tab]Totals = totals+answer
[tab]ENDS
[tab]SELECT Field_Plot_Seasonal
ENDS

You can see from the example that you can nest a SCAN...ENDSCAN within a SCAN...ENDSCAN and SCAN FOR expr and you will always start with the first record that meets the logical expression or the first record if you are using one.

I suggest you re-examine your logic within the above framework, recode it, and post the result.

Chris

 

[rclarke]

that is it it works brilliantley

Thank you ever so much didn't even think of using a select statement let alone you could sum in one but I do now and I will remember it

Thank you again

Rachel

 

[rclarke]

Chris

Again I did not know 'scan, endscan' existed either so thank you I will use this in the future I am sure.

Rachel s-)

P.S It is one heck of a steep learning curve I am on So all help is really appreciated