Querying 2 checkboxs in a form (1 Viewer)

alicezyk

Registered User.
Local time
Today, 06:21
Joined
Oct 16, 2014
Messages
18
Hello everyone.
I posted in the form section but the problem is not solved.
So I have 2 checkboxs in a form.
Future cost and LRM cost.
When future cost is checked, I want the criteria in my make table query to be 00, when LRM cost checked, I want the criteria to be 01.
But the problem is when they are both checked I can't get 00 or 01 at the same time.

I have tried different ways but none of them seem to work because with IFF, can only return one value.
I have tried to put the 2 iff in criteria and the or criteria below it exceeds the max time.
Your help is much appreciated!

:p:)
the criteria I have right now is:
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"))
 
Last edited:

llkhoutx

Registered User.
Local time
Today, 08:21
Joined
Feb 26, 2001
Messages
4,018
Aren't the iifs independent of one another, that is, do they really need to be nested?

K.I.S.S.
 

alicezyk

Registered User.
Local time
Today, 06:21
Joined
Oct 16, 2014
Messages
18
Aren't the iifs independent of one another, that is, do they really need to be nested?

K.I.S.S.

Yes they are independent I tried " and" and " or" both don't work either. 😥how can I make it work :( thanks for replying
 

llkhoutx

Registered User.
Local time
Today, 08:21
Joined
Feb 26, 2001
Messages
4,018
Test one, if false, then the other.

if ... then

elseif ...

else
'error message
end if
 

alicezyk

Registered User.
Local time
Today, 06:21
Joined
Oct 16, 2014
Messages
18
Test one, if false, then the other.

if ... then

elseif ...

else
'error message
end if

I think in access I can only use IFF?
Also the problem with if statement is the return value cannot be 2 value "00" and "01". :confused:
 

themurph2000

Fat, drunk, and stupid
Local time
Today, 08:21
Joined
Sep 24, 2007
Messages
181
I think in access I can only use IFF?
Also the problem with if statement is the return value cannot be 2 value "00" and "01". :confused:

In Access, you can use two IFF statements in a nested format. That way, there's only one value returned.

iff(condition, iff(condition2, truepart, falsepart))

In your case, unfortunately, what if someone checks both boxes? Just to be safe, you may want to put the following VBA code in the AfterUpdate of each checkbox

Private Sub Check1_AfterUpdate()
Me!Check2 = False
End Sub

Private Sub Check2_AfterUpdate()
Me!Check1 = False
End Sub
 

alicezyk

Registered User.
Local time
Today, 06:21
Joined
Oct 16, 2014
Messages
18
In Access, you can use two IFF statements in a nested format. That way, there's only one value returned.

iff(condition, iff(condition2, truepart, falsepart))

In your case, unfortunately, what if someone checks both boxes? Just to be safe, you may want to put the following VBA code in the AfterUpdate of each checkbox

Private Sub Check1_AfterUpdate()
Me!Check2 = False
End Sub

Private Sub Check2_AfterUpdate()
Me!Check1 = False
End Sub

Hi Thanks for your reply. I don't understand what does this code do?
Will I be able to query both values if they check both?

Thanks:D
 

themurph2000

Fat, drunk, and stupid
Local time
Today, 08:21
Joined
Sep 24, 2007
Messages
181
They can't check both; that's the idea. If they click on one checkbox, the other one automatically turns off. That way, there's only one value and you can make your iff statement to read

Code:
IIf([Forms]![TPTC Analyzer]![P Future cost]=True, "00", "01")

I would recommend also making the default value of one checkbox True (whichever one is more likely to happen) and the default value of the other to be False. That way, a number is always chosen.
 

alicezyk

Registered User.
Local time
Today, 06:21
Joined
Oct 16, 2014
Messages
18
They can't check both; that's the idea. If they click on one checkbox, the other one automatically turns off. That way, there's only one value and you can make your iff statement to read

Code:
IIf([Forms]![TPTC Analyzer]![P Future cost]=True, "00", "01")

I would recommend also making the default value of one checkbox True (whichever one is more likely to happen) and the default value of the other to be False. That way, a number is always chosen.

I see but I want to make it possible to choose both :confused::confused:
 

