IIF statement help

[Tony Williams]

I have a report which is based on a query. The query has 2 tables tblFees and
tblLevy. One of the controls has an IIf statement as its control source. This
is it:

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txttemnbr],[tblLevy].[txtmemnbr])

However when I run the report I get a prompt for "tblLevy"

What is wrong with my IIf statement?
Thanks
Tony

 

[Allen Browne]

Omit the table name, Tony:
=IIf([txtmemnbr] Is Null,[txttemnbr],[txtmemnbr])

The report knows the field by its name only (unless you have 2 fields with
the same name, from different tables.)

 

[Tony Williams]

Yes Allen, that is why I included the table name. Both tables have the same
field called txtmemnbr
Tony

Omit the table name, Tony:
=IIf([txtmemnbr] Is Null,[txttemnbr],[txtmemnbr])

The report knows the field by its name only (unless you have 2 fields with
the same name, from different tables.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report which is based on a query. The query has 2 tables tblFees
and
tblLevy. One of the controls has an IIf statement as its control source.
This
is it:

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txttemnbr],[tblLevy].[txtmemnbr])

However when I run the report I get a prompt for "tblLevy"

What is wrong with my IIf statement?
Thanks
Tony

 

[John Spencer]

Not according to what you posted. You have different names in what you posted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Yes Allen, that is why I included the table name. Both tables have the same
field called txtmemnbr
Tony

Omit the table name, Tony:
=IIf([txtmemnbr] Is Null,[txttemnbr],[txtmemnbr])

The report knows the field by its name only (unless you have 2 fields with
the same name, from different tables.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report which is based on a query. The query has 2 tables tblFees
and
tblLevy. One of the controls has an IIf statement as its control source.
This
is it:

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txttemnbr],[tblLevy].[txtmemnbr])

However when I run the report I get a prompt for "tblLevy"

What is wrong with my IIf statement?
Thanks
Tony

 

[Tony Williams]

Sorry John that was a typo here is the correct IIf statement

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

Thanks
Tony

Not according to what you posted. You have different names in what you posted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Yes Allen, that is why I included the table name. Both tables have the same
field called txtmemnbr
Tony

Omit the table name, Tony:
=IIf([txtmemnbr] Is Null,[txttemnbr],[txtmemnbr])

The report knows the field by its name only (unless you have 2 fields with
the same name, from different tables.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report which is based on a query. The query has 2 tables tblFees
and
tblLevy. One of the controls has an IIf statement as its control source.
This
is it:

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txttemnbr],[tblLevy].[txtmemnbr])

However when I run the report I get a prompt for "tblLevy"

What is wrong with my IIf statement?
Thanks
Tony

 

[KARL DEWEY]

If you made a typo in posting maybe you also made one in the query. Check to
see if you typed a comma where you should have a period - only one key over.
--
KARL DEWEY
Build a little - Test a little

Sorry John that was a typo here is the correct IIf statement

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

Thanks
Tony

Not according to what you posted. You have different names in what you posted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Yes Allen, that is why I included the table name. Both tables have the same
field called txtmemnbr
Tony

:

Omit the table name, Tony:
=IIf([txtmemnbr] Is Null,[txttemnbr],[txtmemnbr])

The report knows the field by its name only (unless you have 2 fields with
the same name, from different tables.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report which is based on a query. The query has 2 tables tblFees
and
tblLevy. One of the controls has an IIf statement as its control source.
This
is it:

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txttemnbr],[tblLevy].[txtmemnbr])

However when I run the report I get a prompt for "tblLevy"

What is wrong with my IIf statement?
Thanks
Tony

 

[Tony Williams]

Hi Karl I've checked this is the IIF Statement copied and pasted into this
message


=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

It looks OK to me?
Thanks
Tony

If you made a typo in posting maybe you also made one in the query. Check to
see if you typed a comma where you should have a period - only one key over.
--
KARL DEWEY
Build a little - Test a little

Sorry John that was a typo here is the correct IIf statement

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

Thanks
Tony

Not according to what you posted. You have different names in what you posted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Tony Williams wrote:
Yes Allen, that is why I included the table name. Both tables have the same
field called txtmemnbr
Tony

:

Omit the table name, Tony:
=IIf([txtmemnbr] Is Null,[txttemnbr],[txtmemnbr])

