Newbie question about using multi-checkbox value in a query (1 Viewer)

[alicezyk]

Hello All!
I am new to access forms.
I am trying to use the 2 checkboxs for my make table query.
Future cost checkbox and marginal cost checkbox.
If future cost is checked, then I want 00 as criteria for the field cost type in the query, if marginal is checked then 01. If both checked, then I want both.

Seems like a iif statement is not working in my query. I am trying to create an event now but I do not understand the VB codes..

Any help will be appreciated!

Thanks!

 

[BlueIshDan]

show us what you have so far. Thanks!

 

[alicezyk]

Hi I just have the checkbox right now. I do not understand VBA at all

 

[BlueIshDan]

Seems like a iif statement is not working in my query.

You don't even have a query yet?

 

[alicezyk]

You don't even have a query yet?

Oh I do.
It's a query that has a lot of other fields.

SELECT 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 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]![James Form]![Bid number])) AND ((O858IA_VWRDCAM.PRC_JOB_LST_RUN_DT) Between [forms]![James Form]![ICLOR Start] And [forms]![James Form]![ICLOR End]) AND ((O858IA_VWRDCAM.COS_CCL_MTH_TYP_CD)=IIf([Forms]![James Form]![Future cost]=True,"00")) AND ((Sum(O858IA_VWRDCAM.DEL_LOC_SMT_GRR_A))>0));

 

[BlueIshDan]

I formatted this as best I could.
I'll try and look into it for you a bit today if I find the time.

SELECT 
	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 
	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]![James Form]![Bid number]) ) AND 
		(	(O858IA_VWRDCAM.PRC_JOB_LST_RUN_DT) Between [forms]![James Form]![ICLOR Start] And [forms]![James Form]![ICLOR End] ) AND 
		(	(O858IA_VWRDCAM.COS_CCL_MTH_TYP_CD)=IIf([Forms]![James Form]![Future cost]=True,"00") ) AND 
		(	(Sum(O858IA_VWRDCAM.DEL_LOC_SMT_GRR_A))>0 ) 
	);

 

[alicezyk]

I formatted this as best I could.
I'll try and look into it for you a bit today if I find the time.

SELECT 
    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 
    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]![James Form]![Bid number]) ) AND 
        (    (O858IA_VWRDCAM.PRC_JOB_LST_RUN_DT) Between [forms]![James Form]![ICLOR Start] And [forms]![James Form]![ICLOR End] ) AND 
        (    (O858IA_VWRDCAM.COS_CCL_MTH_TYP_CD)=IIf([Forms]![James Form]![Future cost]=True,"00") ) AND 
        (    (Sum(O858IA_VWRDCAM.DEL_LOC_SMT_GRR_A))>0 ) 
    );

Thanks!! I tried using combo box now and it works. But I still need to be able to choose 2 options at the same time! So I am trying checkbox again..

 

[alicezyk]

Hello Dan,
What I have problem with is the criteria part in the query,
what should I put for the iif statement?
Future cost checkbox and marginal cost checkbox.
If future cost is checked, then I want 00 as criteria for the field cost type in the query, if marginal is checked then 01. If both checked, then I want both.
At least one of them will be checked.

Thanks so much in advance!

 

[alicezyk]

Hello Dan,
What I have problem with is the criteria part in the query,
what should I put for the iif statement?
Future cost checkbox and marginal cost checkbox.
If future cost is checked, then I want 00 as criteria for the field cost type in the query, if marginal is checked then 01. If both checked, then I want both.
At least one of them will be checked.

Thanks so much in advance!

Hello again,
Now my checkbox works.
IIf([Forms]![James Form]![Future cost]=True And [forms]![James Form]![LRM cost]=False,"00",IIf([Forms]![James Form]![Future cost]=False And [forms]![James Form]![LRM cost]=True,"01",IIf([Forms]![James Form]![Future cost]=True And [forms]![James Form]![LRM cost]=True,"00" Or "01")))

But the last part of the criteria, when 2 checkbox are checked I want 2 values, doesn't work
Appreciate if you could help