====> Debug query criteria date field all dates or selected date..URGENT ..

[JOJ via AccessMonster.com]

I have a check box named DTCLOSE2 in my dialogue form.If checked, the
query to filter all records including nulls otherwise filter those
records which matches the date inputted in the field DTCLOSE.
Could you pls debug the following criteria so that my query works as
per the logic above.I think it is something to do with the string
concatenation.


Query criteria on a date field:

IIf(([Forms]![All Shipment Status]![DTCLOSE2]=-1),([dbo_tblShipment].
[ActualArrivalin]) Like "*" Or ([dbo_tblShipment].[ActualArrivalin]) Is Null,
([dbo_tblShipment].[ActualArrivalin]) Like [Forms]![All Shipment Status]!
[DTCLOSE])

 

[John Vinson]

I have a check box named DTCLOSE2 in my dialogue form.If checked, the
query to filter all records including nulls otherwise filter those
records which matches the date inputted in the field DTCLOSE.
Could you pls debug the following criteria so that my query works as
per the logic above.I think it is something to do with the string
concatenation.


Query criteria on a date field:

IIf(([Forms]![All Shipment Status]![DTCLOSE2]=-1),([dbo_tblShipment].
[ActualArrivalin]) Like "*" Or ([dbo_tblShipment].[ActualArrivalin]) Is Null,
([dbo_tblShipment].[ActualArrivalin]) Like [Forms]![All Shipment Status]!
[DTCLOSE])

The problem is that you're trying to pass *operators* - LIKE, OR, IS
NULL - in the arguments of an IIF. No can do! Iif and other such
functions can only return *values*, not portions of the SQL syntax.

Try changing the logic completely: you don't need an IIF at all.Try a
criterion of

(Forms![All Shipment Status]!DTCLOSE2] OR
[dbo_tblShipment].[ActualArrivalin]) = [Forms]![All Shipment
Status]![DTCLOSE])

If DTCLOSE2 is TRUE the first portion of the OR is true, and the
entire WHERE clause will be True - and all records will be retrieved
regardless of the value of ActualArrivalin.

If it is FALSE, the second clause of the OR will be checked; if the
date on the form is equal to the date in the table, you'll see the
record.

Note that LIKE is inappropriate for date fields. Dates are NOT
strings; LIKE is a string operator which uses wildcards. If you're not
using wildcards then LIKE works exactly the same as = except it forces
a conversion of the fields involved to String before the comparison.

John W. Vinson[MVP]

 

[JOJ via AccessMonster.com]

Thanks John.I did manage it other way round. I have convert null date to
an artificial date with a computed field and fixed the criteria on this
column.In the form DTclose filed I input "*" or a particular date and
the query filters exactly what I want.Thanks for your effort.

Like [Forms]![All Shipment Status]![DTCLOSE] is the criteria on the
computed field column

cheers
Joe

I have a check box named DTCLOSE2 in my dialogue form.If checked, the
query to filter all records including nulls otherwise filter those

[quoted text clipped - 9 lines]

([dbo_tblShipment].[ActualArrivalin]) Like [Forms]![All Shipment Status]!
[DTCLOSE])

The problem is that you're trying to pass *operators* - LIKE, OR, IS
NULL - in the arguments of an IIF. No can do! Iif and other such
functions can only return *values*, not portions of the SQL syntax.

Try changing the logic completely: you don't need an IIF at all.Try a
criterion of

(Forms![All Shipment Status]!DTCLOSE2] OR
[dbo_tblShipment].[ActualArrivalin]) = [Forms]![All Shipment
Status]![DTCLOSE])

If DTCLOSE2 is TRUE the first portion of the OR is true, and the
entire WHERE clause will be True - and all records will be retrieved
regardless of the value of ActualArrivalin.

If it is FALSE, the second clause of the OR will be checked; if the
date on the form is equal to the date in the table, you'll see the
record.

Note that LIKE is inappropriate for date fields. Dates are NOT
strings; LIKE is a string operator which uses wildcards. If you're not
using wildcards then LIKE works exactly the same as = except it forces
a conversion of the fields involved to String before the comparison.

John W. Vinson[MVP]