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
