Like IIf statement that needs results to work with even getting bl

[buzz]

Well, I was looking all through this forum and couldn't find anything to
really work. Seemed there were a lot of similar questions but the
resolutions didn't help mine.

I have a form that users set values on and a query is based on those values.
The one problem I am dealing with is that the users have an option of a
hidden field showing to them to enter things on if needed; so they are either
entering an Index_Date value or a number sequence that is part of this ICN
field. My problem is with the IIf statement for the Index_Date field because
either I base it on the form's field value or I just try and display all
records (essentially using the IIf statement to figure out if it needs to
filter or not) and I can't make this work right.

The criteria for this Index_Date field in the query is:

Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")

The prior works perfect if the field has a value; but I need it to also show
the record if there is no value in the field. An "Is Null" criteria for this
field in the query will show me the values and even changing this query field
to have a criteria of:

Like "*" Or Is Null

does exactly what I want. But I can't get this to work right in an IIf
statement. And any alterations I've been doing is giving me no record
results or that too complex query error (which I wonder if it may be due to
how I'm running statements in the other fields for the query).

Below is my entire SQL if you need it (have fun with that one):


SELECT IMAGING_BATCH_T.BUCKET_NBR, IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]) AS Expr1,
IMAGING_BATCH_T.PROCESS_NM, IMAGING_FOLDER_T.ICN, IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION, IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
FROM (IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM) INNER JOIN
IMAGING_CONTRACT_DIVISION_T ON IMAGING_BATCH_T.PROCESS_NM =
IMAGING_CONTRACT_DIVISION_T.PROCESS_NM
GROUP BY IMAGING_BATCH_T.BUCKET_NBR, IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]), IMAGING_BATCH_T.PROCESS_NM,
IMAGING_FOLDER_T.ICN, IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION, IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
HAVING
(((IMAGING_BATCH_T.BUCKET_NBR)=[Forms]![frmBucketNumber-index]![BucketNumber])
AND
((IMAGING_CONTRACT_DIVISION_T.CONTRACT)=[Forms]![frmBucketNumber-index]![Contract])
AND ((IMAGING_FOLDER_T.INDEX_DT) Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*"))
AND ((IMAGING_FOLDER_T.ICN) Like
IIf([Forms]![frmBucketNumber-index]![JulianDate]<>"",IIf([Forms]![frmBucketNumber-index]![Division]="Medicare","??"
& [Forms]![frmBucketNumber-index]![JulianDate] &
"??????",[Forms]![frmBucketNumber-index]![JulianDate] & "*"),"*")) AND
((IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM)="Madison"));

 

[Douglas J. Steele]

Try using

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

although I suspect you may need to use

AND (IMAGING_FOLDER_T.INDEX_DT Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

(I'm assuming INDEX_DT is a text field, since LIKE isn't appropriate to use
with Date/Time fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Well, I was looking all through this forum and couldn't find anything to
really work. Seemed there were a lot of similar questions but the
resolutions didn't help mine.

I have a form that users set values on and a query is based on those
values.
The one problem I am dealing with is that the users have an option of a
hidden field showing to them to enter things on if needed; so they are
either
entering an Index_Date value or a number sequence that is part of this ICN
field. My problem is with the IIf statement for the Index_Date field
because
either I base it on the form's field value or I just try and display all
records (essentially using the IIf statement to figure out if it needs to
filter or not) and I can't make this work right.

The criteria for this Index_Date field in the query is:

Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")

The prior works perfect if the field has a value; but I need it to also
show
the record if there is no value in the field. An "Is Null" criteria for
this
field in the query will show me the values and even changing this query
field
to have a criteria of:

Like "*" Or Is Null

does exactly what I want. But I can't get this to work right in an IIf
statement. And any alterations I've been doing is giving me no record
results or that too complex query error (which I wonder if it may be due
to
how I'm running statements in the other fields for the query).

Below is my entire SQL if you need it (have fun with that one):


SELECT IMAGING_BATCH_T.BUCKET_NBR, IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]) AS Expr1,
IMAGING_BATCH_T.PROCESS_NM, IMAGING_FOLDER_T.ICN,
IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION, IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
FROM (IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM) INNER JOIN
IMAGING_CONTRACT_DIVISION_T ON IMAGING_BATCH_T.PROCESS_NM =
IMAGING_CONTRACT_DIVISION_T.PROCESS_NM
GROUP BY IMAGING_BATCH_T.BUCKET_NBR, IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]),
IMAGING_BATCH_T.PROCESS_NM,
IMAGING_FOLDER_T.ICN, IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION, IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
HAVING
(((IMAGING_BATCH_T.BUCKET_NBR)=[Forms]![frmBucketNumber-index]![BucketNumber])
AND
((IMAGING_CONTRACT_DIVISION_T.CONTRACT)=[Forms]![frmBucketNumber-index]![Contract])
AND ((IMAGING_FOLDER_T.INDEX_DT) Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*"))
AND ((IMAGING_FOLDER_T.ICN) Like
IIf([Forms]![frmBucketNumber-index]![JulianDate]<>"",IIf([Forms]![frmBucketNumber-index]![Division]="Medicare","??"
& [Forms]![frmBucketNumber-index]![JulianDate] &
"??????",[Forms]![frmBucketNumber-index]![JulianDate] & "*"),"*")) AND
((IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM)="Madison"));

 

[buzz]

Index_DT is supposed to be a date/time field

Try using

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

although I suspect you may need to use

AND (IMAGING_FOLDER_T.INDEX_DT Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

(I'm assuming INDEX_DT is a text field, since LIKE isn't appropriate to use
with Date/Time fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Well, I was looking all through this forum and couldn't find anything to
really work. Seemed there were a lot of similar questions but the
resolutions didn't help mine.

I have a form that users set values on and a query is based on those
values.
The one problem I am dealing with is that the users have an option of a
hidden field showing to them to enter things on if needed; so they are
either
entering an Index_Date value or a number sequence that is part of this ICN
field. My problem is with the IIf statement for the Index_Date field
because
either I base it on the form's field value or I just try and display all
records (essentially using the IIf statement to figure out if it needs to
filter or not) and I can't make this work right.

The criteria for this Index_Date field in the query is:

Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")

The prior works perfect if the field has a value; but I need it to also
show
the record if there is no value in the field. An "Is Null" criteria for
this
field in the query will show me the values and even changing this query
field
to have a criteria of:

Like "*" Or Is Null

does exactly what I want. But I can't get this to work right in an IIf
statement. And any alterations I've been doing is giving me no record
results or that too complex query error (which I wonder if it may be due
to
how I'm running statements in the other fields for the query).

Below is my entire SQL if you need it (have fun with that one):


SELECT IMAGING_BATCH_T.BUCKET_NBR, IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]) AS Expr1,
IMAGING_BATCH_T.PROCESS_NM, IMAGING_FOLDER_T.ICN,
IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION, IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
FROM (IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM) INNER JOIN
IMAGING_CONTRACT_DIVISION_T ON IMAGING_BATCH_T.PROCESS_NM =
IMAGING_CONTRACT_DIVISION_T.PROCESS_NM
GROUP BY IMAGING_BATCH_T.BUCKET_NBR, IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]),
IMAGING_BATCH_T.PROCESS_NM,
IMAGING_FOLDER_T.ICN, IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION, IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
HAVING
(((IMAGING_BATCH_T.BUCKET_NBR)=[Forms]![frmBucketNumber-index]![BucketNumber])
AND
((IMAGING_CONTRACT_DIVISION_T.CONTRACT)=[Forms]![frmBucketNumber-index]![Contract])
AND ((IMAGING_FOLDER_T.INDEX_DT) Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*"))
AND ((IMAGING_FOLDER_T.ICN) Like
IIf([Forms]![frmBucketNumber-index]![JulianDate]<>"",IIf([Forms]![frmBucketNumber-index]![Division]="Medicare","??"
& [Forms]![frmBucketNumber-index]![JulianDate] &
"??????",[Forms]![frmBucketNumber-index]![JulianDate] & "*"),"*")) AND
((IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM)="Madison"));

 

[Douglas J. Steele]

In that case, you can't use Like. Try:

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] IS NULL)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Index_DT is supposed to be a date/time field

