IFF in a criteria

  • Thread starter Thread starter Douglas J. Steele
  • Start date Start date
D

Douglas J. Steele

Make the criteria like

= [Forms]![myForm]![myField] Or IsNull([Forms]![myForm]![myField])
 
Hello,
IIf (IsNull([Forms]![myForm]![myField]),"Like *",[Forms]!
[myForm]![myField])
I but this statement to a criteria of a query, If [Forms]!
[myForm]![myField] has a value it works fine. I want it
to bring all values if [Forms]![myForm]![myField] text
box does not have any value. However it seems "Like *"
does not work there.
Is there any way to handle this?
Thanks,
Jim.
 
Oops. That's what happens when you go by memory.

Thanks for the save, Joan.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Joan Wild said:
Actually, Doug meant

= [Forms]![myForm]![myField] Or [Forms]![myForm]![myField] Is Null

--
Joan Wild
Microsoft Access MVP

Douglas J. Steele said:
Make the criteria like

= [Forms]![myForm]![myField] Or IsNull([Forms]![myForm]![myField])



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



JIM.H. said:
Hello,
IIf (IsNull([Forms]![myForm]![myField]),"Like *",[Forms]!
[myForm]![myField])
I but this statement to a criteria of a query, If [Forms]!
[myForm]![myField] has a value it works fine. I want it
to bring all values if [Forms]![myForm]![myField] text
box does not have any value. However it seems "Like *"
does not work there.
Is there any way to handle this?
Thanks,
Jim.
Click to expand...
Click to expand...
Click to expand...
 
Actually, Doug meant

= [Forms]![myForm]![myField] Or [Forms]![myForm]![myField] Is Null

--
Joan Wild
Microsoft Access MVP

Douglas J. Steele said:
Make the criteria like

= [Forms]![myForm]![myField] Or IsNull([Forms]![myForm]![myField])



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



JIM.H. said:
Hello,
IIf (IsNull([Forms]![myForm]![myField]),"Like *",[Forms]!
[myForm]![myField])
I but this statement to a criteria of a query, If [Forms]!
[myForm]![myField] has a value it works fine. I want it
to bring all values if [Forms]![myForm]![myField] text
box does not have any value. However it seems "Like *"
does not work there.
Is there any way to handle this?
Thanks,
Jim.
Click to expand...
Click to expand...
 
That is working perfectly. Now on the same table and
query, I have this in a different date field

Between [Forms]![ myForm]![StartDate] And [Forms]![
myForm]![EndDate]

I need to get his working if one of the date is not
entered through form.
So I have myField, StartDate, EndDate and if there are
something entered in any of them they should act as a
constraint.
-----Original Message-----
Oops. That's what happens when you go by memory.

Thanks for the save, Joan.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Joan Wild said:
Actually, Doug meant

= [Forms]![myForm]![myField] Or [Forms]![myForm]! [myField] Is Null

--
Joan Wild
Microsoft Access MVP

"Douglas J. Steele"
Click to expand...
Make the criteria like

= [Forms]![myForm]![myField] Or IsNull([Forms]! [myForm]![myField])



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Hello,
IIf (IsNull([Forms]![myForm]![myField]),"Like *", [Forms]!
[myForm]![myField])
I but this statement to a criteria of a query, If [Forms]!
[myForm]![myField] has a value it works fine. I want it
to bring all values if [Forms]![myForm]![myField] text
box does not have any value. However it seems "Like *"
does not work there.
Is there any way to handle this?
Thanks,
Jim.
Click to expand...
Click to expand...


.
Click to expand...
 
You can use the Nz function for the date fields.

Between Nz([Forms]![ myForm]![StartDate], #1/1/1900#) And
Nz([Forms]![myForm]![EndDate],#1/1/2100#)

You can change the dates in that to cover all the dates possible.

Note that if your date field has null values, those records won't be
included.
--
Joan Wild
Microsoft Access MVP

JIM.H. said:
That is working perfectly. Now on the same table and
query, I have this in a different date field

Between [Forms]![ myForm]![StartDate] And [Forms]![
myForm]![EndDate]

I need to get his working if one of the date is not
entered through form.
So I have myField, StartDate, EndDate and if there are
something entered in any of them they should act as a
constraint.
-----Original Message-----
Oops. That's what happens when you go by memory.

Thanks for the save, Joan.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Joan Wild said:
Actually, Doug meant

= [Forms]![myForm]![myField] Or [Forms]![myForm]! [myField] Is Null

--
Joan Wild
Microsoft Access MVP

"Douglas J. Steele"
Click to expand...
Make the criteria like

= [Forms]![myForm]![myField] Or IsNull([Forms]! [myForm]![myField])



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Hello,
IIf (IsNull([Forms]![myForm]![myField]),"Like *", [Forms]!
[myForm]![myField])
I but this statement to a criteria of a query, If [Forms]!
[myForm]![myField] has a value it works fine. I want it
to bring all values if [Forms]![myForm]![myField] text
box does not have any value. However it seems "Like *"
does not work there.
Is there any way to handle this?
Thanks,
Jim.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top