Populate list box column

  • Thread starter Thread starter Bird Byte
  • Start date Start date
B

Bird Byte

I have a listbox with 3 columns that populates when the form opens. I'd like
the 3rd column to populate depending on the results of an if then statement,
but I'm having syntax trouble.
Here's the gist of what I'm trying to accomplish:

If table1.field5 = "dog" then
list box column 3 = table1.field2
else if table1.field5 = "cat" then
list box column 3 = table1.field3
end if

Thanks for any help.
 
I have a listbox with 3 columns that populates when the form opens. I'd like
the 3rd column to populate depending on the results of an if then statement,
but I'm having syntax trouble.
Here's the gist of what I'm trying to accomplish:

If table1.field5 = "dog" then
list box column 3 = table1.field2
else if table1.field5 = "cat" then
list box column 3 = table1.field3
end if

Thanks for any help.
Click to expand...

It's difficult for us to see your database.
It would have been helpful had you posted your current list box
rowsource, otherwise we can only guess.

Are Dog or Cat the only possible values in Field5?
If yes then set the list box rowsource to something like this:

Select table1.[FieldA],table1.[FieldB],IIf(Table1.[Field5] =
"dog",[Field2],[Field3]) From table1;

If there may be other possible values, then something like this:

Select table1.[FieldA],table1.[FieldB],IIf(Table1.[Field5] =
"dog",[Field2],IIf(table1.[Field5] = "Cat",[Field3],"Neither")) From
table1;
 
Thanks for the help. Yes, there are other values (currently 5) for field5.
The value for column one is table1.field1, column 2 is table1.field2 and
column 3 actually comes from table2, not table1 as mentioned in the original
post.

The rowsource is currently a sql statement:
stSQL = "SELECT table1.field1, table1.field2 & ', ' + table1.field3 AS
Member, table2.field1, table2.field2, table2.field3, table2.field4,
table2.field5, table2.Field6 FROM table1 INNER JOIN table2 ON table1.field1 =
table2.field1;"

Sorry for not including enough info the first go around. The if then else
statement would look more like:
If table2.field6 = "dog" then
list box column 3 = table2.field2
else if table2.field5 = "cat" then
list box column 3 = table2.field3
else if table2.field5 = "monkey" then
list box column3 = table2.field4
.....
.....
.....
end if

So... if I'm thinking right here - I can inject the current sql statement
with the iif function as you mentioned below?

stSQL = "SELECT table1.field1, table1.field2 & ', ' + table1.field3 AS
Member, table2.field1, iif(table2.[field2]="dog",
[field2]iif(table2.[field3]="cat", [field3], iif(table2.[field4]="monkey",
[field4],iif(table2.field5]="lemur", [field5], "Neither" )))) FROM table1
INNER JOIN table2 ON table1.field1 = table2.field1;"

Am I thinking correctly here?
Thanks again for the help.


fredg said:
I have a listbox with 3 columns that populates when the form opens. I'd like
the 3rd column to populate depending on the results of an if then statement,
but I'm having syntax trouble.
Here's the gist of what I'm trying to accomplish:

If table1.field5 = "dog" then
list box column 3 = table1.field2
else if table1.field5 = "cat" then
list box column 3 = table1.field3
end if

Thanks for any help.
Click to expand...

It's difficult for us to see your database.
It would have been helpful had you posted your current list box
rowsource, otherwise we can only guess.

Are Dog or Cat the only possible values in Field5?
If yes then set the list box rowsource to something like this:

Select table1.[FieldA],table1.[FieldB],IIf(Table1.[Field5] =
"dog",[Field2],[Field3]) From table1;

If there may be other possible values, then something like this:

Select table1.[FieldA],table1.[FieldB],IIf(Table1.[Field5] =
"dog",[Field2],IIf(table1.[Field5] = "Cat",[Field3],"Neither")) From
table1;
Click to expand...
 
Back
Top