Try using

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

although I suspect you may need to use

AND (IMAGING_FOLDER_T.INDEX_DT Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

(I'm assuming INDEX_DT is a text field, since LIKE isn't appropriate to
use
with Date/Time fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Well, I was looking all through this forum and couldn't find anything
to
really work. Seemed there were a lot of similar questions but the
resolutions didn't help mine.

I have a form that users set values on and a query is based on those
values.
The one problem I am dealing with is that the users have an option of a
hidden field showing to them to enter things on if needed; so they are
either
entering an Index_Date value or a number sequence that is part of this
ICN
field. My problem is with the IIf statement for the Index_Date field
because
either I base it on the form's field value or I just try and display
all
records (essentially using the IIf statement to figure out if it needs
to
filter or not) and I can't make this work right.

The criteria for this Index_Date field in the query is:

Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")

The prior works perfect if the field has a value; but I need it to also
show
the record if there is no value in the field. An "Is Null" criteria
for
this
field in the query will show me the values and even changing this query
field
to have a criteria of:

Like "*" Or Is Null

does exactly what I want. But I can't get this to work right in an IIf
statement. And any alterations I've been doing is giving me no record
results or that too complex query error (which I wonder if it may be
due
to
how I'm running statements in the other fields for the query).

Below is my entire SQL if you need it (have fun with that one):


SELECT IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]) AS Expr1,
IMAGING_BATCH_T.PROCESS_NM, IMAGING_FOLDER_T.ICN,
IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION, IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
FROM (IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM) INNER JOIN
IMAGING_CONTRACT_DIVISION_T ON IMAGING_BATCH_T.PROCESS_NM =
IMAGING_CONTRACT_DIVISION_T.PROCESS_NM
GROUP BY IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]),
IMAGING_BATCH_T.PROCESS_NM,
IMAGING_FOLDER_T.ICN, IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION, IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
HAVING
(((IMAGING_BATCH_T.BUCKET_NBR)=[Forms]![frmBucketNumber-index]![BucketNumber])
AND
((IMAGING_CONTRACT_DIVISION_T.CONTRACT)=[Forms]![frmBucketNumber-index]![Contract])
AND ((IMAGING_FOLDER_T.INDEX_DT) Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*"))
AND ((IMAGING_FOLDER_T.ICN) Like
IIf([Forms]![frmBucketNumber-index]![JulianDate]<>"",IIf([Forms]![frmBucketNumber-index]![Division]="Medicare","??"
& [Forms]![frmBucketNumber-index]![JulianDate] &
"??????",[Forms]![frmBucketNumber-index]![JulianDate] & "*"),"*")) AND
((IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM)="Madison"));

 

[buzz]

When I change to that, I get that stupid message about "This express is typed
incorrectly or it is too complex to be evaluated. For example, a numeric
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables." I'm still to
mess with this but I think I'm going to really have to strip this down to get
it to do what I need it to and separate it into pieces.

In that case, you can't use Like. Try:

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] IS NULL)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Index_DT is supposed to be a date/time field