The report knows the field by its name only (unless you have 2 fields with
the same name, from different tables.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report which is based on a query. The query has 2 tables tblFees
and
tblLevy. One of the controls has an IIf statement as its control source.
This
is it:

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txttemnbr],[tblLevy].[txtmemnbr])

However when I run the report I get a prompt for "tblLevy"

What is wrong with my IIf statement?
Thanks
Tony

 

[KARL DEWEY]

Post the complete query SQL.
--
KARL DEWEY
Build a little - Test a little

Hi Karl I've checked this is the IIF Statement copied and pasted into this
message


=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

It looks OK to me?
Thanks
Tony

If you made a typo in posting maybe you also made one in the query. Check to
see if you typed a comma where you should have a period - only one key over.
--
KARL DEWEY
Build a little - Test a little

Sorry John that was a typo here is the correct IIf statement

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

Thanks
Tony


:

Not according to what you posted. You have different names in what you posted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Tony Williams wrote:
Yes Allen, that is why I included the table name. Both tables have the same
field called txtmemnbr
Tony

:

Omit the table name, Tony:
=IIf([txtmemnbr] Is Null,[txttemnbr],[txtmemnbr])

The report knows the field by its name only (unless you have 2 fields with
the same name, from different tables.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report which is based on a query. The query has 2 tables tblFees
and
tblLevy. One of the controls has an IIf statement as its control source.
This
is it:

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txttemnbr],[tblLevy].[txtmemnbr])

However when I run the report I get a prompt for "tblLevy"

What is wrong with my IIf statement?
Thanks
Tony

 

[Tony Williams]

Hi Karl here is the SQL for the query behind the report. The IIF statement
appears on a control on the report.


SELECT tblindividual.txtfirstname, tblindividual.txtsurname,
tblindividual.txtbusinessname, tblindividual.txtbusaddress1,
tblindividual.txtbusaddress2, tblindividual.txtbusaddress3,
tblindividual.txtbusaddress4, tblindividual.txtbuscity,
tblindividual.txtbuscounty, tblindividual.txtbuspostcode,
tblindividual.txtcategory, tblFees.txtmemnbr, tblFees.txtfee,
tblFees.txtfeedatepaid, tblLevy.txtmemnbr, tblLevy.txtlevy,
tblLevy.txtpaidbybusiness, tblLevy.txtlevydatepaid, tblLevy.txtnotes,
tblLevy.txtwritnbr
FROM (tblLevy RIGHT JOIN tblindividual ON tblLevy.txtmemnbr =
tblindividual.txtmemnumber) RIGHT JOIN tblFees ON tblindividual.txtmemnumber
= tblFees.txtmemnbr
WHERE (((tblFees.txtfeedatepaid) Between
[Forms]![frmdateslevyfees]![txtfrom] And [Forms]![frmdateslevyfees]![txtto]))
OR (((tblLevy.txtlevydatepaid) Between [Forms]![frmdateslevyfees]![txtfrom]
And [Forms]![frmdateslevyfees]![txtto]));

Hope that helps?
Tony

Post the complete query SQL.
--
KARL DEWEY
Build a little - Test a little

Hi Karl I've checked this is the IIF Statement copied and pasted into this
message


=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

It looks OK to me?
Thanks
Tony

If you made a typo in posting maybe you also made one in the query. Check to
see if you typed a comma where you should have a period - only one key over.
--
KARL DEWEY
Build a little - Test a little


:

Sorry John that was a typo here is the correct IIf statement

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

Thanks
Tony


:

Not according to what you posted. You have different names in what you posted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Tony Williams wrote:
Yes Allen, that is why I included the table name. Both tables have the same
field called txtmemnbr
Tony

:

Omit the table name, Tony:
=IIf([txtmemnbr] Is Null,[txttemnbr],[txtmemnbr])

The report knows the field by its name only (unless you have 2 fields with
the same name, from different tables.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report which is based on a query. The query has 2 tables tblFees
and
tblLevy. One of the controls has an IIf statement as its control source.
This
is it:

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txttemnbr],[tblLevy].[txtmemnbr])

However when I run the report I get a prompt for "tblLevy"

What is wrong with my IIf statement?
Thanks
Tony

 

[KARL DEWEY]

I do not see a problem. Sometimes there is hidden stuff that does not show
in SQL.

