Nested IIF statements

  • Thread starter Thread starter Darhl Thomason
  • Start date Start date
D

Darhl Thomason

I have an unbound text box control in the header of my report. I'm trying
to use nested IIF statements to control what it says. Here's what I'm
trying to do: On my form, I have optional text boxes for
txtInstallStartDate and txtInstallEndDate. On my report, if someone puts in
txtInstallStartDate then I want the header to say "[StatusName] since
<txtInstallStartDate>". If someone puts in txtInstallEndDate I want the
header to say "[StatusName] Thru <txtInstallEndDate>". If someone puts in
both dates, I want the header to say "[StatusName] <txtInstallStartDate>
thru <txtInstallEndDate>". If they leave both dates blank, I want the
header to say "[StatusName]".

When I run the report, it responds with error 3075, Syntax error (missing
operator) in query expression and lists my control source, but it's
truncated and doesn't list the entire control source, I'm assuming it's
because it's too long. My control source is listed below.

I was thinking of doing this in my form's module with if/then statements
then pass the value to the report to display, but [StatusName] is a grouping
field for the report so I'm not sure if I can do it. I also don't know if I
can put that kind of code in the report's header.

Thanks for taking a look,

Darhl

iif(isnull(Forms!frmStoreData.txtInstallStartDate),iif(isnull(Forms!frmStoreData.txtInstallEndDate),=[StatusName],=[StatusName]
& " thru " &
[Forms!frmStoreData.txtInstallEndDate]),iif(isnull(Forms!frmStoreData.txtInstallEndDate),=[StatusName]
& " since " & [Forms!frmStoreData.txtInstallStartDate],=[StatusName] &
[Forms!frmStoreData.txtInstallStartDate] & " thru " &
[Forms!frmStoreData.txtInstallEndDate]))
 
Without check your entire expression, you should have only one "=" as the
very first character in the expression:

=IIf(isnull(Forms!frmStoreData...
 
Thanks Duane,

I'll give that a shot. Is the IIF statement too long, or should that not be
a problem?

Thanks,

Darhl


Duane Hookom said:
Without check your entire expression, you should have only one "=" as the
very first character in the expression:

=IIf(isnull(Forms!frmStoreData...

--
Duane Hookom
MS Access MVP
--

Darhl Thomason said:
I have an unbound text box control in the header of my report. I'm trying
to use nested IIF statements to control what it says. Here's what I'm
trying to do: On my form, I have optional text boxes for
txtInstallStartDate and txtInstallEndDate. On my report, if someone puts
in txtInstallStartDate then I want the header to say "[StatusName] since
<txtInstallStartDate>". If someone puts in txtInstallEndDate I want the
header to say "[StatusName] Thru <txtInstallEndDate>". If someone puts in
both dates, I want the header to say "[StatusName] <txtInstallStartDate>
thru <txtInstallEndDate>". If they leave both dates blank, I want the
header to say "[StatusName]".

When I run the report, it responds with error 3075, Syntax error (missing
operator) in query expression and lists my control source, but it's
truncated and doesn't list the entire control source, I'm assuming it's
because it's too long. My control source is listed below.

I was thinking of doing this in my form's module with if/then statements
then pass the value to the report to display, but [StatusName] is a
grouping field for the report so I'm not sure if I can do it. I also
don't know if I can put that kind of code in the report's header.

Thanks for taking a look,

Darhl

iif(isnull(Forms!frmStoreData.txtInstallStartDate),iif(isnull(Forms!frmStoreData.txtInstallEndDate),=[StatusName],=[StatusName]
& " thru " &
[Forms!frmStoreData.txtInstallEndDate]),iif(isnull(Forms!frmStoreData.txtInstallEndDate),=[StatusName]
& " since " & [Forms!frmStoreData.txtInstallStartDate],=[StatusName] &
[Forms!frmStoreData.txtInstallStartDate] & " thru " &
[Forms!frmStoreData.txtInstallEndDate]))
Click to expand...
Click to expand...
 
OK, it wasn't too long. The = in the front and nowhere else did the trick.

Thanks a ton!

Darhl


Darhl Thomason said:
Thanks Duane,

I'll give that a shot. Is the IIF statement too long, or should that not
be a problem?

Thanks,

Darhl


Duane Hookom said:
Without check your entire expression, you should have only one "=" as the
very first character in the expression:

=IIf(isnull(Forms!frmStoreData...

--
Duane Hookom
MS Access MVP
--

Darhl Thomason said:
I have an unbound text box control in the header of my report. I'm
trying to use nested IIF statements to control what it says. Here's what
I'm trying to do: On my form, I have optional text boxes for
txtInstallStartDate and txtInstallEndDate. On my report, if someone puts
in txtInstallStartDate then I want the header to say "[StatusName] since
<txtInstallStartDate>". If someone puts in txtInstallEndDate I want the
header to say "[StatusName] Thru <txtInstallEndDate>". If someone puts
in both dates, I want the header to say "[StatusName]
<txtInstallStartDate> thru <txtInstallEndDate>". If they leave both
dates blank, I want the header to say "[StatusName]".

When I run the report, it responds with error 3075, Syntax error
(missing operator) in query expression and lists my control source, but
it's truncated and doesn't list the entire control source, I'm assuming
it's because it's too long. My control source is listed below.

I was thinking of doing this in my form's module with if/then statements
then pass the value to the report to display, but [StatusName] is a
grouping field for the report so I'm not sure if I can do it. I also
don't know if I can put that kind of code in the report's header.

Thanks for taking a look,

Darhl

iif(isnull(Forms!frmStoreData.txtInstallStartDate),iif(isnull(Forms!frmStoreData.txtInstallEndDate),=[StatusName],=[StatusName]
& " thru " &
[Forms!frmStoreData.txtInstallEndDate]),iif(isnull(Forms!frmStoreData.txtInstallEndDate),=[StatusName]
& " since " & [Forms!frmStoreData.txtInstallStartDate],=[StatusName] &
[Forms!frmStoreData.txtInstallStartDate] & " thru " &
[Forms!frmStoreData.txtInstallEndDate]))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top