Criteria help

[itprog]

I am trying to setup a query that will generate a report based on options selected on a form. The fields I am using in the table do not have data for every record. If the criteria in the query is left blank, I would like all records listed regardless of whether there is a value in that field. If a value is selected for the criteria, of course I would like only those records shown. I am testing this only on one field at present. Here is my criteria in the query:

Like "*" & [forms]![frmDischargeRptSelector]![cmbResType] & "*"

With the above criteria, if I do not select a value for the parameter, I will get all fields that have a value in them, but not the blank(null) fields. If I leave the Like "*" out of the criteria, then I get no records if I do not select anything for a parameter. If I add an or Null to this statement, I get all null records even if I select a value for the parameter.

I hope this makes sense.

 

[KenHigg]

iif(not IsNull([forms]![frmDischargeRptSelector]![cmbResType], Like "*" & [forms]![frmDischargeRptSelector]![cmbResType] & "*", "*"))


???

 

[neileg]

Ken, you've missed the Like out of the false part. Also why use "not" instead of reversing the actions?

iif(IsNull([forms]![frmDischargeRptSelector]![cmbResType],Like "*",Like "*" & [forms]![frmDischargeRptSelector]![cmbResType] & "*")

 

[KenHigg]

Good points, sorry - Guess I was a bit eager to reply

 

[Jon K]

The Like operator cannot return Null values. And you can't put an IIF() expression that contains the Like operator in the criteria because Access will put an invisible = sign in front of the IIF() expression.


You can set the criteria like this in a new column in the query grid (replacing with the correct field name):-
---------------------------------------
Field: IIf(IsNull([forms]![frmDischargeRptSelector]![cmbResType]), True, [FieldName]=[forms]![frmDischargeRptSelector]![cmbResType])

Show: uncheck

Criteria: True
---------------------------------------

Alternatively, you can get rid of the IIF like this in the column:-
---------------------------------------
Field: [FieldName]=[forms]![frmDischargeRptSelector]![cmbResType] or [forms]![frmDischargeRptSelector]![cmbResType] Is Null

Show: uncheck

Criteria: True
---------------------------------------

.

 

[itprog]

Jon,
I'm having trouble getting your suggestion to work. I'm not sure what the [fieldname] in the expression represents. And what is the criteria of true is going to show?

 

[Jon K]

In the expression. replace the [FieldName] by the correct name of the field in your table. Since you have used cmbResType for the combo box name on the form, most likely the table field name is ResTpye.

The True in the criteria tells Access to treat the whole expression in the field row as a criterion.
.

 

[itprog]

Jon,
That worked perfectly. Thank you for the help.

 

[neileg]

The Like operator cannot return Null values. And you can't put an IIF() expression that contains the Like operator in the criteria because Access will put an invisible = sign in front of the IIF() expression.

The suggestion would not have involved return nulls with Like, but you are quite right about the IIF() not working. Your solution works and that's the bottom line!

 

[itprog]

I have another question. In the same query I have several fields that follow the format below and are working:

----------------------------------------------------------------------
Field: ResType: [ResidentType]=[forms]![frmDischargeRptSelector]![cmbResType] Or [forms]![frmDischargeRptSelector]![cmbResType] Is Null

Criteria: True
-----------------------------------------------------------------------

Now I want to add a date field to this as well. If someone enters a begin date and end date, show the relevant records, if no dates, then show all records. I tried the expression below, but I get an error message that the syntax is not right - missing comma etc. Can someone tell me what might be wrong with this statement?

Field: Disch: [DischDate]=(between [forms]![frmDischargeRptSelector]![txtBeginDate] And [forms]![frmDischargeRptSelector]![txtEndDate])

 

[mresann]

Try using the Iif statement to determine the records to return. I rarely use the between statement myself, but I think you are getting a null value problem.

 

[itprog]

I get the error message as soon as I leave the field box, even before I get a chance to run the query.

 

[aingram]

The Like operator cannot return Null values. And you can't put an IIF() expression that contains the Like operator in the criteria because Access will put an invisible = sign in front of the IIF() expression.


You can set the criteria like this in a new column in the query grid (replacing with the correct field name):-
---------------------------------------
Field: IIf(IsNull([forms]![frmDischargeRptSelector]![cmbResType]), True, [FieldName]=[forms]![frmDischargeRptSelector]![cmbResType])

Show: uncheck

Criteria: True
---------------------------------------
.

Anyone know of way to add a wild card entry to this so that it will display records when only a part of the date is entered

i.e. looking for

Access World Forums

but only typed access