Try using

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

although I suspect you may need to use

AND (IMAGING_FOLDER_T.INDEX_DT Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

(I'm assuming INDEX_DT is a text field, since LIKE isn't appropriate to
use
with Date/Time fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Well, I was looking all through this forum and couldn't find anything
to
really work. Seemed there were a lot of similar questions but the
resolutions didn't help mine.

I have a form that users set values on and a query is based on those
values.
The one problem I am dealing with is that the users have an option of a
hidden field showing to them to enter things on if needed; so they are
either
entering an Index_Date value or a number sequence that is part of this
ICN
field. My problem is with the IIf statement for the Index_Date field
because
either I base it on the form's field value or I just try and display
all
records (essentially using the IIf statement to figure out if it needs
to
filter or not) and I can't make this work right.

The criteria for this Index_Date field in the query is:

Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")

The prior works perfect if the field has a value; but I need it to also
show
the record if there is no value in the field. An "Is Null" criteria
for
this
field in the query will show me the values and even changing this query
field
to have a criteria of:

Like "*" Or Is Null

does exactly what I want. But I can't get this to work right in an IIf
statement. And any alterations I've been doing is giving me no record
results or that too complex query error (which I wonder if it may be
due
to
how I'm running statements in the other fields for the query).

Below is my entire SQL if you need it (have fun with that one):


SELECT IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]) AS Expr1,
IMAGING_BATCH_T.PROCESS_NM, IMAGING_FOLDER_T.ICN,
IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION, IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
FROM (IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM) INNER JOIN
IMAGING_CONTRACT_DIVISION_T ON IMAGING_BATCH_T.PROCESS_NM =
IMAGING_CONTRACT_DIVISION_T.PROCESS_NM
GROUP BY IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]),
IMAGING_BATCH_T.PROCESS_NM,
IMAGING_FOLDER_T.ICN, IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION, IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
HAVING
(((IMAGING_BATCH_T.BUCKET_NBR)=[Forms]![frmBucketNumber-index]![BucketNumber])
AND
((IMAGING_CONTRACT_DIVISION_T.CONTRACT)=[Forms]![frmBucketNumber-index]![Contract])
AND ((IMAGING_FOLDER_T.INDEX_DT) Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*"))
AND ((IMAGING_FOLDER_T.ICN) Like
IIf([Forms]![frmBucketNumber-index]![JulianDate]<>"",IIf([Forms]![frmBucketNumber-index]![Division]="Medicare","??"
& [Forms]![frmBucketNumber-index]![JulianDate] &
"??????",[Forms]![frmBucketNumber-index]![JulianDate] & "*"),"*")) AND
((IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM)="Madison"));

 

[buzz]

In that case, you can't use Like. Try:

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] IS NULL)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Index_DT is supposed to be a date/time field

