Anyone know whats wrong with this?

[Mike M]

hi all,

I have a form with a text box and a checkbox. If the text box is empty the
page retreives records based on this text and if the check box is checked it
does the same. But im having trouble retreiveing records based on the text
and the checkbox at the same time. PLease see my code below. Am i
contructing the code right?


If strSearch <= "" and Request.QueryString("Upcoming") = "on" Then
strSQL = "SELECT * FROM tblTours WHERE fldUpcoming = 1 ORDER BY
fldGigDateYear, fldGigDateDay, fldGigDateMonth, fldVenue ASC"
objRS.Open strSQL, objConn, 3, 1, 1
Else
If strSearch > "" and Request.QueryString("Upcoming") = "on" Then
strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch & "%'
OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" & strSearch &
"%' AND fldUpcoming = 1 ORDER BY fldGigDateYear, fldGigDateDay,
fldGigDateMonth, fldVenue ASC"
'Response.Write strSQL
objRS.Open strSQL, objConn, 3, 1, 1
Else
strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch &
"%' OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" & strSearch
& "%' ORDER BY fldGigDateYear, fldGigDateDay, fldGigDateMonth, fldVenue ASC"
objRS.Open strSQL, objConn, 3, 1, 1
End If
End If

[Adrian Forbes [ASP MVP]]

Are you having a problem with your SQL string, or is it the programme flow
you are having a problem with? For what it's worth I'd try

if strSearch = "" and Request.QueryString("Upcomping") <> "" then
' search is empty, upcoming is checked
elseif strSearch <> "" and Request.QueryString("Upcomping") <> "" then
' search has data, upcoming is checked
else
' search might have data, it might be empty but upcoming is unchecked
end if

Note that your three conditions might not be exactly what you intended.

"Mike M" <mike@mack1.karoo.co.uk> wrote in message
news:D5ycnc8C7MYVObLdSa8jmA@karoo.co.uk...

> hi all,
>
> I have a form with a text box and a checkbox. If the text box is empty the
> page retreives records based on this text and if the check box is checked

it

> does the same. But im having trouble retreiveing records based on the text
> and the checkbox at the same time. PLease see my code below. Am i
> contructing the code right?
>
>
> If strSearch <= "" and Request.QueryString("Upcoming") = "on" Then
> strSQL = "SELECT * FROM tblTours WHERE fldUpcoming = 1 ORDER BY
> fldGigDateYear, fldGigDateDay, fldGigDateMonth, fldVenue ASC"
> objRS.Open strSQL, objConn, 3, 1, 1
> Else
> If strSearch > "" and Request.QueryString("Upcoming") = "on" Then
> strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch &

"%'

> OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" & strSearch &
> "%' AND fldUpcoming = 1 ORDER BY fldGigDateYear, fldGigDateDay,
> fldGigDateMonth, fldVenue ASC"
> 'Response.Write strSQL
> objRS.Open strSQL, objConn, 3, 1, 1
> Else
> strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch &
> "%' OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" &

strSearch

> & "%' ORDER BY fldGigDateYear, fldGigDateDay, fldGigDateMonth, fldVenue

ASC"

> objRS.Open strSQL, objConn, 3, 1, 1
> End If
> End If
>
>
>

[Mike M]

"Adrian Forbes [ASP MVP]" <sorry@noemail.zzz> wrote in message
news:e5t#nPO9DHA.2308@TK2MSFTNGP11.phx.gbl...

> Are you having a problem with your SQL string, or is it the programme flow
> you are having a problem with? For what it's worth I'd try
>
> if strSearch = "" and Request.QueryString("Upcomping") <> "" then
> ' search is empty, upcoming is checked
> elseif strSearch <> "" and Request.QueryString("Upcomping") <> "" then
> ' search has data, upcoming is checked
> else
> ' search might have data, it might be empty but upcoming is unchecked
> end if
>
> Note that your three conditions might not be exactly what you intended.
>
> "Mike M" <mike@mack1.karoo.co.uk> wrote in message
> news:D5ycnc8C7MYVObLdSa8jmA@karoo.co.uk...

> > hi all,
> >
> > I have a form with a text box and a checkbox. If the text box is empty

the

> > page retreives records based on this text and if the check box is

checked

> it

> > does the same. But im having trouble retreiveing records based on the

text

> > and the checkbox at the same time. PLease see my code below. Am i
> > contructing the code right?
> >
> >
> > If strSearch <= "" and Request.QueryString("Upcoming") = "on" Then
> > strSQL = "SELECT * FROM tblTours WHERE fldUpcoming = 1 ORDER BY
> > fldGigDateYear, fldGigDateDay, fldGigDateMonth, fldVenue ASC"
> > objRS.Open strSQL, objConn, 3, 1, 1
> > Else
> > If strSearch > "" and Request.QueryString("Upcoming") = "on" Then
> > strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch &

> "%'

> > OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" & strSearch

&

> > "%' AND fldUpcoming = 1 ORDER BY fldGigDateYear, fldGigDateDay,
> > fldGigDateMonth, fldVenue ASC"
> > 'Response.Write strSQL
> > objRS.Open strSQL, objConn, 3, 1, 1
> > Else
> > strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch

&

> > "%' OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" &

> strSearch

> > & "%' ORDER BY fldGigDateYear, fldGigDateDay, fldGigDateMonth, fldVenue

> ASC"

> > objRS.Open strSQL, objConn, 3, 1, 1
> > End If
> > End If
> >
> >
> >

>
>

Hi,

The section im having trouble with is the :

If strSearch > "" and Request.QueryString("Upcoming") = "on" Then
strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch &
"%' OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" & strSearch
& "%' AND fldUpcoming = 1 ORDER BY fldGigDateYear, fldGigDateDay,
fldGigDateMonth, fldVenue ASC"

It works fine in showing any record that contains data that i entered to
search but not the ones that are marked as upcoming.

such as If i enter aldershot without the upcoming check box ticked i return
4 records but if i just tick upcoming and do not type any text i get back 3
records (which is correct) but in theory i should retreive 3 records if i
type Aldershot and tick the box? I'll try your changes tomorow, right now i
need sleep. Thanks

Mike

[Adrian Forbes [ASP MVP]]

> If strSearch > "" and Request.QueryString("Upcoming") = "on" Then

> strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch &
> "%' OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" &

strSearch

> & "%' AND fldUpcoming = 1 ORDER BY fldGigDateYear, fldGigDateDay,
> fldGigDateMonth, fldVenue ASC"
>
> It works fine in showing any record that contains data that i entered to
> search but not the ones that are marked as upcoming.
>
> such as If i enter aldershot without the upcoming check box ticked i

return

> 4 records but if i just tick upcoming and do not type any text i get back

3

> records (which is correct) but in theory i should retreive 3 records if i
> type Aldershot and tick the box? I'll try your changes tomorow, right now

i

> need sleep. Thanks

Maybe it is clever enough to know that Aldershot can't possibly be upcoming
in anything :)

Try and group your OR statements as one unit and your AND as seperate

WHERE (fldVenue LIKE... OR fldTown LIKE...OR fldTour LIKE ...) AND
fldUpcoming = 1

BTW is dlfUpcoming a bit or yes/no? If so you should use "= 0" for false
and "<> 0" for true. Access treats a bit as 0/1 and SQL Server 0/-1

[Mike M]

"Adrian Forbes [ASP MVP]" <sorry@noemail.zzz> wrote in message
news:uc#NarO9DHA.2472@TK2MSFTNGP10.phx.gbl...

> > If strSearch > "" and Request.QueryString("Upcoming") = "on" Then
> > strSQL = "SELECT * FROM tblTours WHERE fldVenue LIKE '%" & strSearch &
> > "%' OR fldTown LIKE '%" & strSearch & "%' OR fldTour LIKE '%" &

> strSearch

> > & "%' AND fldUpcoming = 1 ORDER BY fldGigDateYear, fldGigDateDay,
> > fldGigDateMonth, fldVenue ASC"
> >
> > It works fine in showing any record that contains data that i entered to
> > search but not the ones that are marked as upcoming.
> >
> > such as If i enter aldershot without the upcoming check box ticked i

> return

> > 4 records but if i just tick upcoming and do not type any text i get

back

> 3

> > records (which is correct) but in theory i should retreive 3 records if

i

> > type Aldershot and tick the box? I'll try your changes tomorow, right

now

> i

> > need sleep. Thanks

>
> Maybe it is clever enough to know that Aldershot can't possibly be

upcoming

> in anything :)
>
> Try and group your OR statements as one unit and your AND as seperate
>
> WHERE (fldVenue LIKE... OR fldTown LIKE...OR fldTour LIKE ...) AND
> fldUpcoming = 1
>
> BTW is dlfUpcoming a bit or yes/no? If so you should use "= 0" for false
> and "<> 0" for true. Access treats a bit as 0/1 and SQL Server 0/-1
>
>

Its a number field with a Field size of "long integer" Im using An access
database. Not upgraded to SQWL yet as i may me buying some hosting

[Adrian Forbes [ASP MVP]]

> > Try and group your OR statements as one unit and your AND as seperate

> >
> > WHERE (fldVenue LIKE... OR fldTown LIKE...OR fldTour LIKE ...) AND
> > fldUpcoming = 1
> >
> > BTW is dlfUpcoming a bit or yes/no? If so you should use "= 0" for

false

> > and "<> 0" for true. Access treats a bit as 0/1 and SQL Server 0/-1
> >
> >

> Its a number field with a Field size of "long integer" Im using An access
> database. Not upgraded to SQWL yet as i may me buying some hosting

Have you tried grouping the ORs in their own block yet?