IIf condition, form date, all dates (1 Viewer)

[supmktg]

OK I give!

I'm trying to get the following code to work in my query:

IIf([Forms]![frmDetailReportSelection]![ChkPostDate]=True,[Forms]![frmDetailReportSelection]![CmbPosted], "*")

The true part works fine, but the wildcard false part just won't work.

I've tried <=Date()
I've tried >#1/1/1998#
I've tried >#01/01/1998#
I've tried >Format(01/01/1998,"mm/dd/yyyy")
I've even tried using a hidden textbox on my form and making it the false part.
Nothing works!

If I try #08/19/2003# as the false part 'which happens to be a valid date in my data, it returns all records for that date.

Can anyone tell me what I'm doing wrong?

Thanks,

Supmktg

 

[WayneRyan]

supmktg,

I assume that you are using the Iif in the Criteria
section of the query. It is a one-state function.
In your case it tests for equality.

If the CheckBox is True, you will get all records
having this date:

[Forms]![frmDetailReportSelection]![CmbPosted]

If the CheckBox is False Then you will get all records
having the 8/19/2003 date.

If you want all records > the combo (If Checkbox = True)
or all > 8/19/2003 Then this will do:

>IIf([Forms]![frmDetailReportSelection]![ChkPostDate]=True,_
     CDate([Forms]![frmDetailReportSelection]![CmbPosted]), _
     CDate("8/19/2003"))

I get the feeling that you want = Combo or all greater
than the constant.

Wayne

 

[supmktg]

Wayne,

Thanks for the quick response!

What I'm trying to do is:

If the CheckBox is True, I want to get all records
having this date:

[Forms]![frmDetailReportSelection]![CmbPosted]

If the CheckBox is False Then I want to get ALL records.

I don't understand why the wildcard ' Like "*" ' doesn't work, or why ' >= [a very early date] ' doesn't work, while ' #[a specific date]# ' does work.

By the way, ' Like "*" ' does work if it's not part of the IIF statement.

Supmktg

 

[WayneRyan]

supmktg,

You could try multiple criteria (With an "Or")

=CDate([Forms]![frmDetailReportSelection]![CmbPosted]) And [Forms]![frmDetailReportSelection]![ChkPostDate]=True
>CDate("YourEarliestDate") And [Forms]![frmDetailReportSelection]![ChkPostDate]=False

Haven't tried it, but it should work

Wayne

 

[WayneRyan]

supmktg,

That last post was two lines by the way. I don't know why my
responses to this thread keep getting hammered.

The Iif can't do an = for one condition and a greater than for
the other condition.

The last method should do it.

Wayne

 

[supmktg]

Wayne,

I certainly didn't intend to hammer your response, on the contrary, I appreciate your help and was trying to clarify my initial question.

Your last response did solve my problem with a method I hadn't thought of using, two lines of criteria, OR instead if IIF.

For anyone who might be having a similar problem, here's the code that worked:

[Forms]![frmDetailReportSelection]![ChkPostDate]=True And [Forms]![frmDetailReportSelection]![CmbPosted]

or

[Forms]![frmDetailReportSelection]![ChkPostDate]=False And Like "*"

Wayne - Thank you very much for your assistance, I would have never figured it out on my own!

Supmktg

 

[WayneRyan]

supmktg,

No, I meant the formatting of my responses. Not you!

Glad to see that it worked.

see ya,
Wayne

 

[GGib7711]

IIf([Forms]![frmDetailReportSelection]![ChkPostDate] = True, [Forms]![frmDetailReportSelection]![CmbPosted], "*")

The above IIF criteria in the first post seems quite OK. Does anyone know why it won't work?

 

[Jon K]

"*" is a wildcard character and hence needs the Like Operator:-

Like IIf([Forms]![frmDetailReportSelection]![ChkPostDate] = True, [Forms]![frmDetailReportSelection]![CmbPosted],"*")


Without the Like Operator, Access puts an invisible = sign in front of IIF, thereby creating a type mismatch error when [ChkPostDate] on the form is not checked i.e. [a DateTimeField] = "*"

 

[GGib7711]

Jon,

I wasn't able to replicate the database and try the "Like Operator". Can you post an example?

I was also wondering ... if the = sign was invisible, how would we know there was an = sign there?

Best regards.

Gib

 

[Jon K]

Gib

I have attached a database replicating the poster's problem. You can open the form, make your selections and click on the command button to return the records. The command button runs the query "Query with Like Operator", which contains the poster's IIF criteria and the Like Operator.

I have also included another query, in which the criteria for the date field is set as ="*". When this query is run, an error message of "data type mismatch in criteria expression" should pop up.


To see the invisible = sign, you can open the query "Query with Like Operator" in query Design View. Delete the word Like from the criteria and switch to query SQL View. There you can see the = sign in front of IIF in the Where Clause of the SQL Statement.

The Design View of a query is only a graphic presentation of an SQL Statement. When a query is run, Access actually runs the underlying SQL Statement.

Jon K

(The database was saved from Access 2000.)

 

[GGib7711]

Jon,

Thanks for the example and your clear explanation.

I have one more question if you don't mind.  How do I change the criteria to return also the null dates when the check box ChkPostDate is not checked? In the present IIF criteria, null dates are excluded.

Thanks again.

Gib

 

[Jon K]

[DateField] Like "*" will exclude any null dates.

To return the null dates as well, you can set the IIF criteria in a column in the query grid as follows:-

Field: IIf([Forms]![frmDetailReportSelection]![ChkPostDate] = True, [DateField] = [Forms]![frmDetailReportSelection]![CmbPosted], True)

Show: uncheck

Criteria: <>False


The word True in the false-part of the IIF expression will return every record including those with null dates.  Note that now the name of the date field is placed in the true-part of the expression.