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

[nickvans]

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:

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); 

Any help would be greatly appreciated.
Thanks in advance!

[Scott Price]

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:

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:

Or [Forms]![frmModSearch]![tbModNumDELETE] Is Null

Should read:

Or IsNull([Forms]![frmModSearch]![tblModNumDELETE])

And this line:

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

[nickvans]

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:

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:

Or [Forms]![frmModSearch]![tbModNumDELETE] Is Null

Should read:

Or IsNull([Forms]![frmModSearch]![tblModNumDELETE])

And this line:

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

 IF([LEN([text_box])=0,"*",[check_box]) 

?

I hope that clarifies my question. Thanks again!

[Scott Price]

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,

IF([LEN([text_box])=0,"*",[check_box])

is close. Have a go at something like this:

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

[nickvans]

VBA help file has this to say about IsNull:

As for your other IIF statement,

IF([LEN([text_box])=0,"*",[check_box])

is close. Have a go at something like this:

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?

[Scott Price]

Look carefully.... you have

IFF

(two FF's) the syntax is

IIF

(two II's)

Regards,
Scott

[nickvans]

Look carefully.... you have

IFF

(two FF's) the syntax is

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!

[Scott Price]

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

[power2005]

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

[Scott Price]

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

[power2005]

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