IIF Statement used in ListBox RowSource

[Guest]

I got a form with a ListBox that has the following RowSource

SELECT IIf([SumOfCommission]>600,"X","") AS Chk, tblLocation.Name,
Sum(tblIncomeSum.Commission) AS SumOfCommission
FROM tblLocation INNER JOIN tblIncomeSum ON tblLocation.LocationID =
tblIncomeSum.LocationID
WHERE (((Year(([tblIncomeSum].[Date])))=Year(Date())-1))
GROUP BY tblLocation.Name;

And it works fine when the form is opened. But after I do a update to a text
box I run the following code pretty much the same SQL just the year(Date())-1
is changed

Dim strSQL As String
strSQL = "SELECT IIf([SumOfCommission]>600,""X"","") AS Chk,
tblLocation.Name, Sum(tblIncomeSum.Commission) AS SumOfCommission " & _
"FROM tblLocation INNER JOIN tblIncomeSum ON
tblLocation.LocationID = tblIncomeSum.LocationID " & _
"WHERE (((Year(([tblIncomeSum].[Date]))) =
[forms]![frmTaxForm]![txtYear])) " & _
"GROUP BY tblLocation.Name;"

lstLocationName.RowSource = strSQL

The ListBox now is empty, Why? I came to the conclusion that my IIF
STATEMENT is to blame because it will work if I don’t use the IIF STATEMENT.
What's up with that??
Any ideas??

 

[RoyVidar]

I got a form with a ListBox that has the following RowSource

SELECT IIf([SumOfCommission]>600,"X","") AS Chk, tblLocation.Name,
Sum(tblIncomeSum.Commission) AS SumOfCommission
FROM tblLocation INNER JOIN tblIncomeSum ON tblLocation.LocationID =
tblIncomeSum.LocationID
WHERE (((Year(([tblIncomeSum].[Date])))=Year(Date())-1))
GROUP BY tblLocation.Name;

And it works fine when the form is opened. But after I do a update to
a text box I run the following code pretty much the same SQL just
the year(Date())-1 is changed

Dim strSQL As String
strSQL = "SELECT IIf([SumOfCommission]>600,""X"","") AS Chk,
tblLocation.Name, Sum(tblIncomeSum.Commission) AS SumOfCommission " &
_ "FROM tblLocation INNER JOIN tblIncomeSum ON
tblLocation.LocationID = tblIncomeSum.LocationID " & _
"WHERE (((Year(([tblIncomeSum].[Date]))) =
[forms]![frmTaxForm]![txtYear])) " & _
"GROUP BY tblLocation.Name;"

lstLocationName.RowSource = strSQL

The ListBox now is empty, Why? I came to the conclusion that my IIF
STATEMENT is to blame because it will work if I don’t use the IIF
STATEMENT. What's up with that??
Any ideas??

I don't know, but I think for starters, I'd try to enter the value
of your form control into the string, and not the reference

....WHERE Year([tblIncomeSum].[Date]) = " & _
[forms]![frmTaxForm]![txtYear] & _ ...

If the control is on the current form, Me!txtYear should suffice.

 

[RoyVidar]

I got a form with a ListBox that has the following RowSource

SELECT IIf([SumOfCommission]>600,"X","") AS Chk, tblLocation.Name,
Sum(tblIncomeSum.Commission) AS SumOfCommission
FROM tblLocation INNER JOIN tblIncomeSum ON tblLocation.LocationID =
tblIncomeSum.LocationID
WHERE (((Year(([tblIncomeSum].[Date])))=Year(Date())-1))
GROUP BY tblLocation.Name;

And it works fine when the form is opened. But after I do a update
to a text box I run the following code pretty much the same SQL
just the year(Date())-1 is changed

Dim strSQL As String
strSQL = "SELECT IIf([SumOfCommission]>600,""X"","") AS Chk,
tblLocation.Name, Sum(tblIncomeSum.Commission) AS SumOfCommission "
& _ "FROM tblLocation INNER JOIN tblIncomeSum ON
tblLocation.LocationID = tblIncomeSum.LocationID " & _
"WHERE (((Year(([tblIncomeSum].[Date]))) =
[forms]![frmTaxForm]![txtYear])) " & _
"GROUP BY tblLocation.Name;"

lstLocationName.RowSource = strSQL

The ListBox now is empty, Why? I came to the conclusion that my IIF
STATEMENT is to blame because it will work if I don’t use the IIF
STATEMENT. What's up with that??
Any ideas??

I don't know, but I think for starters, I'd try to enter the value
of your form control into the string, and not the reference

...WHERE Year([tblIncomeSum].[Date]) = " & _
[forms]![frmTaxForm]![txtYear] & _ ...

If the control is on the current form, Me!txtYear should suffice.

Oh - sorry, forgot - for this, you'd need an extra space, for instance
before the GROUP BY clause

...." GROUP BY...

 

[RoyVidar]

the WHERE works fine I'v ran it and it work's. It's the IIF that does
not work. Thanks for the input, any other Idea's
--
thank You

<[email protected]>:
I got a form with a ListBox that has the following RowSource

SELECT IIf([SumOfCommission]>600,"X","") AS Chk, tblLocation.Name,
Sum(tblIncomeSum.Commission) AS SumOfCommission
FROM tblLocation INNER JOIN tblIncomeSum ON tblLocation.LocationID
= tblIncomeSum.LocationID
WHERE (((Year(([tblIncomeSum].[Date])))=Year(Date())-1))
GROUP BY tblLocation.Name;