Try using

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

although I suspect you may need to use

AND (IMAGING_FOLDER_T.INDEX_DT Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

(I'm assuming INDEX_DT is a text field, since LIKE isn't appropriate to
use
with Date/Time fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Well, I was looking all through this forum and couldn't find anything
to
really work. Seemed there were a lot of similar questions but the
resolutions didn't help mine.

I have a form that users set values on and a query is based on those
values.
The one problem I am dealing with is that the users have an option of a
hidden field showing to them to enter things on if needed; so they are
either
entering an Index_Date value or a number sequence that is part of this
ICN
field. My problem is with the IIf statement for the Index_Date field
because
either I base it on the form's field value or I just try and display
all
records (essentially using the IIf statement to figure out if it needs
to
filter or not) and I can't make this work right.

The criteria for this Index_Date field in the query is:

Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")

The prior works perfect if the field has a value; but I need it to also
show
the record if there is no value in the field. An "Is Null" criteria
for
this
field in the query will show me the values and even changing this query
field
to have a criteria of:

Like "*" Or Is Null

does exactly what I want. But I can't get this to work right in an IIf
statement. And any alterations I've been doing is giving me no record
results or that too complex query error (which I wonder if it may be
due
to
how I'm running statements in the other fields for the query).

Below is my entire SQL if you need it (have fun with that one):


SELECT IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]) AS Expr1,
IMAGING_BATCH_T.PROCESS_NM, IMAGING_FOLDER_T.ICN,
IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION, IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
FROM (IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM) INNER JOIN
IMAGING_CONTRACT_DIVISION_T ON IMAGING_BATCH_T.PROCESS_NM =
IMAGING_CONTRACT_DIVISION_T.PROCESS_NM
GROUP BY IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]),
IMAGING_BATCH_T.PROCESS_NM,
IMAGING_FOLDER_T.ICN, IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION, IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
HAVING
(((IMAGING_BATCH_T.BUCKET_NBR)=[Forms]![frmBucketNumber-index]![BucketNumber])
AND
((IMAGING_CONTRACT_DIVISION_T.CONTRACT)=[Forms]![frmBucketNumber-index]![Contract])
AND ((IMAGING_FOLDER_T.INDEX_DT) Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*"))
AND ((IMAGING_FOLDER_T.ICN) Like
IIf([Forms]![frmBucketNumber-index]![JulianDate]<>"",IIf([Forms]![frmBucketNumber-index]![Division]="Medicare","??"
& [Forms]![frmBucketNumber-index]![JulianDate] &
"??????",[Forms]![frmBucketNumber-index]![JulianDate] & "*"),"*")) AND
((IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM)="Madison"));

 

[Douglas J. Steele]

You might need to declare the parameter types.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

When I change to that, I get that stupid message about "This express is
typed
incorrectly or it is too complex to be evaluated. For example, a numeric
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables." I'm still
to
mess with this but I think I'm going to really have to strip this down to
get
it to do what I need it to and separate it into pieces.

In that case, you can't use Like. Try:

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] IS NULL)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Index_DT is supposed to be a date/time field

