By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,414 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,414 IT Pros & Developers. It's quick & easy.

Help with OR and Nulls in Query

P: n/a
PST
Here's a problem I'm trying to deal with:

I'm working on a Frontpage 2000 website for a boat handicapping
system, built in Access 97.

What I'm trying to accomplish is:

The user enters a boat name in a text field OR a sail number in a text
field, and gets the desired boat record back (an exact match).

The problem is the tricky combination of a query with OR in the WHERE
clause, and those pesky nulls.

The Boats query looks something like this:
fIDBoat ftxBoatName ftxSailNum flgRating
1 Yuletime 74 32
2 <null> 1130 66
3 Namba <null> 78
.....

As you can see, some of the records have nulls in the two fields I'm
trying to filter by. If, for example, I enter 1130 in the Sail Number
field and leave the Boat Name field blank, I'd get record 2 and record
3 as well.

I think the problem is the query itself. Should I leave the nulls as
nulls, or covert them to a space (" ") or something else? Or should I
do something with what the user enters and get rid of the nulls there?

Bear in mind this has to go into Frontpage, and I'm stuck with the
options that program gives me. I can't custom-design a SQL string for
the ASP page.

Thanks in advance

Please respond to ptupper at intergate dot ca
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Sounds Dodgy to me.
Certainly in races I have dealt with there always seem to me a couple of
pairs of boats with the same name, so without a sail number they can't be
distinguished. Equally, if you dont enforce a name or a number you can get a
boat with no information other than her handicap.

I use
SELECT DISTINCT Boat.BoatName AS Boat, jnBoatRaceMaster.RacingNo,
ExtractNumber([RacingNo]) AS [No], jnBoatRaceMaster.BoatID FROM Boat INNER
JOIN jnBoatRaceMaster ON Boat.BoatID = jnBoatRaceMaster.BoatID WHERE
jnBoatRaceMaster.RaceMasterID = screen.activeform!RacemasterID
UNION SELECT DISTINCT jnBoatRaceMaster.RacingNo AS Boat,
ExtractNumber([RacingNo]) AS [No], Boat.BoatName, jnBoatRaceMaster.BoatID
FROM Boat INNER JOIN jnBoatRaceMaster ON Boat.BoatID =
jnBoatRaceMaster.BoatID WHERE jnBoatRaceMaster.RaceMasterID =
screen.activeform!RacemasterID
UNION SELECT DISTINCT ExtractNumber([RacingNo]) AS [No],
jnBoatRaceMaster.RacingNo AS Boat, Boat.BoatName, jnBoatRaceMaster.BoatID
FROM Boat INNER JOIN jnBoatRaceMaster ON Boat.BoatID =
jnBoatRaceMaster.BoatID WHERE jnBoatRaceMaster.RaceMasterID =
screen.activeform!RacemasterID ORDER BY Boat DESC;

The ExtractNumber strips country letters from the sail number ( We use GBR
and I think you use US) so that you can key in just the numerical part of
the sail number

Function ExtractNumber(Text As String)

Dim i As Integer
Dim Num As String

For i = 1 To Len(Text)
If IsNumeric(Mid(Text, i, 1)) Then
Num = Num & Mid(Text, i, 1)
End If
Next
If Num = "" Then
ExtractNumber = ""
Exit Function
End If

ExtractNumber = Num

End Function

Hope this helps

Phil
"PST" <ptupper at intergate dot ca> wrote in message
news:fv********************************@4ax.com...
Here's a problem I'm trying to deal with:

I'm working on a Frontpage 2000 website for a boat handicapping
system, built in Access 97.

What I'm trying to accomplish is:

The user enters a boat name in a text field OR a sail number in a text
field, and gets the desired boat record back (an exact match).

The problem is the tricky combination of a query with OR in the WHERE
clause, and those pesky nulls.

The Boats query looks something like this:
fIDBoat ftxBoatName ftxSailNum flgRating
1 Yuletime 74 32
2 <null> 1130 66
3 Namba <null> 78
....

As you can see, some of the records have nulls in the two fields I'm
trying to filter by. If, for example, I enter 1130 in the Sail Number
field and leave the Boat Name field blank, I'd get record 2 and record
3 as well.

I think the problem is the query itself. Should I leave the nulls as
nulls, or covert them to a space (" ") or something else? Or should I
do something with what the user enters and get rid of the nulls there?

Bear in mind this has to go into Frontpage, and I'm stuck with the
options that program gives me. I can't custom-design a SQL string for
the ASP page.

Thanks in advance

Please respond to ptupper at intergate dot ca

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.