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_Event ON
tblEvent.lngEventID = tblLINKVenue_Event.lngEventID) ON
tblVenue.lngVenueID = tblLINKVenue_Event.lngVenueID) ON
tblEventType.lngEventTypeID = tblEvent.lngEventTypeID WHERE
(((tblVenue.Region)='"&strRegion&"'))"

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_Event ON
tblEvent.lngEventID = tblLINKVenue_Event.lngEventID) ON
tblVenue.lngVenueID = tblLINKVenue_Event.lngVenueID) ON
tblEventType.lngEventTypeID = tblEvent.lngEventTypeID"

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

--
Wayne Morgan
MS Access MVP
"Neil H" <ne**********@ntlworld.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_Event ON
tblEvent.lngEventID = tblLINKVenue_Event.lngEventID) ON
tblVenue.lngVenueID = tblLINKVenue_Event.lngVenueID) ON
tblEventType.lngEventTypeID = tblEvent.lngEventTypeID WHERE
(((tblVenue.Region)='"&strRegion&"'))"

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!