:

Try using

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

although I suspect you may need to use

AND (IMAGING_FOLDER_T.INDEX_DT Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

(I'm assuming INDEX_DT is a text field, since LIKE isn't appropriate
to
use
with Date/Time fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Well, I was looking all through this forum and couldn't find
anything
to
really work. Seemed there were a lot of similar questions but the
resolutions didn't help mine.

I have a form that users set values on and a query is based on those
values.
The one problem I am dealing with is that the users have an option
of a
hidden field showing to them to enter things on if needed; so they
are
either
entering an Index_Date value or a number sequence that is part of
this
ICN
field. My problem is with the IIf statement for the Index_Date
field
because
either I base it on the form's field value or I just try and display
all
records (essentially using the IIf statement to figure out if it
needs
to
filter or not) and I can't make this work right.

The criteria for this Index_Date field in the query is:

Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")

The prior works perfect if the field has a value; but I need it to
also
show
the record if there is no value in the field. An "Is Null" criteria
for
this
field in the query will show me the values and even changing this
query
field
to have a criteria of:

Like "*" Or Is Null

does exactly what I want. But I can't get this to work right in an
IIf
statement. And any alterations I've been doing is giving me no
record
results or that too complex query error (which I wonder if it may be
due
to
how I'm running statements in the other fields for the query).

Below is my entire SQL if you need it (have fun with that one):


SELECT IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]) AS Expr1,
IMAGING_BATCH_T.PROCESS_NM, IMAGING_FOLDER_T.ICN,
IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION,
IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
FROM (IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM) INNER JOIN
IMAGING_CONTRACT_DIVISION_T ON IMAGING_BATCH_T.PROCESS_NM =
IMAGING_CONTRACT_DIVISION_T.PROCESS_NM
GROUP BY IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]),
IMAGING_BATCH_T.PROCESS_NM,
IMAGING_FOLDER_T.ICN, IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION,
IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
HAVING
(((IMAGING_BATCH_T.BUCKET_NBR)=[Forms]![frmBucketNumber-index]![BucketNumber])
AND
((IMAGING_CONTRACT_DIVISION_T.CONTRACT)=[Forms]![frmBucketNumber-index]![Contract])
AND ((IMAGING_FOLDER_T.INDEX_DT) Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*"))
AND ((IMAGING_FOLDER_T.ICN) Like
IIf([Forms]![frmBucketNumber-index]![JulianDate]<>"",IIf([Forms]![frmBucketNumber-index]![Division]="Medicare","??"
& [Forms]![frmBucketNumber-index]![JulianDate] &
"??????",[Forms]![frmBucketNumber-index]![JulianDate] & "*"),"*"))
AND
((IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM)="Madison"));

 

[buzz]

I'll have to start looking things up there as that's starting to tread new
ground in the specifics here that I'm not used to. I've seen the parameter
settings but didn't really understand what they were for; now may be a time
to figure it out.

You might need to declare the parameter types.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

When I change to that, I get that stupid message about "This express is
typed
incorrectly or it is too complex to be evaluated. For example, a numeric
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables." I'm still
to
mess with this but I think I'm going to really have to strip this down to
get
it to do what I need it to and separate it into pieces.

In that case, you can't use Like. Try:

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] IS NULL)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Index_DT is supposed to be a date/time field

