By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

Ignore Yes/No (checkboxes) when value input in text box

P: 62
I have a form in which users may search for a module based on a number of criteria including three check boxes (indicating which processes have been completed) as well as a text box for the user to type in the module number.

I would like to make a query return all values that match the indicated check boxes when the text box is blank, but return ONLY the indicated module number when the text box has text in it.

I know I can use a UNION query to display both (check box query results and the text box result), but for ease of use it would be convenient if only the single value were displayed when the text box is filled in. Or I could simply include all the criteria, but then it would only return the typed in record if the check boxes are also set correctly.

I have tried using a query in which the criteria for the module number is set to the text box (with a ... OR [txtModuleNum] Is Null included so it is ignored if nothing is typed) and using an If statement to control whether or not the check box is evaluated, but I can't seem to get the syntax right. My code is:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblModules.ModuleNum, tblModules.Usedinpdm, tblModules.UsedInCATIA
  2. FROM tblModules
  3. WHERE (((tblModules.ModuleNum)=[Forms]![frmModSearch]![tbModNumDELETE] Or [Forms]![frmModSearch]![tbModNumDELETE] Is Null) AND IFF(Len([Forms]![frmModSearch]![tbModNumDELETE])=0, ((tblModules.Usedinpdm)=[Forms]![frmModSearch]![CheckUpdateUsedInPDM])), Is Null); 
Any help would be greatly appreciated.
Thanks in advance!
Sep 5 '07 #1
Share this Question
Share on Google+
10 Replies


Scott Price
Expert 100+
P: 1,384
Haven't done a real eval to see if this query is really what you want to accomplish what you are attempting, but two things in the SQL stand out:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblModules.ModuleNum, tblModules.Usedinpdm, tblModules.UsedInCATIA FROM tblModules WHERE (((tblModules.ModuleNum)=[Forms]![frmModSearch]![tbModNumDELETE] Or [Forms]![frmModSearch]![tbModNumDELETE] Is Null) AND IFF(Len([Forms]![frmModSearch]![tbModNumDELETE])=0, ((tblModules.Usedinpdm)=[Forms]![frmModSearch]![CheckUpdateUsedInPDM])), Is Null);
This line:
Expand|Select|Wrap|Line Numbers
  1. Or [Forms]![frmModSearch]![tbModNumDELETE] Is Null
Should read:
Expand|Select|Wrap|Line Numbers
  1. Or IsNull([Forms]![frmModSearch]![tblModNumDELETE])
And this line:
Expand|Select|Wrap|Line Numbers
  1. IFF(Len([Forms]![frmModSearch]![tbModNumDELETE])=0, ((tblModules.Usedinpdm)=[Forms]![frmModSearch]![CheckUpdateUsedInPDM])), Is Null)
Should be IIF, is missing the second condition, and your value if false condition will not evaluate to anything... The general syntax is IIF( [expression to evaluate], [value returned if false], [value returned if true]). You need to include all three of the values. IIF stands for If and only IF. If the condition you give evaluates true then return one value, if the condition evaluates false, then return the other.

Regards,
Scott
Sep 5 '07 #2

P: 62
Haven't done a real eval to see if this query is really what you want to accomplish what you are attempting, but two things in the SQL stand out:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblModules.ModuleNum, tblModules.Usedinpdm, tblModules.UsedInCATIA FROM tblModules WHERE (((tblModules.ModuleNum)=[Forms]![frmModSearch]![tbModNumDELETE] Or [Forms]![frmModSearch]![tbModNumDELETE] Is Null) AND IFF(Len([Forms]![frmModSearch]![tbModNumDELETE])=0, ((tblModules.Usedinpdm)=[Forms]![frmModSearch]![CheckUpdateUsedInPDM])),Is Null);
This line:
Expand|Select|Wrap|Line Numbers
  1. Or [Forms]![frmModSearch]![tbModNumDELETE] Is Null
Should read:
Expand|Select|Wrap|Line Numbers
  1. Or IsNull([Forms]![frmModSearch]![tblModNumDELETE])
And this line:
Expand|Select|Wrap|Line Numbers
  1. IFF(Len([Forms]![frmModSearch]![tbModNumDELETE])=0, ((tblModules.Usedinpdm)=[Forms]![frmModSearch]![CheckUpdateUsedInPDM])),Is Null)
Should be IIF, is missing the second condition, and your value if false condition will not evaluate to anything... The general syntax is IIF( [expression to evaluate], [value returned if false], [value returned if true]). You need to include all three of the values. IIF stands for If and only IF. If the condition you give evaluates true then return one value, if the condition evaluates false, then return the other.

Regards,
Scott
Thanks for the quick reply. I'm pretty new to SQL coding, but the first Or...Is Null statement is a statement I've used many times in the criteria field of searches to make the query ignore the value of a text box if its left blank. The real trouble I'm having is with the IF syntax.

