IS NULL in criteria question

[McArthurGDM]

Hi,



I have this particular field with values "YES" or Blank


I have it set up so that now all I need is that Criteria specifies whether an unbound text box on another form was filled out or not. If it was, the Null field values dissapear and I am only left with the fields with "YES" values. Also, if the field was not filled out, then nothing happens and the records remain.

I assume this entails an iif statement or something, but I can't seem to get it to work. I'd prefer working with just the criteria field btw and not VBA.

[orange]

Can you show us the SQL view of the query you have?

[June7]

Try:
IIf(IsNull(Forms!formname!textboxname), "*" Or Is Null, "Yes")

It would be better if the Exp1 returned something in every record, like N/A or None or No.

[McArthurGDM]

SELECT MAIN_T.REFID, MAIN_T.AgenciesMain, MAIN_T.SourceMain, MAIN_T.AnnouncedMain, MAIN_T.EffectiveMain, MAIN_T.UpdatesMain, MAIN_T.ContentsMain, MAIN_T.EntitiesMain, MAIN_T.Action, MAIN_T.Summary, IIf(DatePart("ww",[AnnouncedMain])=DatePart("ww",[Forms]![Search_F]![ANNOUNCED]),"YES","") AS Expr1FROM MAIN_T
WHERE (((MAIN_T.UpdatesMain) Like "*" & [forms]![Search_F]![UPDATE] & "*") AND ((MAIN_T.ContentsMain) Like "*" & [forms]![Search_F]![CONTENT] & "*") AND ((MAIN_T.EntitiesMain.Value) Like "*" & [forms]![Search_F]![ENTITY] & "*") AND ((MAIN_T.AgenciesMain.Value) Like "*" & [forms]![Search_F]![AGENCY] & "*")) OR (((MAIN_T.AgenciesMain.Value)=[forms]![Search_F]![ANNOUNCED]));

Basically, I set up a field to test whether something was true or not, if it was - YES - if not - Blank. Now, all I need is to know whether or not someone inputted something into a particular text box on another form - in this case [forms]![Search_F]![ANNOUNCED]. If they did, then the YES's stay and the blanks disappear.

[McArthurGDM]

June7.. that worked. Why did that work?

that's like saying

If IsNull(forms![form][whatever]) Then
"*" OR IS NULL
Else
"YES"
End If

Kind of trying to figure out the logic behind the statements "*" OR IS NULL and just the "YES"

[June7]

"*" is wildcard but Nulls are ignored so those records would not return, therefore the Or Is Null captures the Null records. If every record had a string value, could simplify:

IIf(IsNull(Forms!formname!textboxname), "*", "Yes")

[McArthurGDM]

Oh, Okay. I get it. Thanks!

[McArthurGDM]

How do you specify that this thread was solved?

[June7]

Thread Tools dropdown above first post.

[McArthurGDM]

I'm not sure. I'm running into another problem.

All I want to do is check to see if a user inputted date falls within a certain week. If it does, I show all the records within that week.

So, if a user inputs data for 06/26/2014. All the records from that given week show up.

I know how to calculate the week number given DatePart("ww", forms![Search][whatever]). But I want to run all the other dates against that and then display only the other dates that fall into that week.

[June7]

WHERE DatePart("ww", [fieldname]) = DatePart("ww", Forms![Search][whatever])

However, if multiple years in data:

WHERE Year([fieldname]) & DatePart("ww", [fieldname]) = Year(Forms![Search][whatever]) & DatePart("ww", Forms![Search][whatever])

[McArthurGDM]

What happens if the search field is blank?

[June7]

Try it and let me know.

I don't use dynamic parameterized queries. I use VBA to build filter criteria and apply to form or report.