:

Try using

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

although I suspect you may need to use

AND (IMAGING_FOLDER_T.INDEX_DT Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

(I'm assuming INDEX_DT is a text field, since LIKE isn't appropriate
to
use
with Date/Time fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Well, I was looking all through this forum and couldn't find
anything
to
really work. Seemed there were a lot of similar questions but the
resolutions didn't help mine.

I have a form that users set values on and a query is based on those
values.
The one problem I am dealing with is that the users have an option
of a
hidden field showing to them to enter things on if needed; so they
are
either
entering an Index_Date value or a number sequence that is part of
this
ICN
field. My problem is with the IIf statement for the Index_Date
field
because
either I base it on the form's field value or I just try and display
all
records (essentially using the IIf statement to figure out if it
needs
to
filter or not) and I can't make this work right.

The criteria for this Index_Date field in the query is:

Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")

The prior works perfect if the field has a value; but I need it to
also
show
the record if there is no value in the field. An "Is Null" criteria
for
this
field in the query will show me the values and even changing this
query
field
to have a criteria of:

Like "*" Or Is Null

does exactly what I want. But I can't get this to work right in an
IIf
statement. And any alterations I've been doing is giving me no
record
results or that too complex query error (which I wonder if it may be
due
to
how I'm running statements in the other fields for the query).

Below is my entire SQL if you need it (have fun with that one):


SELECT IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]) AS Expr1,
IMAGING_BATCH_T.PROCESS_NM, IMAGING_FOLDER_T.ICN,
IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION,
IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
FROM (IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM) INNER JOIN
IMAGING_CONTRACT_DIVISION_T ON IMAGING_BATCH_T.PROCESS_NM =
IMAGING_CONTRACT_DIVISION_T.PROCESS_NM
GROUP BY IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]),
IMAGING_BATCH_T.PROCESS_NM,
IMAGING_FOLDER_T.ICN, IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION,
IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
HAVING
(((IMAGING_BATCH_T.BUCKET_NBR)=[Forms]![frmBucketNumber-index]![BucketNumber])
AND
((IMAGING_CONTRACT_DIVISION_T.CONTRACT)=[Forms]![frmBucketNumber-index]![Contract])
AND ((IMAGING_FOLDER_T.INDEX_DT) Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*"))
AND ((IMAGING_FOLDER_T.ICN) Like
IIf([Forms]![frmBucketNumber-index]![JulianDate]<>"",IIf([Forms]![frmBucketNumber-index]![Division]="Medicare","??"
& [Forms]![frmBucketNumber-index]![JulianDate] &
"??????",[Forms]![frmBucketNumber-index]![JulianDate] & "*"),"*"))
AND
((IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM)="Madison"));

 

[buzz]

I guess I'm a little new when to and when parameter types are not really
needed to be used.

But I defined a parameter type for
[Forms]![frmBucketNumber-index]![CalendarDate] to be a data/time format which
amazing enough allowed the query then to execute.

But it didn't work to have results found when the Index_DT value would not
have a value set or was blank. I did try to set the Index_DT criteria to
"[Forms]![frmBucketNumber-index]![CalendarDate] Or Is Null" which worked for
part of the conditions. But what did work was taking this one step further
to cover all conditions by setting the criteria to
"[Forms]![frmBucketNumber-index]![CalendarDate] Or
[Forms]![frmBucketNumber-index]![CalendarDate] Is Null Or Is Null".

Thanks for your help with this.

You might need to declare the parameter types.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

When I change to that, I get that stupid message about "This express is
typed
incorrectly or it is too complex to be evaluated. For example, a numeric
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables." I'm still
to
mess with this but I think I'm going to really have to strip this down to
get
it to do what I need it to and separate it into pieces.

In that case, you can't use Like. Try:

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] IS NULL)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Index_DT is supposed to be a date/time field