What I'm trying to create in SQL code (and not having much luck as of yet) is an if statement that checks that there is any text in a form text box: if TRUE, it should return all values, if FALSE it should set criteria to a form-check box.

Perhaps something like
Expand|Select|Wrap|Line Numbers
  1.  IF([LEN([text_box])=0,"*",[check_box]) 
?

I hope that clarifies my question. Thanks again!
Sep 5 '07 #3

Scott Price
Expert 100+
P: 1,384
VBA help file has this to say about IsNull:
Important Use the IsNull function to determine whether an expression contains a Null value. Expressions that you might expect to evaluate to True under some circumstances, such as If Var = Null and If Var <> Null, are always False. This is because any expression containing a Null is itself Null and, therefore, False.
As for your other IIF statement,
Expand|Select|Wrap|Line Numbers
  1. IF([LEN([text_box])=0,"*",[check_box])
is close. Have a go at something like this:

Expand|Select|Wrap|Line Numbers
  1. IIF(IsNull([text_box], [check_box],)
Since you are using this as a WHERE criteria: What this statement does is checks for a null value in the text box. If it is null the expression returns a WHERE criteria of nothing, so the records in your query will be filtered by 'nothing' thereby returning all records. If it is false (meaning the text box contains text) the WHERE criteria is drawn from the value entered in the check box.

Regards,
Scott
Sep 6 '07 #4

P: 62
VBA help file has this to say about IsNull:

As for your other IIF statement,
Expand|Select|Wrap|Line Numbers
  1. IF([LEN([text_box])=0,"*",[check_box])
is close. Have a go at something like this:

Expand|Select|Wrap|Line Numbers
  1. IIF(IsNull([text_box], [check_box],)
Since you are using this as a WHERE criteria: What this statement does is checks for a null value in the text box. If it is null the expression returns a WHERE criteria of nothing, so the records in your query will be filtered by 'nothing' thereby returning all records. If it is false (meaning the text box contains text) the WHERE criteria is drawn from the value entered in the check box.

Regards,
Scott

I've tried using your suggested code for the criteria for the check box, but I can't seem to make it work correctly. It returns the expected results when I have no criteria specified in the ModuleNum field, however when I put a similar statement in the criteria for that field it tells me "Undefined function IFF." I have the same syntax in that where as other places: IFF(IsNull([tbModule_Num]),Like "*",[tbModule_Num]) but it doesn't seem to like it.

Any ideas?
Sep 6 '07 #5

Scott Price
Expert 100+
P: 1,384
Look carefully.... you have
Expand|Select|Wrap|Line Numbers
  1. IFF
(two FF's) the syntax is
Expand|Select|Wrap|Line Numbers
  1. IIF
(two II's)

Regards,
Scott
Sep 6 '07 #6

P: 62
Look carefully.... you have
Expand|Select|Wrap|Line Numbers
  1. IFF
(two FF's) the syntax is
Expand|Select|Wrap|Line Numbers
  1. IIF
(two II's)

Regards,
Scott

Wow. Thats a brain fart of epic proportions. I was fighting with that damn thing for two hours. I guess I reverted back to my old college math days. Thanks, Scott, for your help. It works great now.

Thanks again!
Sep 6 '07 #7

Scott Price
Expert 100+
P: 1,384
Wow. Thats a brain fart of epic proportions. I was fighting with that damn thing for two hours. I guess I reverted back to my old college math days. Thanks, Scott, for your help. It works great now.

Thanks again!
Not a problem! Glad it's working for you.

Regards,
Scott
Sep 6 '07 #8

P: 8
Hi Scott/nickvans

I'm having the same problem with the IIf statement and it's driving me crazy... I've attached the mdb file at

http://www.mediafire.com/?fymly9xtx0r

it's a very simple file with two tables, one query and one form, i'm using the button in the form to test out the IIf statement in the query. i'm a total newbie and i've spent hours to make it work but to no avail...

Please help me!! Thanks in advance.

power2005
Sep 13 '07 #9

Scott Price
Expert 100+
P: 1,384
Hi Scott/nickvans

I'm having the same problem with the IIf statement and it's driving me crazy... I've attached the mdb file at

http://www.mediafire.com/?fymly9xtx0r

it's a very simple file with two tables, one query and one form, i'm using the button in the form to test out the IIf statement in the query. i'm a total newbie and i've spent hours to make it work but to no avail...

Please help me!! Thanks in advance.

power2005
power2005,

Please do not hijack another members thread with your question. Post a separate thread explaining the problem you are having and we will be more than happy to help you out.

Regards,
Scott
Sep 13 '07 #10

P: 8
Hi Scott

I'm really sorry. I thought since I have the same problem so I post in the related thread. Thanks for pointing out my lack of manner. I shall post a new thread!

power2005
Sep 13 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.