report changing value's visual basic 6

triple
11-03-2004, 09:28 AM
i use the date report from visual basic
and i have a rpttestbox called(txtsport_freq)
i also have a rptlabel called(Label55)
the data is collected by a dataenviroment(sql-statement)

what i want to do

if txtsport_freq = 1 then '<= here is somethings wrong
rptmedischfiche.Sections("medische_fiche_Detail").Controls("Label55").Caption = "hello world "
else
rptmedischfiche.Sections("medische_fiche_Detail").Controls("Label55").Caption = "bye world "
end if
the problem is how do i access the value from the txtsport_freq field

Dennis DVR
11-03-2004, 09:52 AM
i use the date report from visual basic
and i have a rpttestbox called(txtsport_freq)
i also have a rptlabel called(Label55)
the data is collected by a dataenviroment(sql-statement)

what i want to do

if txtsport_freq = 1 then '<= here is somethings wrong
rptmedischfiche.Sections("medische_fiche_Detail").Controls("Label55").Caption = "hello world "
else
rptmedischfiche.Sections("medische_fiche_Detail").Controls("Label55").Caption = "bye world "
end if
the problem is how do i access the value from the txtsport_freq field

you can't to that with data report, the efficient and proper way to that, is to add a conditional field in your SELECT statement in your DataEnvironment using IIF Function if you are using Access and CASE statement using SQL Server.

triple
11-03-2004, 09:58 AM
can u give a small example or a hint wher to find it ( iam ussing access)
thx

Dennis DVR
11-03-2004, 10:01 AM
can u give a small example or a hint wher to find it ( iam ussing access)
thx

are you using SELECT statement in your DataEnvironment? or you just selected the table from the Dropdown list?

triple
11-03-2004, 11:04 AM
it is an sql-statement ( select + parameter)

Dennis DVR
11-03-2004, 06:16 PM
it is an sql-statement ( select + parameter)

Suppose you want to add a conditional field in your select statement based on the value of field3

SELECT field1,field2,field3,IIF(field3<=1,"Hello","Hi") AS field4 WHERE field1 = ?


Now instead of using label control you will have to textbox control and bind the textbox control to field4.

triple
11-04-2004, 08:49 AM
still having some problems

in access the sqlstring works good but when i copy and paste them to the dataenviroment he says " unable to determin parameter info for the parameters, use the parameters tab to specify appropriot information"

when i do the sqlstring without parameters he tells me "dataprovider failed while executing a provider command"

some other thing the query is saved in the access database but does not show up in the view from de dataenviroment

can someone help

here is the sqlstring its long but there are many the same things