And it works fine when the form is opened. But after I do a update
to a text box I run the following code pretty much the same SQL
just the year(Date())-1 is changed

Dim strSQL As String
strSQL = "SELECT IIf([SumOfCommission]>600,""X"","") AS
Chk, tblLocation.Name, Sum(tblIncomeSum.Commission) AS
SumOfCommission " & _ "FROM tblLocation INNER JOIN
tblIncomeSum ON tblLocation.LocationID = tblIncomeSum.LocationID
" & _ "WHERE (((Year(([tblIncomeSum].[Date]))) =
[forms]![frmTaxForm]![txtYear])) " & _
"GROUP BY tblLocation.Name;"

lstLocationName.RowSource = strSQL

The ListBox now is empty, Why? I came to the conclusion that my
IIF STATEMENT is to blame because it will work if I don’t use the
IIF STATEMENT. What's up with that??
Any ideas??

I don't know, but I think for starters, I'd try to enter the value
of your form control into the string, and not the reference

...WHERE Year([tblIncomeSum].[Date]) = " & _
[forms]![frmTaxForm]![txtYear] & _ ...

If the control is on the current form, Me!txtYear should suffice.

Oh - sorry, forgot - for this, you'd need an extra space, for
instance before the GROUP BY clause

...." GROUP BY...

For the False part, try either with four quotes, or none.

....IIf([SumOfCommission]>600,""X"")...

....IIf([SumOfCommission]>600,""X"", """")...

 

[Rick Brandt]

the WHERE works fine I'v ran it and it work's. It's the IIF that does
not work. Thanks for the input, any other Idea's

The IIf() cannot cause your ListBox to be empty. It could cause the first
column to be blank since one of the possible outputs from the IIF() is "", but
you would still see the other columns filled with data.

The only exception I can think of is if the IIf() is causing an error in the
query and that results in getting no rows back from the query. However; unless
you are using an ODBC source for the query that should cause an Error message to
be displayed.

What is the value you have in [forms]![frmTaxForm]![txtYear] when the ListBox is
empty?

 

[Rick Brandt]

the value i been using in the [forms]![frmTaxForm]![txtYear] is 2006.
i can repalce the expression with 2006 and still get a empty
ListBox. If I remove the IIF in the strSQL statemnet it works fine.
But Again the hole thing works fine in the Row Source of the
propertie box for the ListBox. I know this because when i open the
form the ListBox has all my valves. I just dont understand this one.

If it's not already, save the RowSource as an actual Query that appears in
the db window. Then you can open the form, experiment with different
entries on the form and see how the saved query responds instead of trying
to interpret what is going on with the ListBox. That should let you see
what the problem is.

 

[mcescher]

I tried to duplicate just the main query, and when I looked at the SQL,
Access had added the IIF statement to the GROUP BY. Also, Access
prompted me for the value of "SumOfCommission". I hit enter and it
calculated correctly, but if you have warnings turned off, that may be
giving you some problems.

One of the earlier posts mentioned the four quotes for the false
portion of your IIF statement. Also make sure you have a space before
the SQL clauses.

Debug print your SQL statement after you've created it, to see if it's
really what you wanted.

Perhaps a better IIF statement would be

IIF(Sum(Commission)>600,"X","") As Chk

That's a couple of things to try anyway, hth,
Chris M.

 

[mcescher]

Glad to help.

Chris M.

GROUP BY than's it!!!!!
--
thank You

I tried to duplicate just the main query, and when I looked at the SQL,
Access had added the IIF statement to the GROUP BY. Also, Access
prompted me for the value of "SumOfCommission". I hit enter and it
calculated correctly, but if you have warnings turned off, that may be
giving you some problems.

One of the earlier posts mentioned the four quotes for the false
portion of your IIF statement. Also make sure you have a space before
the SQL clauses.

Debug print your SQL statement after you've created it, to see if it's
really what you wanted.

Perhaps a better IIF statement would be

IIF(Sum(Commission)>600,"X","") As Chk

That's a couple of things to try anyway, hth,
Chris M.

I got a form with a ListBox that has the following RowSource
SELECT IIf([SumOfCommission]>600,"X","") AS Chk, tblLocation.Name,
Sum(tblIncomeSum.Commission) AS SumOfCommission
FROM tblLocation INNER JOIN tblIncomeSum ON tblLocation.LocationID =
tblIncomeSum.LocationID
WHERE (((Year(([tblIncomeSum].[Date])))=Year(Date())-1))
GROUP BY tblLocation.Name;
And it works fine when the form is opened. But after I do a update to a text
box I run the following code pretty much the same SQL just the year(Date())-1
is changed
Dim strSQL As String
strSQL = "SELECT IIf([SumOfCommission]>600,""X"","") AS Chk,
tblLocation.Name, Sum(tblIncomeSum.Commission) AS SumOfCommission " & _
"FROM tblLocation INNER JOIN tblIncomeSum ON
tblLocation.LocationID = tblIncomeSum.LocationID " & _
"WHERE (((Year(([tblIncomeSum].[Date]))) =
[forms]![frmTaxForm]![txtYear])) " & _
"GROUP BY tblLocation.Name;"
lstLocationName.RowSource = strSQL
The ListBox now is empty, Why? I came to the conclusion that my IIF
STATEMENT is to blame because it will work if I don't use the IIF STATEMENT.
What's up with that??
Any ideas??