microprolog-p96s
پنج شنبه 28 مرداد 1389, 06:48 صبح
سلام
من جند تا کد SQL دارم که می خوام توی اکسس به عنوان کد vb برای سورس فرم استفاده کنم . که هر بار یکی از اونها به عنوان رکورد سورس به فرم وصل بشه . اما هر کاری کردم نشد و به کد SQL ایراد میگرفت و چند تا از خط های کد قرمز رنگ میشد . نمونه کد رو می زارم اگه امکانش هست برام تصحیح ش کنید .
SELECT tbl_Estate_aGroup.No_domain, tbl_Estate_aGroup.Date, tbl_devolution_sale.price_koli, tbl_estate_1.metr_zamin, tbl_estate_1.metr_sakht, tbl_estate_1.[year_ sakht], tbl_estate_1.qty_khab, tbl_estate_1.qty_tab, tbl_Estate_aGroup.add_bystreet, tbl_Estate_aGroup.add_street, tbl_Estate_aGroup.retail, tbl_Estate_aGroup.exchange, tbl_Estate_aGroup.loan, tbl_Estate_aGroup.position_archive, tbl_Estate_aGroup.devolution_sale, tbl_Estate_aGroup.operations, tbl_Estate_aGroup.estate, tbl_Estate_aGroup.estate_group, tbl_estate_1.estate_type, tbl_estate_1.estate_control, tbl_Estate_aGroup.add_province, tbl_Estate_aGroup.add_county, tbl_Estate_aGroup.add_basic, tbl_Estate_aGroup.add_region, tbl_Estate_aGroup.location, tbl_Estate_aGroup.position_sanad, tbl_Estate_aGroup.position_passage FROM (tbl_Estate_aGroup INNER JOIN tbl_estate_1 ON tbl_Estate_aGroup.No_domain = tbl_estate_1.No_domain) INNER JOIN tbl_devolution_sale ON tbl_estate_1.No_domain = tbl_devolution_sale.No_domain GROUP BY tbl_Estate_aGroup.No_domain, tbl_Estate_aGroup.Date, tbl_devolution_sale.price_koli, tbl_estate_1.metr_zamin, tbl_estate_1.metr_sakht, tbl_estate_1.[year_ sakht], tbl_estate_1.qty_khab, tbl_estate_1.qty_tab, tbl_Estate_aGroup.add_bystreet, tbl_Estate_aGroup.add_street, tbl_Estate_aGroup.retail, tbl_Estate_aGroup.exchange, tbl_Estate_aGroup.loan, tbl_Estate_aGroup.position_archive, tbl_Estate_aGroup.devolution_sale, tbl_Estate_aGroup.operations, tbl_Estate_aGroup.estate, tbl_Estate_aGroup.estate_group, tbl_estate_1.estate_type, tbl_estate_1.estate_control, tbl_Estate_aGroup.add_province, tbl_Estate_aGroup.add_county, tbl_Estate_aGroup.add_basic, tbl_Estate_aGroup.add_region, tbl_Estate_aGroup.location, tbl_Estate_aGroup.position_sanad, tbl_Estate_aGroup.position_passage HAVING (((tbl_devolution_sale.price_koli) Between (nz([Forms]![frm_Search]![price_koli_Down])) And (nz([Forms]![frm_Search]![price_koli_Up]))) AND ((tbl_estate_1.metr_zamin) Between (nz([Forms]![frm_Search]![metr_zamin_Down])) And (nz([Forms]![frm_Search]![metr_zamin_Up]))) AND ((tbl_estate_1.metr_sakht) Between (nz([Forms]![frm_Search]![metr_sakht_Down])) And (nz([Forms]![frm_Search]![metr_sakht_Up]))) AND ((tbl_estate_1.[year_ sakht]) Between (nz([Forms]![frm_Search]![year_sakht_Down])) And (nz([Forms]![frm_Search]![year_sakht_Up]))) AND ((tbl_estate_1.qty_khab) Between (nz([Forms]![frm_Search]![qty_khab_Down])) And (nz([Forms]![frm_Search]![qty_khab_Up]))) AND ((tbl_estate_1.qty_tab) Between (nz([Forms]![frm_Search]![qty_tab_Down])) And (nz([Forms]![frm_Search]![qty_tab_Up]))) AND ((tbl_Estate_aGroup.retail)=IIf(((nz([Forms].[frm_Search].[retail]))<3),IIf(((nz([Forms].[frm_Search].[retail]))=2),((nz([Forms].[frm_Search].[retail]))=2),((nz([Forms].[frm_Search].[retail]))=1)),([tbl_Estate_aGroup].[retail]))) AND ((tbl_Estate_aGroup.exchange)=IIf(((nz([Forms].[frm_Search].[exchange]))="" Or ([tbl_Estate_aGroup].[exchange])=IsNull(nz([Forms].[frm_Search].[exchange]))),nz([Forms].[frm_Search].[exchange]),([tbl_Estate_aGroup].[exchange]))) AND ((tbl_Estate_aGroup.loan)=IIf(((nz([Forms].[frm_Search].[loan]))="" Or ([tbl_Estate_aGroup].[loan])=IsNull(nz([Forms].[frm_Search].[loan]))),nz([Forms].[frm_Search].[loan]),([tbl_Estate_aGroup].[loan]))) AND ((tbl_Estate_aGroup.position_archive)=IIf(((nz([Forms].[frm_Search].[position_archive]))="" Or ([tbl_Estate_aGroup].[position_archive])=IsNull(nz([Forms].[frm_Search].[position_archive]))),([tbl_Estate_aGroup].[position_archive]),nz([Forms].[frm_Search].[position_archive]))) AND ((tbl_Estate_aGroup.devolution_sale)=(nz([Forms].[frm_Search].[devolution_sale]))) AND ((tbl_Estate_aGroup.operations)=1) AND ((tbl_Estate_aGroup.estate_group)=(nz([Forms]![frm_Search]![estate_group]))) AND ((tbl_estate_1.estate_type)=IIf(((nz([Forms].[frm_Search].[estate_type]))="" Or ([tbl_estate_1].[estate_type])=IsNull(nz([Forms].[frm_Search].[estate_type]))),([tbl_estate_1].[estate_type]),nz([Forms].[frm_Search].[estate_type]))) AND ((tbl_estate_1.estate_control)=IIf(((nz([Forms].[frm_Search].[estate_control]))="" Or ([tbl_estate_1].[estate_control])=IsNull(nz([Forms].[frm_Search].[estate_control]))),([tbl_estate_1].[estate_control]),nz([Forms].[frm_Search].[estate_control]))) AND ((tbl_Estate_aGroup.add_province)=IIf(((nz([Forms].[frm_Search].[add_province]))="" Or ([tbl_Estate_aGroup].[add_province])=IsNull(nz([Forms].[frm_Search].[add_province]))),([tbl_Estate_aGroup].[add_province]),nz([Forms].[frm_Search].[add_province]))) AND ((tbl_Estate_aGroup.add_county)=IIf(((nz([Forms].[frm_Search].[add_county]))="" Or ([tbl_Estate_aGroup].[add_county])=IsNull(nz([Forms].[frm_Search].[add_county]))),([tbl_Estate_aGroup].[add_county]),nz([Forms].[frm_Search].[add_county]))) AND ((tbl_Estate_aGroup.add_basic)=IIf(((nz([Forms].[frm_Search].[add_basic]))="" Or ([tbl_Estate_aGroup].[add_basic])=IsNull(nz([Forms].[frm_Search].[add_basic]))),([tbl_Estate_aGroup].[add_basic]),nz([Forms].[frm_Search].[add_basic]))) AND ((tbl_Estate_aGroup.add_region)=IIf(((nz([Forms].[frm_Search].[add_region]))="" Or ([tbl_Estate_aGroup].[add_region])=IsNull(nz([Forms].[frm_Search].[add_region]))),([tbl_Estate_aGroup].[add_region]),nz([Forms].[frm_Search].[add_region]))) AND ((tbl_Estate_aGroup.location)=IIf(((nz([Forms].[frm_Search].[location]))="" Or ([tbl_Estate_aGroup].[location])=IsNull(nz([Forms].[frm_Search].[location]))),([tbl_Estate_aGroup].[location]),nz([Forms].[frm_Search].[location]))) AND ((tbl_Estate_aGroup.position_sanad)=IIf(((nz([Forms].[frm_Search].[position_sanad]))="" Or ([tbl_Estate_aGroup].[position_sanad])=IsNull(nz([Forms].[frm_Search].[position_sanad]))),([tbl_Estate_aGroup].[position_sanad]),nz([Forms].[frm_Search].[position_sanad]))) AND ((tbl_Estate_aGroup.position_passage)=IIf(((nz([Forms].[frm_Search].[position_passage]))="" Or ([tbl_Estate_aGroup].[position_passage])=IsNull(nz([Forms].[frm_Search].[position_passage]))),([tbl_Estate_aGroup].[position_passage]),nz([Forms].[frm_Search].[position_passage]))));
باز هم از اساتید تشکر میکنم .
من جند تا کد SQL دارم که می خوام توی اکسس به عنوان کد vb برای سورس فرم استفاده کنم . که هر بار یکی از اونها به عنوان رکورد سورس به فرم وصل بشه . اما هر کاری کردم نشد و به کد SQL ایراد میگرفت و چند تا از خط های کد قرمز رنگ میشد . نمونه کد رو می زارم اگه امکانش هست برام تصحیح ش کنید .
SELECT tbl_Estate_aGroup.No_domain, tbl_Estate_aGroup.Date, tbl_devolution_sale.price_koli, tbl_estate_1.metr_zamin, tbl_estate_1.metr_sakht, tbl_estate_1.[year_ sakht], tbl_estate_1.qty_khab, tbl_estate_1.qty_tab, tbl_Estate_aGroup.add_bystreet, tbl_Estate_aGroup.add_street, tbl_Estate_aGroup.retail, tbl_Estate_aGroup.exchange, tbl_Estate_aGroup.loan, tbl_Estate_aGroup.position_archive, tbl_Estate_aGroup.devolution_sale, tbl_Estate_aGroup.operations, tbl_Estate_aGroup.estate, tbl_Estate_aGroup.estate_group, tbl_estate_1.estate_type, tbl_estate_1.estate_control, tbl_Estate_aGroup.add_province, tbl_Estate_aGroup.add_county, tbl_Estate_aGroup.add_basic, tbl_Estate_aGroup.add_region, tbl_Estate_aGroup.location, tbl_Estate_aGroup.position_sanad, tbl_Estate_aGroup.position_passage FROM (tbl_Estate_aGroup INNER JOIN tbl_estate_1 ON tbl_Estate_aGroup.No_domain = tbl_estate_1.No_domain) INNER JOIN tbl_devolution_sale ON tbl_estate_1.No_domain = tbl_devolution_sale.No_domain GROUP BY tbl_Estate_aGroup.No_domain, tbl_Estate_aGroup.Date, tbl_devolution_sale.price_koli, tbl_estate_1.metr_zamin, tbl_estate_1.metr_sakht, tbl_estate_1.[year_ sakht], tbl_estate_1.qty_khab, tbl_estate_1.qty_tab, tbl_Estate_aGroup.add_bystreet, tbl_Estate_aGroup.add_street, tbl_Estate_aGroup.retail, tbl_Estate_aGroup.exchange, tbl_Estate_aGroup.loan, tbl_Estate_aGroup.position_archive, tbl_Estate_aGroup.devolution_sale, tbl_Estate_aGroup.operations, tbl_Estate_aGroup.estate, tbl_Estate_aGroup.estate_group, tbl_estate_1.estate_type, tbl_estate_1.estate_control, tbl_Estate_aGroup.add_province, tbl_Estate_aGroup.add_county, tbl_Estate_aGroup.add_basic, tbl_Estate_aGroup.add_region, tbl_Estate_aGroup.location, tbl_Estate_aGroup.position_sanad, tbl_Estate_aGroup.position_passage HAVING (((tbl_devolution_sale.price_koli) Between (nz([Forms]![frm_Search]![price_koli_Down])) And (nz([Forms]![frm_Search]![price_koli_Up]))) AND ((tbl_estate_1.metr_zamin) Between (nz([Forms]![frm_Search]![metr_zamin_Down])) And (nz([Forms]![frm_Search]![metr_zamin_Up]))) AND ((tbl_estate_1.metr_sakht) Between (nz([Forms]![frm_Search]![metr_sakht_Down])) And (nz([Forms]![frm_Search]![metr_sakht_Up]))) AND ((tbl_estate_1.[year_ sakht]) Between (nz([Forms]![frm_Search]![year_sakht_Down])) And (nz([Forms]![frm_Search]![year_sakht_Up]))) AND ((tbl_estate_1.qty_khab) Between (nz([Forms]![frm_Search]![qty_khab_Down])) And (nz([Forms]![frm_Search]![qty_khab_Up]))) AND ((tbl_estate_1.qty_tab) Between (nz([Forms]![frm_Search]![qty_tab_Down])) And (nz([Forms]![frm_Search]![qty_tab_Up]))) AND ((tbl_Estate_aGroup.retail)=IIf(((nz([Forms].[frm_Search].[retail]))<3),IIf(((nz([Forms].[frm_Search].[retail]))=2),((nz([Forms].[frm_Search].[retail]))=2),((nz([Forms].[frm_Search].[retail]))=1)),([tbl_Estate_aGroup].[retail]))) AND ((tbl_Estate_aGroup.exchange)=IIf(((nz([Forms].[frm_Search].[exchange]))="" Or ([tbl_Estate_aGroup].[exchange])=IsNull(nz([Forms].[frm_Search].[exchange]))),nz([Forms].[frm_Search].[exchange]),([tbl_Estate_aGroup].[exchange]))) AND ((tbl_Estate_aGroup.loan)=IIf(((nz([Forms].[frm_Search].[loan]))="" Or ([tbl_Estate_aGroup].[loan])=IsNull(nz([Forms].[frm_Search].[loan]))),nz([Forms].[frm_Search].[loan]),([tbl_Estate_aGroup].[loan]))) AND ((tbl_Estate_aGroup.position_archive)=IIf(((nz([Forms].[frm_Search].[position_archive]))="" Or ([tbl_Estate_aGroup].[position_archive])=IsNull(nz([Forms].[frm_Search].[position_archive]))),([tbl_Estate_aGroup].[position_archive]),nz([Forms].[frm_Search].[position_archive]))) AND ((tbl_Estate_aGroup.devolution_sale)=(nz([Forms].[frm_Search].[devolution_sale]))) AND ((tbl_Estate_aGroup.operations)=1) AND ((tbl_Estate_aGroup.estate_group)=(nz([Forms]![frm_Search]![estate_group]))) AND ((tbl_estate_1.estate_type)=IIf(((nz([Forms].[frm_Search].[estate_type]))="" Or ([tbl_estate_1].[estate_type])=IsNull(nz([Forms].[frm_Search].[estate_type]))),([tbl_estate_1].[estate_type]),nz([Forms].[frm_Search].[estate_type]))) AND ((tbl_estate_1.estate_control)=IIf(((nz([Forms].[frm_Search].[estate_control]))="" Or ([tbl_estate_1].[estate_control])=IsNull(nz([Forms].[frm_Search].[estate_control]))),([tbl_estate_1].[estate_control]),nz([Forms].[frm_Search].[estate_control]))) AND ((tbl_Estate_aGroup.add_province)=IIf(((nz([Forms].[frm_Search].[add_province]))="" Or ([tbl_Estate_aGroup].[add_province])=IsNull(nz([Forms].[frm_Search].[add_province]))),([tbl_Estate_aGroup].[add_province]),nz([Forms].[frm_Search].[add_province]))) AND ((tbl_Estate_aGroup.add_county)=IIf(((nz([Forms].[frm_Search].[add_county]))="" Or ([tbl_Estate_aGroup].[add_county])=IsNull(nz([Forms].[frm_Search].[add_county]))),([tbl_Estate_aGroup].[add_county]),nz([Forms].[frm_Search].[add_county]))) AND ((tbl_Estate_aGroup.add_basic)=IIf(((nz([Forms].[frm_Search].[add_basic]))="" Or ([tbl_Estate_aGroup].[add_basic])=IsNull(nz([Forms].[frm_Search].[add_basic]))),([tbl_Estate_aGroup].[add_basic]),nz([Forms].[frm_Search].[add_basic]))) AND ((tbl_Estate_aGroup.add_region)=IIf(((nz([Forms].[frm_Search].[add_region]))="" Or ([tbl_Estate_aGroup].[add_region])=IsNull(nz([Forms].[frm_Search].[add_region]))),([tbl_Estate_aGroup].[add_region]),nz([Forms].[frm_Search].[add_region]))) AND ((tbl_Estate_aGroup.location)=IIf(((nz([Forms].[frm_Search].[location]))="" Or ([tbl_Estate_aGroup].[location])=IsNull(nz([Forms].[frm_Search].[location]))),([tbl_Estate_aGroup].[location]),nz([Forms].[frm_Search].[location]))) AND ((tbl_Estate_aGroup.position_sanad)=IIf(((nz([Forms].[frm_Search].[position_sanad]))="" Or ([tbl_Estate_aGroup].[position_sanad])=IsNull(nz([Forms].[frm_Search].[position_sanad]))),([tbl_Estate_aGroup].[position_sanad]),nz([Forms].[frm_Search].[position_sanad]))) AND ((tbl_Estate_aGroup.position_passage)=IIf(((nz([Forms].[frm_Search].[position_passage]))="" Or ([tbl_Estate_aGroup].[position_passage])=IsNull(nz([Forms].[frm_Search].[position_passage]))),([tbl_Estate_aGroup].[position_passage]),nz([Forms].[frm_Search].[position_passage]))));
باز هم از اساتید تشکر میکنم .