Re: SQL Query for listbox

Hi,


WHERE ... AND iif(FORMS!formName!ComboBox='all', true, City =
FORMS!formName!ComboBox )


So if the combo box display all , the iif returns true, for any
record. Otherwise, the iif return true or false accordingly to the value
under the field City being equal or not to the one in the combo box. You can
also use:



WHERE ... AND (FORMS!formName!ComboBox='all' OR City =
FORMS!formName!ComboBox )


which does the same, but this time, relaying on the property of the OR
Boolean operator.



Hoping it may help,
Vanderghast, Access MVP


<amitbadgi@xxxxxxxxx> wrote in message
news:1122328685.513014.204600@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Hi guys I have created a form in access which has 4 options,
> BID, Telephone, address and License number, now if a employee enters
> any one of the fields and clicks search, the related record is pulled
> out, and there are 2 differnet cities which have 2 tables each. Here is
> teh query that I have written
>
> SELECT OneWorld.bus_id, OneWorld.bus_nam, OneWorld.bus_add,
> OneWorld.city, OneWorld.state, OneWorld.zip, OneWorld.phone,
> OneWorld.license
> FROM [Select Distinct dbo_businessNC.bus_id as
> bus_id,dbo_businessNC.bus_name as bus_nam,dbo_businessNC.adrs1 as
> bus_add,dbo_businessNC.city as city,dbo_businessNC.state as
> state,dbo_businessNC.zip as zip, dbo_businessNC.phone as phone,
> dbo_occhistoryNC.license as license from dbo_businessNC,
> dbo_occhistoryNC WHERE ( dbo_businessNC.bus_id=dbo_occhistoryNC.bus_id)
> UNION ALL Select bus_id, bus_name as bus_nam, bus_add, city, state,
> zip, phone, license from Hawaccsumm
> ]. AS OneWorld
> WHERE (((OneWorld.bus_id) LIKE "*" &[Forms].[Form1].[Text2]& "*")) AND
> (((OneWorld.bus_nam) LIKE "*" &[Forms].[Form1].[Text5]& "*")) AND
> (((OneWorld.bus_add) LIKE"*" & [Forms].[Form1].[Text8]& "*")) AND
> (((OneWorld.phone) LIKE "*" &[Forms].[Form1].[Text10]& "*")) AND
> (((OneWorld.zip) LIKE "*" &[Forms].[Form1].[Text15]& "*"));
>
> Now I want to include a listbox with 3 options
> city1, city2, all
> Hence if the user selects city1 and then enters a field and clicks
> search, then only city1 shld be searched and teh related record shld be
> pulled out, and the same for city 2, and for ALL, all teh cities shld
> be searched, which its doign as of now. Hence i wanted to know how do i
> proceed. Thanks.
>


.