:

Try using

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

although I suspect you may need to use

AND (IMAGING_FOLDER_T.INDEX_DT Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

(I'm assuming INDEX_DT is a text field, since LIKE isn't appropriate
to
use
with Date/Time fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Well, I was looking all through this forum and couldn't find
anything
to
really work. Seemed there were a lot of similar questions but the
resolutions didn't help mine.

I have a form that users set values on and a query is based on those
values.
The one problem I am dealing with is that the users have an option
of a
hidden field showing to them to enter things on if needed; so they
are
either
entering an Index_Date value or a number sequence that is part of
this
ICN
field. My problem is with the IIf statement for the Index_Date
field
because
either I base it on the form's field value or I just try and display
all
records (essentially using the IIf statement to figure out if it
needs
to
filter or not) and I can't make this work right.

The criteria for this Index_Date field in the query is:

Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")

The prior works perfect if the field has a value; but I need it to
also
show
the record if there is no value in the field. An "Is Null" criteria
for
this
field in the query will show me the values and even changing this
query
field
to have a criteria of:

Like "*" Or Is Null

does exactly what I want. But I can't get this to work right in an
IIf
statement. And any alterations I've been doing is giving me no
record
results or that too complex query error (which I wonder if it may be
due
to
how I'm running statements in the other fields for the query).

Below is my entire SQL if you need it (have fun with that one):


SELECT IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]) AS Expr1,
IMAGING_BATCH_T.PROCESS_NM, IMAGING_FOLDER_T.ICN,
IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION,
IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
FROM (IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM) INNER JOIN
IMAGING_CONTRACT_DIVISION_T ON IMAGING_BATCH_T.PROCESS_NM =
IMAGING_CONTRACT_DIVISION_T.PROCESS_NM
GROUP BY IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]),
IMAGING_BATCH_T.PROCESS_NM,
IMAGING_FOLDER_T.ICN, IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION,
IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
HAVING
(((IMAGING_BATCH_T.BUCKET_NBR)=[Forms]![frmBucketNumber-index]![BucketNumber])
AND
((IMAGING_CONTRACT_DIVISION_T.CONTRACT)=[Forms]![frmBucketNumber-index]![Contract])
AND ((IMAGING_FOLDER_T.INDEX_DT) Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*"))
AND ((IMAGING_FOLDER_T.ICN) Like
IIf([Forms]![frmBucketNumber-index]![JulianDate]<>"",IIf([Forms]![frmBucketNumber-index]![Division]="Medicare","??"
& [Forms]![frmBucketNumber-index]![JulianDate] &
"??????",[Forms]![frmBucketNumber-index]![JulianDate] & "*"),"*"))
AND
((IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM)="Madison"));

 

[Douglas J. Steele]

You shouldn't need the "Or Is Null" at the end of the critera.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I guess I'm a little new when to and when parameter types are not really
needed to be used.

But I defined a parameter type for
[Forms]![frmBucketNumber-index]![CalendarDate] to be a data/time format
which
amazing enough allowed the query then to execute.

But it didn't work to have results found when the Index_DT value would not
have a value set or was blank. I did try to set the Index_DT criteria to
"[Forms]![frmBucketNumber-index]![CalendarDate] Or Is Null" which worked
for
part of the conditions. But what did work was taking this one step
further
to cover all conditions by setting the criteria to
"[Forms]![frmBucketNumber-index]![CalendarDate] Or
[Forms]![frmBucketNumber-index]![CalendarDate] Is Null Or Is Null".

Thanks for your help with this.

You might need to declare the parameter types.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

When I change to that, I get that stupid message about "This express is
typed
incorrectly or it is too complex to be evaluated. For example, a
numeric
expression may contain too many complicated elements. Try simplifying
the
expression by assigning parts of the expression to variables." I'm
still
to
mess with this but I think I'm going to really have to strip this down
to
get
it to do what I need it to and separate it into pieces.

:

In that case, you can't use Like. Try:

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] IS NULL)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Index_DT is supposed to be a date/time field