SELECT patienten.ptid, patienten.ptnaam, patienten.ptgeboortedatum, patienten.Lidnummer, mdfiche.welk_sport, Switch([sport_freq]=0,"Niet aktief",[sport_freq]=1,"Nu en dan ( 1 keer per week)",[sport_freq]=2,"Matig aktief (1 2 keer per week)",[sport_freq]=3,"aktief ( 3 4 keer per week)") AS sport_freqR, mdfiche.geschiedenissport, IIf(pers_hoog_choles=0,"N","J") AS pers_hoog_cholesR, IIf(pers_hoog_bloed=0,"N","J") AS pers_hoog_bloedR, IIf(pers_diabet=0,"N","J") AS pers_diabetR, IIf(pers_pijn_borst=0,"N","J") AS pers_pijn_borstR, IIf(pers_prob_hart=0,"N","J") AS pers_prob_hartR, IIf(pers_betablok=0,"N","J") AS pers_hoog_pers_betablokR, mdfiche.pers_medic_hart, mdfiche.pers_roken, IIf(pers_rook_stoppen=0,"N","J") AS pers_rook_stoppenR, IIf(famhartaanvvoor60=0,"N","J") AS famhartaanvvoor60R, IIf(famhartaanvna60=0,"N","J") AS famhartaanvna60R, IIf(famhoogbloed=0,"N","J") AS famhoogbloedR, IIf(famdiabet=0,"N","J") AS famdiabetR, mdfiche.letsel_rug_omschr, IIf(letsel_rugletselnek=-1,"Nek","/") AS letsel_rugletselnekR, IIf(letsel_rugletselnek=-1,IIf(letsel_rug_nek_soort=-1,"Acuut","Chronish"),"/") AS letsel_rug_nek_soortR, IIf(letsel_rugletselnek=-1,IIf(letsel_rug_nek_uitstraal=-1,"J","N"),"/") AS letsel_rug_nek_uitstraalR, IIf(letsel_rugletselnek=-1,letsel_rug_nek_behand,"/") AS letsel_rug_nek_behandlR, IIf(letsel_rugletselmidden=-1,"Middenrug","/") AS letsel_rugletselmiddenR, IIf(letsel_rugletselmidden=-1,IIf(letsel_rug_midden_soort=-1,"Acuut","Chronish"),"/") AS letsel_rug_midden_soortR, IIf(letsel_rugletselmidden=-1,IIf(letsel_rug_midden_uitstraal=-1,"J","N"),"/") AS letsel_rug_midden_uitstraalR, IIf(letsel_rugletselmidden=-1,letsel_rug_midden_behand,"/") AS letsel_rug_midden_behandR, IIf(letsel_rugletselonderrug=-1,"Lage rug","/") AS letsel_rugletselonderrugR, IIf(letsel_rugletselonderrug=-1,IIf(letsel_rug_laag_soort=-1,"Acuut","Chronish"),"/") AS letsel_rug_laag_soortR, IIf(letsel_rugletselonderrug=-1,IIf(letsel_rug_laag_uitstraal=-1,"J","N"),"/") AS letsel_rug_laag_uitstraalR, IIf(letsel_rugletselonderrug=-1,letsel_rug_laag_behandel,"/") AS letsel_rug_laag_behandlR, mdfiche.letsel_rug_ander_omschrijving, IIf(IsNull(letsel_rug_ander1_lok),"/",letsel_rug_ander1_lok) AS letsel_rug_ander1_lokR, IIf(IsNull(letsel_rug_ander1_lok),"/",IIf(letsel_rug_ander1_soort=-1,"Acuut","Chronish")) AS letsel_rug_ander1_soortR, IIf(IsNull(letsel_rug_ander2_lok),"/",letsel_rug_ander2_lok) AS letsel_rug_ander2_lokR, IIf(IsNull(letsel_rug_ander2_lok),"/",IIf(letsel_rug_ander2_soort=-1,"Acuut","Chronish")) AS letsel_rug_ander2_soortR, IIf(IsNull(letsel_rug_ander3_lok),"/",letsel_rug_ander3_lok) AS letsel_rug_ander3_lokR, IIf(IsNull(letsel_rug_ander3_lok),"/",IIf(letsel_rug_ander3_soort=-1,"Acuut","Chronish")) AS letsel_rug_ander3_soortR, IIf(IsNull(letsel_rug_ander4_lok),"/",letsel_rug_ander4_lok) AS letsel_rug_ander4_lokR, IIf(IsNull(letsel_rug_ander4_lok),"/",IIf(letsel_rug_ander4_soort=-1,"Acuut","Chronish")) AS letsel_rug_ander4_soortR, IIf(IsNull(letsel_rug_ander5_lok),"/",letsel_rug_ander5_lok) AS letsel_rug_ander5_lokR, IIf(IsNull(letsel_rug_ander5_lok),"/",IIf(letsel_rug_ander5_soort=-1,"Acuut","Chronish")) AS letsel_rug_ander5_soortR, IIf(Mid(doel_gezondheid,1,1)="1","doktersadvies","/") AS doktersadviesR, IIf(Mid(doel_gezondheid,2,1)="1","Stressmanagement","/") AS StressmanagementR, IIf(Mid(doel_gezondheid,3,1)="1","Beter slapen","/") AS BeterslapenR, IIf(Mid(doel_voorkomen,1,1)="1","Meer spiermassa","/") AS SpiermassaR, IIf(Mid(doel_voorkomen,2,1)="1","Vetverbranding","/") AS VetverbrandingR, IIf(Mid(doel_voorkomen,3,1)="1","Figuur verbeteren","/") AS FigverbeterenR, IIf(Mid(doel_prestatie,1,1)="1","Uithouding","/") AS UithoudingR, IIf(Mid(doel_prestatie,2,1)="1","Energieniveau","/") AS EnergieniveauR, IIf(Mid(doel_prestatie,3,1)="1","Lenigheid","/") AS LenigheidR, mdfiche.training_doeltraining, Switch([training_freq]=0,"1 maal per week",[training_freq]=1,"2 maal per week",[training_freq]=2,"3 maal per week",[training_freq]=3,"4 maal per week",[training_freq]=4,"meer dan 4 maal per week") AS trainning_freqR, mdfiche.trainingtijd
FROM patienten INNER JOIN mdfiche ON patienten.ptid = mdfiche.patid
WHERE (((patienten.ptid)=1));

