Querying 2 checkboxs in a form (1 Viewer)

SELECT "ICLOR" AS Source, O858IA_VWRDCAM.NVP_BID_NR, O858IA_VWRDCAM.SRC_CNY_CD, O858IA_VWRDCAM.PRC_JOB_LST_RUN_DT, O858IA_VWRDCAM.COS_CCL_MTH_TYP_CD, O858IA_VWRDCAM.PKG_CHA_TYP_CD, O858IA_VWRDCAM.SVC_TYP_CD, O858IA_VWRDCAM.MVM_DRC_CD, O858IA_VWRDCAM.ORG_CNY_IAT_CD, O858IA_VWRDCAM.DTN_CNY_IAT_CD, ([SMT_BIL_WGT_QY]*O858IA_VWRDCAM!DEL_LOC_PKG_QY) AS Billed_Wgt, Sum(([SMT_BIL_WGT_QY]*O858IA_VWRDCAM!DEL_LOC_PKG_QY)) AS Sum_Billed_Wgt, Sum(([LNE_PKG_AVG_CU_QY]*O858IA_VWRDCAM!DEL_LOC_PKG_QY)) AS Cube, Sum(O858IA_VWRDCAM.DEL_LOC_PKG_QY) AS SumOfDEL_LOC_PKG_QY, Sum(O858IA_VWRDCAM.DEL_LOC_SMT_QY) AS SumOfDEL_LOC_SMT_QY, Sum(O858IA_VWRDCAM.DEL_LOC_SMT_GRR_A) AS SumOfDEL_LOC_SMT_GRR_A, Sum(O858IA_VWRDCAM.DEL_LOC_SMT_NRV_A) AS SumOfDEL_LOC_SMT_NRV_A, Sum((([pdc_pus_fxc_cpp_a]+[pdc_pus_tvl_cpp_a]+[pdc_pus_vhc_cpp_a]+[pdc_pus_pal_fxc_a]+[pdc_pus_pal_vhc_a]+[pdc_pus_cki_cpp_a]+[pdc_pus_oth_cpp_a]+[pdc_pus_smt_avg_a]+[pdc_pus_smt_vhc_a]+[pdc_pus_grr_a]+[pdc_pus_xtn_cpp_a])*[del_loc_pkg_qy])) AS PU, Sum((([pdc_ls_rgr_cpp_a]+[pdc_ls_irr_cpp_a])*[del_loc_pkg_qy])) AS LS, Sum((([pdc_snk_cs_rgr_a]+[pdc_snk_cs_irr_a]+[pdc_snk_cs_fxc_a]+[pdc_cs_smt_fxc_a]+[pdc_cs_smt_vhc_a]+[pdc_ls_tns_vhc_a])*[del_loc_pkg_qy])) AS CS, Sum((([pdc_org_snk_gnf_a]+[pdc_dtn_snk_gnf_a]+[pdc_snk_ldf_cpp_a]+[pdc_snk_tfc_gnf_a]+[pdc_snk_tlr_cpp_a]+[pdc_snk_sxb_ldf_a])*[del_loc_pkg_qy])) AS FDR, Sum((([pdc_snk_rmp_fxc_a]+[pdc_snk_rmp_vrc_a])*[del_loc_pkg_qy])) AS RMP, Sum((([pdc_snk_jet_arf_a]+[pdc_snk_jet_fxc_a]+[pdc_snk_jet_vrc_a]+[pdc_org_snk_arf_a]+[pdc_dtn_snk_arf_a]+[pdc_snk_jet_wgt_a]+[pdc_org_snk_asf_a]+[pdc_dtn_snk_asf_a])*[del_loc_pkg_qy])) AS AFD, Sum((([pdc_prl_rgr_cpp_a]+[pdc_prl_irr_cpp_a]+[sml_pkg_pdv_cos_a])*[del_loc_pkg_qy])) AS PRL, Sum((([pdc_ds_fxc_cpp_a]+[pdc_ds_tvl_cpp_a]+[pdc_ds_vhc_cpp_a]+[pdc_ds_pal_fxc_a]+[pdc_ds_pal_vhc_a]+[pdc_ds_cki_cpp_a]+[pdc_ds_oth_cpp_a]+[pdc_ds_sag_cpp_a]+[pdc_ds_smt_avg_a]+[pdc_ds_smt_vhc_a]+[pdc_ds_grr_a]+[pdc_ds_xtn_cpp_a]+[sml_del_vhc_a])*[del_loc_pkg_qy])) AS DEL, Sum((([pdc_org_bkr_smt_a]+[pdc_dtn_bkr_smt_a]))) AS BKR, Sum((([pdc_frq_nop_cpp_a]+[pdc_vnr_nop_cpp_a]+[pdc_org_nop_grr_a]+[pdc_org_nop_a]+[pdc_org_nop_smt_a]+[pdc_org_igs_a]+[pdc_org_igs_smt_a]+[pdc_org_igs_grr_a]+[pdc_org_ovh_grr_a]+[pdc_dtn_nop_grr_a]+[pdc_dtn_nop_a]+[pdc_dtn_nop_smt_a]+[pdc_dtn_igs_a]+[pdc_dtn_igs_smt_a]+[pdc_dtn_igs_grr_a]+[pdc_dtn_ovh_grr_a]+[pdc_oth_nop_a]+[pdc_oth_nop_smt_a]+[pdc_oth_nop_grr_a])*[del_loc_pkg_qy])) AS NOP, O858IA_VWRDCAM.MIN_NET_RVN_APL_IR INTO RAW1
FROM O858IA_VWRDCAM
GROUP BY "ICLOR", O858IA_VWRDCAM.NVP_BID_NR, O858IA_VWRDCAM.SRC_CNY_CD, O858IA_VWRDCAM.PRC_JOB_LST_RUN_DT, O858IA_VWRDCAM.COS_CCL_MTH_TYP_CD, O858IA_VWRDCAM.PKG_CHA_TYP_CD, O858IA_VWRDCAM.SVC_TYP_CD, O858IA_VWRDCAM.MVM_DRC_CD, O858IA_VWRDCAM.ORG_CNY_IAT_CD, O858IA_VWRDCAM.DTN_CNY_IAT_CD, ([SMT_BIL_WGT_QY]*O858IA_VWRDCAM!DEL_LOC_PKG_QY), O858IA_VWRDCAM.MIN_NET_RVN_APL_IR
HAVING (((O858IA_VWRDCAM.NVP_BID_NR)=Trim([forms]![TPTC Analyzer]![P Bid number])) AND ((O858IA_VWRDCAM.PRC_JOB_LST_RUN_DT) Between [forms]![TPTC Analyzer]![P ICLOR Start] And [forms]![TPTC Analyzer]![P ICLOR End]) AND ((O858IA_VWRDCAM.COS_CCL_MTH_TYP_CD)=IIf([Forms]![TPTC Analyzer]![P Future cost]=True And [forms]![TPTC Analyzer]![P LRM cost]=False,"00",IIf([Forms]![TPTC Analyzer]![P Future cost]=False And [forms]![TPTC Analyzer]![P LRM cost]=True,"01",IIf([Forms]![TPTC Analyzer]![P Future cost]=True And [forms]![TPTC Analyzer]![P LRM cost]=True,"00")))) AND ((Sum(O858IA_VWRDCAM.DEL_LOC_SMT_GRR_A))>0)) OR (((Sum(O858IA_VWRDCAM.DEL_LOC_SMT_GRR_A))>0) AND
(((O858IA_VWRDCAM.NVP_BID_NR)=Trim([forms]![TPTC Analyzer]![P Bid number])) AND ((O858IA_VWRDCAM.PRC_JOB_LST_RUN_DT) Between [forms]![TPTC Analyzer]![P ICLOR Start] And [forms]![TPTC Analyzer]![P ICLOR End]) AND ((O858IA_VWRDCAM.COS_CCL_MTH_TYP_CD=IIf([Forms]![TPTC Analyzer]![P Future cost]=True And [forms]![TPTC Analyzer]![P LRM cost]=True,"01")))));

Click to expand...

JHB said:

Try it now, (don't know it it runs, can't test it without data, database is also attached).

Click to expand...

themurph2000 said:

Create the two text boxes first on your form. Then click on the first checkbox and, if the Properties pane isn't up, hit [ALT]-[ENTER] on your keyboard to bring it up. Click on the Event tab and the AfterUpdate row. There should be a button with an ellipsis (...) at the right. Click that and choose "Code Builder" from the list of options. You should then get a new window with text that looks similar to this...

Code:Copy to clipboard

Private Sub CheckBox1_AfterUpdate()
 
End Sub

Alter the text so you get the following, replacing what I write with the names of your text box and check box in the form.

Code:Copy to clipboard

Private Sub CheckBox1_AfterUpdate()
If Me!CheckBox1 = True
Then Me!TextBox1 = "00"
End If
 
End Sub

Then do the same thing for the other check box and text box. NOTE: if anybody saw a mistake in there, please correct it. I'm no expert on this, I freely admit.

Click to expand...