llkhoutx

Registered User.
Local time
Today, 08:21
Joined
Feb 26, 2001
Messages
4,018
IIF is a function; If is a statement.

If x=24 then
'do domething
elseif x=32 then
'do something else
else
'didn't calculate either
end if
 

themurph2000

Fat, drunk, and stupid
Local time
Today, 08:21
Joined
Sep 24, 2007
Messages
181
I see but I want to make it possible to choose both :confused::confused:

Sorry, I misunderstood. What will you be doing with the numbers once you get them? Another option would be to create two text boxes on the form, set them to invisible, and set the AfterUpdate() to fill the text box with 00 (or 01) when that particular checkbox is selected.

You would then use those textboxes as part of your criteria in the query.

Code:
Like "*" & [Forms]![YourForm]!TextBox1 & "*"
 
Last edited:

alicezyk

Registered User.
Local time
Today, 06:21
Joined
Oct 16, 2014
Messages
18
Sorry, I misunderstood. What will you be doing with the numbers once you get them? Another option would be to create two text boxes on the form, set them to invisible, and set the AfterUpdate() to fill the text box with 00 (or 01) when that particular checkbox is selected.

You would then use those textboxes as part of your criteria in the query.

Code:
Like "*" & [Forms]![YourForm]!TextBox1 & "*"
Thanks for your reply! Sounds like a solution! Just need to figure out how to do the after update since I don't understand code at all :(
 

themurph2000

Fat, drunk, and stupid
Local time
Today, 08:21
Joined
Sep 24, 2007
Messages
181
Thanks for your reply! Sounds like a solution! Just need to figure out how to do the after update since I don't understand code at all :(

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:
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:
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.
 

JHB

Have been here a while
Local time
Today, 14:21
Joined
Jun 17, 2012
Messages
7,732
Try the below; (replace the Directory with your fieldname, (two replaces):
WHERE (((Directory)=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"))))) OR (((Directory)=IIf([Forms]![TPTC Analyzer]![P Future cost]=True And [forms]![TPTC Analyzer]![P LRM cost]=True,"01")));
 

alicezyk

Registered User.
Local time
Today, 06:21
Joined
Oct 16, 2014
Messages
18
Try the below; (replace the Directory with your fieldname, (two replaces):
Thanks for your reply!
Do I put the below as the criteria in the query?
I tried and it says undefined where.
:confused::DTHANKS SO MUCH AGAIN!
 

JHB

Have been here a while
Local time
Today, 14:21
Joined
Jun 17, 2012
Messages
7,732
Show your whole SQL string!
 

alicezyk

Registered User.
Local time
Today, 06:21
Joined
Oct 16, 2014
Messages
18
Show your whole SQL string!

currently its
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]![R Bid number])) AND ((O858IA_VWRDCAM.PRC_JOB_LST_RUN_DT) Between [forms]![TPTC Analyzer]![R ICLOR Start] And [forms]![TPTC Analyzer]![R 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"))) AND ((Sum(O858IA_VWRDCAM.DEL_LOC_SMT_GRR_A))>0)) OR (((Sum(O858IA_VWRDCAM.DEL_LOC_SMT_GRR_A))>0) AND ((WHERE((("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"))))))<>False));


----------
I tried replacing mine criteria with yours but it says error.
Thanks so much!
I don't know SQL at all lol:D
 

alicezyk

Registered User.
Local time
Today, 06:21
Joined
Oct 16, 2014
Messages
18
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:
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:
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.
Thanks for your answer!!
However it says compile error:
Syntax error.
:(
 

JHB

Have been here a while
Local time
Today, 14:21
Joined
Jun 17, 2012
Messages
7,732
Oh - to get that right I need a stripped version of your database with some sample data, (zip it) + name of the query.
 

alicezyk

Registered User.
Local time
Today, 06:21
Joined
Oct 16, 2014
Messages
18
Oh - to get that right I need a stripped version of your database with some sample data, (zip it) + name of the query.
I don't know how this will work cuz my database has a ODBC connection in the query to make a new table. You don't have that connection so you won't be able to test whether it works or not.:(

I just left the only query in there. :D
 
Last edited:

Users who are viewing this thread

Top Bottom