A suggest that has had results for others is to copy the SQL and paste into
another query.
--
KARL DEWEY
Build a little - Test a little

Hi Karl here is the SQL for the query behind the report. The IIF statement
appears on a control on the report.


SELECT tblindividual.txtfirstname, tblindividual.txtsurname,
tblindividual.txtbusinessname, tblindividual.txtbusaddress1,
tblindividual.txtbusaddress2, tblindividual.txtbusaddress3,
tblindividual.txtbusaddress4, tblindividual.txtbuscity,
tblindividual.txtbuscounty, tblindividual.txtbuspostcode,
tblindividual.txtcategory, tblFees.txtmemnbr, tblFees.txtfee,
tblFees.txtfeedatepaid, tblLevy.txtmemnbr, tblLevy.txtlevy,
tblLevy.txtpaidbybusiness, tblLevy.txtlevydatepaid, tblLevy.txtnotes,
tblLevy.txtwritnbr
FROM (tblLevy RIGHT JOIN tblindividual ON tblLevy.txtmemnbr =
tblindividual.txtmemnumber) RIGHT JOIN tblFees ON tblindividual.txtmemnumber
= tblFees.txtmemnbr
WHERE (((tblFees.txtfeedatepaid) Between
[Forms]![frmdateslevyfees]![txtfrom] And [Forms]![frmdateslevyfees]![txtto]))
OR (((tblLevy.txtlevydatepaid) Between [Forms]![frmdateslevyfees]![txtfrom]
And [Forms]![frmdateslevyfees]![txtto]));

Hope that helps?
Tony

Post the complete query SQL.
--
KARL DEWEY
Build a little - Test a little

Hi Karl I've checked this is the IIF Statement copied and pasted into this
message


=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

It looks OK to me?
Thanks
Tony

:

If you made a typo in posting maybe you also made one in the query. Check to
see if you typed a comma where you should have a period - only one key over.
--
KARL DEWEY
Build a little - Test a little


:

Sorry John that was a typo here is the correct IIf statement

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

Thanks
Tony


:

Not according to what you posted. You have different names in what you posted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Tony Williams wrote:
Yes Allen, that is why I included the table name. Both tables have the same
field called txtmemnbr
Tony

:

Omit the table name, Tony:
=IIf([txtmemnbr] Is Null,[txttemnbr],[txtmemnbr])

The report knows the field by its name only (unless you have 2 fields with
the same name, from different tables.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report which is based on a query. The query has 2 tables tblFees
and
tblLevy. One of the controls has an IIf statement as its control source.
This
is it:

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txttemnbr],[tblLevy].[txtmemnbr])

However when I run the report I get a prompt for "tblLevy"

What is wrong with my IIf statement?
Thanks
Tony

 

[Tony Williams]

Hi Karl, the SQL appears to be OK I get the same results in another query but
the IIF statement in the report still does not work?
Tony

I do not see a problem. Sometimes there is hidden stuff that does not show
in SQL.

A suggest that has had results for others is to copy the SQL and paste into
another query.
--
KARL DEWEY
Build a little - Test a little

Hi Karl here is the SQL for the query behind the report. The IIF statement
appears on a control on the report.


SELECT tblindividual.txtfirstname, tblindividual.txtsurname,
tblindividual.txtbusinessname, tblindividual.txtbusaddress1,
tblindividual.txtbusaddress2, tblindividual.txtbusaddress3,
tblindividual.txtbusaddress4, tblindividual.txtbuscity,
tblindividual.txtbuscounty, tblindividual.txtbuspostcode,
tblindividual.txtcategory, tblFees.txtmemnbr, tblFees.txtfee,
tblFees.txtfeedatepaid, tblLevy.txtmemnbr, tblLevy.txtlevy,
tblLevy.txtpaidbybusiness, tblLevy.txtlevydatepaid, tblLevy.txtnotes,
tblLevy.txtwritnbr
FROM (tblLevy RIGHT JOIN tblindividual ON tblLevy.txtmemnbr =
tblindividual.txtmemnumber) RIGHT JOIN tblFees ON tblindividual.txtmemnumber
= tblFees.txtmemnbr
WHERE (((tblFees.txtfeedatepaid) Between
[Forms]![frmdateslevyfees]![txtfrom] And [Forms]![frmdateslevyfees]![txtto]))
OR (((tblLevy.txtlevydatepaid) Between [Forms]![frmdateslevyfees]![txtfrom]
And [Forms]![frmdateslevyfees]![txtto]));

