SQL QUERY FOR LISTBOX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL QUERY FOR LISTBOX

Author

Topic

amitbadgi
Starting Member

29 Posts

Posted - 2005-07-25 : 17:55:56

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.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-25 : 19:22:20

This is not an Access forum as such, but here goes:
It's best to build your query dynamically in Access in these circumstances. You have more control and you're less at risk of Access pulling a fast one on you.

dim sql as String
sql = "SELECT fields....." _
    & "  FROM tables ...."
IF Me.lstCity.Text <> "" Then
    sql = sql & " WHERE City = '" & Me.lstCity.Text & "'"
End If

Hopefully this gives you the idea.

HTH,

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources