Select "All" Option

[Neil H]

Hi All

I am doing a multiple table and field database search, and my problem
lies in the options that a user has. In each field, the user can specify
a specific value or any value. I take each value from the form and store
them as variables which are passed onto another asp page. I then have an
SQL statement constructed which takes these variables as conditions and
returns matching records. If they choose a specific value for each
field, there is no problem but if they choose "All", I want to be able
to use the same SQL statement as well.

Heres a bit of code that works if strRegion is equal to a value stored
in the table, ie London, Scotland etc.

SELECT DISTINCT tblVenue.Area FROM tblEventType INNER JOIN (tblVenue
INNER JOIN (tblEvent INNER JOIN tblLINKVenue_Ev ent ON
tblEvent.lngEve ntID = tblLINKVenue_Ev ent.lngEventID) ON
tblVenue.lngVen ueID = tblLINKVenue_Ev ent.lngVenueID) ON
tblEventType.ln gEventTypeID = tblEvent.lngEve ntTypeID WHERE
(((tblVenue.Reg ion)='"&strRegi on&"'))"

How do i incorporate "All"?. If coice is "All" I was thinking about
making strRegion = London OR Wales OR Scotland OR etc etc etc. Is there
a quicker way of doing this?

Thanks Again


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Wayne Morgan]

The quickest way would probably be to concatenate the WHERE clause on.

strSQL = "SELECT DISTINCT tblVenue.Area FROM tblEventType INNER JOIN
(tblVenue
INNER JOIN (tblEvent INNER JOIN tblLINKVenue_Ev ent ON
tblEvent.lngEve ntID = tblLINKVenue_Ev ent.lngEventID) ON
tblVenue.lngVen ueID = tblLINKVenue_Ev ent.lngVenueID) ON
tblEventType.ln gEventTypeID = tblEvent.lngEve ntTypeID"

If MyValue = "All" Then
strSQL = strSQL & ";"
Else
strSQL = strSQL & " WHERE (((tblVenue.Reg ion)='" & strRegion & "'));"
End If

--
Wayne Morgan
MS Access MVP
"Neil H" <ne**********@n tlworld.com> wrote in message
news:41******** **************@ news.newsgroups .ws...

Hi All

I am doing a multiple table and field database search, and my problem
lies in the options that a user has. In each field, the user can specify
a specific value or any value. I take each value from the form and store
them as variables which are passed onto another asp page. I then have an
SQL statement constructed which takes these variables as conditions and
returns matching records. If they choose a specific value for each
field, there is no problem but if they choose "All", I want to be able
to use the same SQL statement as well.

Heres a bit of code that works if strRegion is equal to a value stored
in the table, ie London, Scotland etc.

SELECT DISTINCT tblVenue.Area FROM tblEventType INNER JOIN (tblVenue
INNER JOIN (tblEvent INNER JOIN tblLINKVenue_Ev ent ON
tblEvent.lngEve ntID = tblLINKVenue_Ev ent.lngEventID) ON
tblVenue.lngVen ueID = tblLINKVenue_Ev ent.lngVenueID) ON
tblEventType.ln gEventTypeID = tblEvent.lngEve ntTypeID WHERE
(((tblVenue.Reg ion)='"&strRegi on&"'))"

How do i incorporate "All"?. If coice is "All" I was thinking about
making strRegion = London OR Wales OR Scotland OR etc etc etc. Is there
a quicker way of doing this?

Thanks Again


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!