IIf Criteria for Query

[alsoto]

Can someone tell me how to fix the Criteria for my query:



IIf([Forms]![0001Test]![Status] Is Null, Like "*",[Forms]![0001Test]![Status])


I want the report to show ALL if no status is chosen from the combo box on my "Choose Parameters for Report" form.

Thanks,
Al

[June7]

IIF not needed.

Like [Forms]![0001Test]![Status] & "*"

[alsoto]

Hi,

Thanks for your reply.

When I use: Like [Forms]![0001Test]![Status] & "*" as suggested, I do not get all records in the query results.

I have a test table I am using. It has 40 records. 10 records do not have a Status entered. When I use the above, I only get the 30 records that do have a status. I should get all records since the parameter field ([Forms]![0001Test]![Status]) in Form 0001Test was left blank.

Please advise.

Thanks in advance,
Al

[alsoto]

This works:

[Forms]![0001Test]![Status] OR [Forms]![0001Test]![Status] Is Null

Thanks for everybody"s help.

Al

I SPOKE TOO SOON. DOESN'T WORK WELL EITHER.

[June7]

Yes, I did not think about possible Nulls. The wildcard should work if every record has a value.

Your solution means the report will return records that meet the specific criteria as well as records that are null in that field but not ALL records. Is this satisfactory?

This should return ALL:
[Forms]![0001Test]![Status] LIKE "*" OR [Forms]![0001Test]![Status] Is Null

Is Status a text datatype, not a Yes/No?

[alsoto]

Report should show either records that match value entered in parameter form (1, 2, or all 3 different parameters) or All records, whether they are null or not null.

Please advise.
Thanks

[June7]

Only 3 ways I can see.

One is to always require a value in the field, even if it is NA for not available. This could be a default setting for the field so when new record created it is populated.

Another is to use expression in a query to give all the Null status records a value, such as NA or empty string, and then use that constructed field as the criteria field. Such as - AllStatus: Nz([Status],"")

I don't normally use parameters in queries. I use VBA code to construct the filter criteria for a form or report and pass it to the form/report with the WHERE argument of DoCmd.OpenReport or OpenForm.

What is the datatype of Status field?

[alsoto]

I have 3 parameters:

Status (text)
Rep (text)
Program (text)

plus a Between AND date range.
StartD and EndD

I thought about DoCmd.OpenReport, but won't OpenArgs only take one parameter? I would love to use code instead of query parameters, can you give me an example?

Thanks,
Al

[June7]

Okay, here is from my project that will construct a WHERE clause to filter data (OpenArgs serves another purpose) from selected criteria. Only the AND operator is used, no OR.

DoCmd.OpenForm "DataSoilsAggMoistOrg", acNormal, , "Submit.DateEnter=#" & Forms![SampleManagement]!ctrSampleList!DateEnter & "#" & _
" AND Submit.ProjRecID=" & Forms![SampleManagement]!ctrSampleList!ProjRecID, , , "Edit" & rsGroup.RecordCount

Yours would look something like:
DoCmd.OpenReport "reportname", , ,"Status='" Me.txtStatus & "' AND Rep='" & Me.txtRep & "' AND Program='" & Me.txtProgram & "' AND #" & Me.Date & "# BETWEEN StartD AND EndD"

[alsoto]

June7,

Can you PLEASE add Sort By (parameter field called [SortBy])? Then I can test it.

THANK YOU SO MUCH FOR YOUR HELP WITH THIS!!!
Al

[June7]

The sort criteria is not passed in the WhereCondition argument of OpenReport. Set sort in the report's RecordSource or in Grouping&Sorting settings or in the report OrderBy property.

See http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[alsoto]

Ok, thanks. I will let you know how it goes.

Thanks,
Al

[alsoto]

DoCmd.OpenReport "0001RptSummary", , ,"Status='" Me.Status & "' AND Rep='" & Me.Rep & "' AND Program='" & Me.Program & "' AND #" & Me.StartDate & "# BETWEEN StartD AND EndD"

With this code, if the field happens to be empty (ie no rep chosen on parameter form), will report show all?

Thanks,
Al

[June7]

No, allowing for nulls is another issue.
Try:
DoCmd.OpenReport "0001RptSummary", , ,"Status LIKE '" Me.Status & "*' AND Rep LIKE '" & Me.Rep & "*' AND Program LIKE '" & Me.Program & "*' AND #" & Me.StartDate & "# BETWEEN StartD AND EndD"

If no dates given, that may be trickier. An alternative is to build the criteria string incrementally. Example:
strSQL = IIf(IsNull(.cbxField1) Or (Not IsNull(.cbxField1) And IsNull(.cbxFor1)), "", strField1 & "='" & strFor1 & "'")
strSQL = strSQL & IIf(IsNull(.cbxField2) Or (Not IsNull(.cbxField2) And IsNull(.cbxFor2)), "", IIf(strSQL = "", "", " AND ") & strField2 & "='" & strFor2 & "'")
strSQL = strSQL & IIf(IsNull(.cbxCategory) Or (Not IsNull(.cbxCategory) And (IsNull(.tbxStart) Or IsNull(.tbxEnd))), "", IIf(strSQL = "", "", " AND ") & strField & " BETWEEN " & strStart & " AND " & strEnd)
strSQL = strSQL & IIf(IsNull(.cbxTest), "", IIf(strSQL = "", "", " AND ") & "TestNum='" & .cbxTest & "'")

[alsoto]

LOL - looks complicated. Beautiful looking code, just over my head.

There will ALWAYS be dates. Does that make it easier?

Al