the last 1 is normal the parameter and replaced by ?

Dennis DVR
11-04-2004, 09:20 AM
still having some problems

in access the sqlstring works good but when i copy and paste them to the dataenviroment he says " unable to determin parameter info for the parameters, use the parameters tab to specify appropriot information"

when i do the sqlstring without parameters he tells me "dataprovider failed while executing a provider command"

some other thing the query is saved in the access database but does not show up in the view from de dataenviroment

can someone help

here is the sqlstring its long but there are many the same things


SELECT patienten.ptid, patienten.ptnaam, patienten.ptgeboortedatum, patienten.Lidnummer, mdfiche.welk_sport, Switch([sport_freq]=0,"Niet aktief",[sport_freq]=1,"Nu en dan ( 1 keer per week)",[sport_freq]=2,"Matig aktief (1 2 keer per week)",[sport_freq]=3,"aktief ( 3 4 keer per week)") AS sport_freqR, mdfiche.geschiedenissport, IIf(pers_hoog_choles=0,"N","J") AS pers_hoog_cholesR, IIf(pers_hoog_bloed=0,"N","J") AS pers_hoog_bloedR, IIf(pers_diabet=0,"N","J") AS pers_diabetR, IIf(pers_pijn_borst=0,"N","J") AS pers_pijn_borstR, IIf(pers_prob_hart=0,"N","J") AS pers_prob_hartR, IIf(pers_betablok=0,"N","J") AS pers_hoog_pers_betablokR, mdfiche.pers_medic_hart, mdfiche.pers_roken, IIf(pers_rook_stoppen=0,"N","J") AS pers_rook_stoppenR, IIf(famhartaanvvoor60=0,"N","J") AS famhartaanvvoor60R, IIf(famhartaanvna60=0,"N","J") AS famhartaanvna60R, IIf(famhoogbloed=0,"N","J") AS famhoogbloedR, IIf(famdiabet=0,"N","J") AS famdiabetR, mdfiche.letsel_rug_omschr, IIf(letsel_rugletselnek=-1,"Nek","/") AS letsel_rugletselnekR, IIf(letsel_rugletselnek=-1,IIf(letsel_rug_nek_soort=-1,"Acuut","Chronish"),"/") AS letsel_rug_nek_soortR, IIf(letsel_rugletselnek=-1,IIf(letsel_rug_nek_uitstraal=-1,"J","N"),"/") AS letsel_rug_nek_uitstraalR, IIf(letsel_rugletselnek=-1,letsel_rug_nek_behand,"/") AS letsel_rug_nek_behandlR, IIf(letsel_rugletselmidden=-1,"Middenrug","/") AS letsel_rugletselmiddenR, IIf(letsel_rugletselmidden=-1,IIf(letsel_rug_midden_soort=-1,"Acuut","Chronish"),"/") AS letsel_rug_midden_soortR, IIf(letsel_rugletselmidden=-1,IIf(letsel_rug_midden_uitstraal=-1,"J","N"),"/") AS letsel_rug_midden_uitstraalR, IIf(letsel_rugletselmidden=-1,letsel_rug_midden_behand,"/") AS letsel_rug_midden_behandR, IIf(letsel_rugletselonderrug=-1,"Lage rug","/") AS letsel_rugletselonderrugR, IIf(letsel_rugletselonderrug=-1,IIf(letsel_rug_laag_soort=-1,"Acuut","Chronish"),"/") AS letsel_rug_laag_soortR, IIf(letsel_rugletselonderrug=-1,IIf(letsel_rug_laag_uitstraal=-1,"J","N"),"/") AS letsel_rug_laag_uitstraalR, IIf(letsel_rugletselonderrug=-1,letsel_rug_laag_behandel,"/") AS letsel_rug_laag_behandlR, mdfiche.letsel_rug_ander_omschrijving, IIf(IsNull(letsel_rug_ander1_lok),"/",letsel_rug_ander1_lok) AS letsel_rug_ander1_lokR, IIf(IsNull(letsel_rug_ander1_lok),"/",IIf(letsel_rug_ander1_soort=-1,"Acuut","Chronish")) AS letsel_rug_ander1_soortR, IIf(IsNull(letsel_rug_ander2_lok),"/",letsel_rug_ander2_lok) AS letsel_rug_ander2_lokR, IIf(IsNull(letsel_rug_ander2_lok),"/",IIf(letsel_rug_ander2_soort=-1,"Acuut","Chronish")) AS letsel_rug_ander2_soortR, IIf(IsNull(letsel_rug_ander3_lok),"/",letsel_rug_ander3_lok) AS letsel_rug_ander3_lokR, IIf(IsNull(letsel_rug_ander3_lok),"/",IIf(letsel_rug_ander3_soort=-1,"Acuut","Chronish")) AS letsel_rug_ander3_soortR, IIf(IsNull(letsel_rug_ander4_lok),"/",letsel_rug_ander4_lok) AS letsel_rug_ander4_lokR, IIf(IsNull(letsel_rug_ander4_lok),"/",IIf(letsel_rug_ander4_soort=-1,"Acuut","Chronish")) AS letsel_rug_ander4_soortR, IIf(IsNull(letsel_rug_ander5_lok),"/",letsel_rug_ander5_lok) AS letsel_rug_ander5_lokR, IIf(IsNull(letsel_rug_ander5_lok),"/",IIf(letsel_rug_ander5_soort=-1,"Acuut","Chronish")) AS letsel_rug_ander5_soortR, IIf(Mid(doel_gezondheid,1,1)="1","doktersadvies","/") AS doktersadviesR, IIf(Mid(doel_gezondheid,2,1)="1","Stressmanagement","/") AS StressmanagementR, IIf(Mid(doel_gezondheid,3,1)="1","Beter slapen","/") AS BeterslapenR, IIf(Mid(doel_voorkomen,1,1)="1","Meer spiermassa","/") AS SpiermassaR, IIf(Mid(doel_voorkomen,2,1)="1","Vetverbranding","/") AS VetverbrandingR, IIf(Mid(doel_voorkomen,3,1)="1","Figuur verbeteren","/") AS FigverbeterenR, IIf(Mid(doel_prestatie,1,1)="1","Uithouding","/") AS UithoudingR, IIf(Mid(doel_prestatie,2,1)="1","Energieniveau","/") AS EnergieniveauR, IIf(Mid(doel_prestatie,3,1)="1","Lenigheid","/") AS LenigheidR, mdfiche.training_doeltraining, Switch([training_freq]=0,"1 maal per week",[training_freq]=1,"2 maal per week",[training_freq]=2,"3 maal per week",[training_freq]=3,"4 maal per week",[training_freq]=4,"meer dan 4 maal per week") AS trainning_freqR, mdfiche.trainingtijd
FROM patienten INNER JOIN mdfiche ON patienten.ptid = mdfiche.patid
WHERE (((patienten.ptid)=1));

the last 1 is normal the parameter and replaced by ?

I would suggest to copy the SQL Statement to the dataenvironment rather than calling the query from the dataenvironment, and replace all the switch function with IIF function, since switch isn't a recognize function in VB, and replace the WHERE clause with

...WHERE patienten.ptid=?


but you have to make sure that you are not using ODBC otherwise the parameter will not be properly set.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum