data dependent format in listbox 1

[CaptainBob007]

I'm working on a search form, and am trying to figure out how to format the data to populate a listbox the way I want.

Let's say I have a view that selects 4 fields: OwnID, OwnLast, OwnFirst, and Business.

I want to populate the list box with 2 fields, OwnID (which will not be visible), and Name. I'm trying to do this in a VBA statement. It works in simple form (ie when I dont try the IIF statement below). I'm trying to figure out how to make an equivalent of the below statement that works.

Me.listbox.RowSource = "SELECT OwnID, 
IIF([Business]=False,[OwnLast]+', '+[OwnFirst],[OwnLast]) As Name
from myView
WHERE [OwnLast] LIKE '" & Me.LastName & "%'"

But it doesnt work, or I'm not doing it right, or (probably) both. Like I said if I replace the "IIF" statement with "[OwnLast]", it works perfectly, but I really need it to change depending on the Business field.

Would it be better to put this statement in the View (as another column perhaps) or in the Row Source statement of the list box? Is there anything noticably wrong with that statement?

Any help or advice would be greatly appreciated.

~Bob

 

[donutman]

SQL doesn't have the IIF(expression, then, else) construct. You have to use the equivalent

CASE WHEN expression THEN [OwnFirst] ELSE [OwnLast] END AS SomeName
[green]         --but I prefer[/green]
SomeName = CASE WHEN expression 
                THEN [OwnFirst] 
                ELSE [OwnLast] 
           END

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLBill]

Is your backend a Microsoft SQL Server database (which is what this forum is for) or a Microsoft Access database?

If it's Access or anything other than SQL Server, you need to post your query in the appropriate forum.

If it is SQL Server, then you need to use the correct syntax as Karl points out.

-SQLBill

 

[CaptainBob007]

Thanks Karl - that's exactly what I wanted! Have a STAR!

SQLBill - Yes I am using SQL Server backend. I've done Access for quite a while, but I'm trying to move up to SQL server now.

~Bob

 

[SQLBill]

With SQL Server comes the BOL. That's Books OnLine which is MS SQL Server's HELP. It's installed as part of the Client Tools and you can usually find it at Start>Programs>Microsoft SQL Server>Books OnLine.

It will help you with your Transact-SQL (SQL Server's programming language).

-SQLBill

 

[ESquared]

Using quotename(Me.LastName) is probably a good idea, as some last names have apostrophes: O'Brien

 

[chrissie1]

if you want to use access as frontend to sqlserver try using adp's or access project's they are made for this, and then in a couple of months go to .net or some other higher programming language

Christiaan Baes
Belgium

What a wonderfull world - Louis armstrong