Hope that helps?
Tony

Post the complete query SQL.
--
KARL DEWEY
Build a little - Test a little


:

Hi Karl I've checked this is the IIF Statement copied and pasted into this
message


=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

It looks OK to me?
Thanks
Tony

:

If you made a typo in posting maybe you also made one in the query. Check to
see if you typed a comma where you should have a period - only one key over.
--
KARL DEWEY
Build a little - Test a little


:

Sorry John that was a typo here is the correct IIf statement

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

Thanks
Tony


:

Not according to what you posted. You have different names in what you posted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Tony Williams wrote:
Yes Allen, that is why I included the table name. Both tables have the same
field called txtmemnbr
Tony

:

Omit the table name, Tony:
=IIf([txtmemnbr] Is Null,[txttemnbr],[txtmemnbr])

The report knows the field by its name only (unless you have 2 fields with
the same name, from different tables.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report which is based on a query. The query has 2 tables tblFees
and
tblLevy. One of the controls has an IIf statement as its control source.
This
is it:

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txttemnbr],[tblLevy].[txtmemnbr])

However when I run the report I get a prompt for "tblLevy"

What is wrong with my IIf statement?
Thanks
Tony

 

[KARL DEWEY]

I did not read close enough nor review the SQL except for 'tblLevy.'
I suggest you put the IIF in the query instead of the control.
--
KARL DEWEY
Build a little - Test a little

Hi Karl, the SQL appears to be OK I get the same results in another query but
the IIF statement in the report still does not work?
Tony

I do not see a problem. Sometimes there is hidden stuff that does not show
in SQL.

A suggest that has had results for others is to copy the SQL and paste into
another query.
--
KARL DEWEY
Build a little - Test a little

Hi Karl here is the SQL for the query behind the report. The IIF statement
appears on a control on the report.


SELECT tblindividual.txtfirstname, tblindividual.txtsurname,
tblindividual.txtbusinessname, tblindividual.txtbusaddress1,
tblindividual.txtbusaddress2, tblindividual.txtbusaddress3,
tblindividual.txtbusaddress4, tblindividual.txtbuscity,
tblindividual.txtbuscounty, tblindividual.txtbuspostcode,
tblindividual.txtcategory, tblFees.txtmemnbr, tblFees.txtfee,
tblFees.txtfeedatepaid, tblLevy.txtmemnbr, tblLevy.txtlevy,
tblLevy.txtpaidbybusiness, tblLevy.txtlevydatepaid, tblLevy.txtnotes,
tblLevy.txtwritnbr
FROM (tblLevy RIGHT JOIN tblindividual ON tblLevy.txtmemnbr =
tblindividual.txtmemnumber) RIGHT JOIN tblFees ON tblindividual.txtmemnumber
= tblFees.txtmemnbr
WHERE (((tblFees.txtfeedatepaid) Between
[Forms]![frmdateslevyfees]![txtfrom] And [Forms]![frmdateslevyfees]![txtto]))
OR (((tblLevy.txtlevydatepaid) Between [Forms]![frmdateslevyfees]![txtfrom]
And [Forms]![frmdateslevyfees]![txtto]));

Hope that helps?
Tony


:

Post the complete query SQL.
--
KARL DEWEY
Build a little - Test a little


:

Hi Karl I've checked this is the IIF Statement copied and pasted into this
message


=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

It looks OK to me?
Thanks
Tony

:

If you made a typo in posting maybe you also made one in the query. Check to
see if you typed a comma where you should have a period - only one key over.
--
KARL DEWEY
Build a little - Test a little


:

Sorry John that was a typo here is the correct IIf statement

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

Thanks
Tony


:

Not according to what you posted. You have different names in what you posted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Tony Williams wrote:
Yes Allen, that is why I included the table name. Both tables have the same
field called txtmemnbr
Tony

:

Omit the table name, Tony:
=IIf([txtmemnbr] Is Null,[txttemnbr],[txtmemnbr])

