Help with OR and Nulls in Query

  • Thread starter Thread starter PST
  • Start date Start date
P

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
 
Before constructing your sql string (for your query), you
could test for nulls in what was (or was not) entered.

E.g.
'***user entered a boatname
IF Len(userEnteredBoatName) > 0 Then

'**use the boatname for the criteria
SELECT boatname FROM boattable Where(boatname =
UserEnteredBoatName)

'****user entered a sail number
ELSE IF Len(userEnteredSailNumber) > 0 Then etc., etc...

'****user entered neither
ELSE (both are null) tell your user to enter one or the
other....

END IF
 
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
 
Back
Top