:

Try using

AND (IMAGING_FOLDER_T.INDEX_DT =
[Forms]![frmBucketNumber-index]![CalendarDate]
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

although I suspect you may need to use

AND (IMAGING_FOLDER_T.INDEX_DT Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")
OR [Forms]![frmBucketNumber-index]![CalendarDate] = '')

(I'm assuming INDEX_DT is a text field, since LIKE isn't
appropriate
to
use
with Date/Time fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Well, I was looking all through this forum and couldn't find
anything
to
really work. Seemed there were a lot of similar questions but
the
resolutions didn't help mine.

I have a form that users set values on and a query is based on
those
values.
The one problem I am dealing with is that the users have an
option
of a
hidden field showing to them to enter things on if needed; so
they
are
either
entering an Index_Date value or a number sequence that is part of
this
ICN
field. My problem is with the IIf statement for the Index_Date
field
because
either I base it on the form's field value or I just try and
display
all
records (essentially using the IIf statement to figure out if it
needs
to
filter or not) and I can't make this work right.

The criteria for this Index_Date field in the query is:

Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*")

The prior works perfect if the field has a value; but I need it
to
also
show
the record if there is no value in the field. An "Is Null"
criteria
for
this
field in the query will show me the values and even changing this
query
field
to have a criteria of:

Like "*" Or Is Null

does exactly what I want. But I can't get this to work right in
an
IIf
statement. And any alterations I've been doing is giving me no
record
results or that too complex query error (which I wonder if it may
be
due
to
how I'm running statements in the other fields for the query).

Below is my entire SQL if you need it (have fun with that one):


SELECT IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]) AS Expr1,
IMAGING_BATCH_T.PROCESS_NM, IMAGING_FOLDER_T.ICN,
IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION,
IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
FROM (IMAGING_BATCH_T INNER JOIN IMAGING_FOLDER_T ON
IMAGING_BATCH_T.BATCH_NM = IMAGING_FOLDER_T.BATCH_NM) INNER JOIN
IMAGING_CONTRACT_DIVISION_T ON IMAGING_BATCH_T.PROCESS_NM =
IMAGING_CONTRACT_DIVISION_T.PROCESS_NM
GROUP BY IMAGING_BATCH_T.BUCKET_NBR,
IMAGING_CONTRACT_DIVISION_T.CONTRACT,
IMAGING_FOLDER_T.INDEX_DT, UCase$([INDEX_OPER]),
IMAGING_BATCH_T.PROCESS_NM,
IMAGING_FOLDER_T.ICN, IMAGING_FOLDER_T.BATCH_NM,
IMAGING_CONTRACT_DIVISION_T.DIVISION,
IMAGING_FOLDER_T.REJECT_REASON,
IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM
HAVING
(((IMAGING_BATCH_T.BUCKET_NBR)=[Forms]![frmBucketNumber-index]![BucketNumber])
AND
((IMAGING_CONTRACT_DIVISION_T.CONTRACT)=[Forms]![frmBucketNumber-index]![Contract])
AND ((IMAGING_FOLDER_T.INDEX_DT) Like
IIf([Forms]![frmBucketNumber-index]![CalendarDate]<>'',[Forms]![frmBucketNumber-index]![CalendarDate],"*"))
AND ((IMAGING_FOLDER_T.ICN) Like
IIf([Forms]![frmBucketNumber-index]![JulianDate]<>"",IIf([Forms]![frmBucketNumber-index]![Division]="Medicare","??"
& [Forms]![frmBucketNumber-index]![JulianDate] &
"??????",[Forms]![frmBucketNumber-index]![JulianDate] &
"*"),"*"))
AND
((IMAGING_CONTRACT_DIVISION_T.SITE_LOC_NM)="Madison"));