The report knows the field by its name only (unless you have 2 fields with
the same name, from different tables.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report which is based on a query. The query has 2 tables tblFees
and
tblLevy. One of the controls has an IIf statement as its control source.
This
is it:

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txttemnbr],[tblLevy].[txtmemnbr])

However when I run the report I get a prompt for "tblLevy"

What is wrong with my IIf statement?
Thanks
Tony

 

[Tony Williams]

I'll try that Karl but tomorrow. It's been a long day and at 20.08 I've had
enough
Cheers
Tony

I did not read close enough nor review the SQL except for 'tblLevy.'
I suggest you put the IIF in the query instead of the control.
--
KARL DEWEY
Build a little - Test a little

Hi Karl, the SQL appears to be OK I get the same results in another query but
the IIF statement in the report still does not work?
Tony

I do not see a problem. Sometimes there is hidden stuff that does not show
in SQL.

A suggest that has had results for others is to copy the SQL and paste into
another query.
--
KARL DEWEY
Build a little - Test a little


:

Hi Karl here is the SQL for the query behind the report. The IIF statement
appears on a control on the report.


SELECT tblindividual.txtfirstname, tblindividual.txtsurname,
tblindividual.txtbusinessname, tblindividual.txtbusaddress1,
tblindividual.txtbusaddress2, tblindividual.txtbusaddress3,
tblindividual.txtbusaddress4, tblindividual.txtbuscity,
tblindividual.txtbuscounty, tblindividual.txtbuspostcode,
tblindividual.txtcategory, tblFees.txtmemnbr, tblFees.txtfee,
tblFees.txtfeedatepaid, tblLevy.txtmemnbr, tblLevy.txtlevy,
tblLevy.txtpaidbybusiness, tblLevy.txtlevydatepaid, tblLevy.txtnotes,
tblLevy.txtwritnbr
FROM (tblLevy RIGHT JOIN tblindividual ON tblLevy.txtmemnbr =
tblindividual.txtmemnumber) RIGHT JOIN tblFees ON tblindividual.txtmemnumber
= tblFees.txtmemnbr
WHERE (((tblFees.txtfeedatepaid) Between
[Forms]![frmdateslevyfees]![txtfrom] And [Forms]![frmdateslevyfees]![txtto]))
OR (((tblLevy.txtlevydatepaid) Between [Forms]![frmdateslevyfees]![txtfrom]
And [Forms]![frmdateslevyfees]![txtto]));

Hope that helps?
Tony


:

Post the complete query SQL.
--
KARL DEWEY
Build a little - Test a little


:

Hi Karl I've checked this is the IIF Statement copied and pasted into this
message


=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

It looks OK to me?
Thanks
Tony

:

If you made a typo in posting maybe you also made one in the query. Check to
see if you typed a comma where you should have a period - only one key over.
--
KARL DEWEY
Build a little - Test a little


:

Sorry John that was a typo here is the correct IIf statement

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

Thanks
Tony


:

Not according to what you posted. You have different names in what you posted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Tony Williams wrote:
Yes Allen, that is why I included the table name. Both tables have the same
field called txtmemnbr
Tony

:

Omit the table name, Tony:
=IIf([txtmemnbr] Is Null,[txttemnbr],[txtmemnbr])

The report knows the field by its name only (unless you have 2 fields with
the same name, from different tables.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report which is based on a query. The query has 2 tables tblFees
and
tblLevy. One of the controls has an IIf statement as its control source.
This
is it:

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txttemnbr],[tblLevy].[txtmemnbr])

However when I run the report I get a prompt for "tblLevy"

What is wrong with my IIf statement?
Thanks
Tony

 

[Allen Browne]

Tony, alias the fields in the query so they have different names (e.g. as
below.)

Then make sure you have text boxes for BOTH fields on the report (even if
hidden.)

You should then be able to refer to them in your IIf() expression.

SELECT tblindividual.txtfirstname,
tblindividual.txtsurname,
tblindividual.txtbusinessname,
tblindividual.txtbusaddress1,
tblindividual.txtbusaddress2,
tblindividual.txtbusaddress3,
tblindividual.txtbusaddress4,
tblindividual.txtbuscity,
tblindividual.txtbuscounty,
tblindividual.txtbuspostcode,
tblindividual.txtcategory,
tblFees.txtmemnbr AS FeesMemNbr,
tblFees.txtfee,
tblFees.txtfeedatepaid,
tblLevy.txtmemnbr AS LevyMemNbr,
tblLevy.txtlevy,
tblLevy.txtpaidbybusiness,
tblLevy.txtlevydatepaid,
tblLevy.txtnotes,
tblLevy.txtwritnbr
FROM (tblLevy RIGHT JOIN tblindividual
ON tblLevy.txtmemnbr = tblindividual.txtmemnumber)
RIGHT JOIN tblFees ON tblindividual.txtmemnumber = tblFees.txtmemnbr
WHERE (((tblFees.txtfeedatepaid) Between
[Forms]![frmdateslevyfees]![txtfrom] And
[Forms]![frmdateslevyfees]![txtto]))
OR (((tblLevy.txtlevydatepaid) Between [Forms]![frmdateslevyfees]![txtfrom]
And [Forms]![frmdateslevyfees]![txtto]));

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'll try that Karl but tomorrow. It's been a long day and at 20.08 I've
had
enough
Cheers
Tony

I did not read close enough nor review the SQL except for 'tblLevy.'
I suggest you put the IIF in the query instead of the control.
--
KARL DEWEY
Build a little - Test a little

Hi Karl, the SQL appears to be OK I get the same results in another
query but
the IIF statement in the report still does not work?
Tony

:

I do not see a problem. Sometimes there is hidden stuff that does
not show
in SQL.

A suggest that has had results for others is to copy the SQL and
paste into
another query.
--
KARL DEWEY
Build a little - Test a little


:

Hi Karl here is the SQL for the query behind the report. The IIF
statement
appears on a control on the report.


SELECT tblindividual.txtfirstname, tblindividual.txtsurname,
tblindividual.txtbusinessname, tblindividual.txtbusaddress1,
tblindividual.txtbusaddress2, tblindividual.txtbusaddress3,
tblindividual.txtbusaddress4, tblindividual.txtbuscity,
tblindividual.txtbuscounty, tblindividual.txtbuspostcode,
tblindividual.txtcategory, tblFees.txtmemnbr, tblFees.txtfee,
tblFees.txtfeedatepaid, tblLevy.txtmemnbr, tblLevy.txtlevy,
tblLevy.txtpaidbybusiness, tblLevy.txtlevydatepaid,
tblLevy.txtnotes,
tblLevy.txtwritnbr
FROM (tblLevy RIGHT JOIN tblindividual ON tblLevy.txtmemnbr =
tblindividual.txtmemnumber) RIGHT JOIN tblFees ON
tblindividual.txtmemnumber
= tblFees.txtmemnbr
WHERE (((tblFees.txtfeedatepaid) Between
[Forms]![frmdateslevyfees]![txtfrom] And
[Forms]![frmdateslevyfees]![txtto]))
OR (((tblLevy.txtlevydatepaid) Between
[Forms]![frmdateslevyfees]![txtfrom]
And [Forms]![frmdateslevyfees]![txtto]));

Hope that helps?
Tony


:

Post the complete query SQL.
--
KARL DEWEY
Build a little - Test a little


:

Hi Karl I've checked this is the IIF Statement copied and
pasted into this
message


=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

It looks OK to me?
Thanks
Tony

:

If you made a typo in posting maybe you also made one in the
query. Check to
see if you typed a comma where you should have a period -
only one key over.
--
KARL DEWEY
Build a little - Test a little


:

Sorry John that was a typo here is the correct IIf
statement

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txtmemnbr],[tblLevy].[txtmemnbr])

Thanks
Tony


:

Not according to what you posted. You have different
names in what you posted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Tony Williams wrote:
Yes Allen, that is why I included the table name. Both
tables have the same
field called txtmemnbr
Tony

:

Omit the table name, Tony:
=IIf([txtmemnbr] Is Null,[txttemnbr],[txtmemnbr])

The report knows the field by its name only (unless
you have 2 fields with
the same name, from different tables.)

"Tony Williams"
message
I have a report which is based on a query. The query
has 2 tables tblFees
and
tblLevy. One of the controls has an IIf statement as
its control source.
This
is it:

=IIf([tblLevy].[txtmemnbr] Is
Null,[tblfees].[txttemnbr],[tblLevy].[txtmemnbr])

However when I run the report I get a prompt for
"tblLevy"

What is wrong with my IIf statement?