Problem querying for divetype

[scubal]

There is a problem querying DL6.0 for divetype due to the way divetype IDs are stored.
One dive can be in multiple catagories - for example "teaching" and "deep".
If multiple types are selected than they are concatenated into a string separated by commas.
Hoever this can cause queries when searching for specific types of dive.
For example:
divetype=6=cave
divetype=26=stage

If I search for divetype = cave(6) then it *only* finds the records where divetype=6.
It doesnt find the records where cave is in the string eg "14,6"

If I change the search to be divetype contains cave(6) then it finds records where the divetype contains 6 in any position - for example 16 or 26 or 6. Which is also wrong

How do you query from the divetype containing 6 or ,6 - so that if will find only the cave dives (and all of them).
I think it needs a tweak to the conversion behind the scenes from the name of the type to the code, to ensure it includes codes regardless of which position they appear in?

Hope that makes sense.

[scubal]

To further clarify this is what I think the query should be to ensure that you find all occassions where '6' occurs in the string but not 16 or 26 etc:

SELECT Logbook.Number, Logbook.Divetype, Logbook.SupplyType
FROM Logbook
WHERE (((Logbook.Divetype) Like "6,*")) OR (((Logbook.Divetype)="6")) OR (((Logbook.Divetype) Like "*,6"));

[divinglog]

Yes, I'm aware of this problem and that's the reason the dive type dives can only be reliable seen in the tree browser. It will analyze each dive separately by splitting the strings. Yeah, it was a bad design decision. "UsedEquip" has the same problem.

SELECT Logbook.Number, Logbook.Divetype, Logbook.SupplyType
FROM Logbook
WHERE (((Logbook.Divetype) Like "6,*")) OR (((Logbook.Divetype)="6")) OR (((Logbook.Divetype) Like "*,6"));

Hmm, great idea. Have you tried it to see if it works? I guess it will still list 16 and 26.

[scubal]

OK I have done some more testing and modified the query string to look for the four possible conditions:
Divetype is 6
Divetype starts with 6 and has other values
Divetype ends with 6 and has other values
Divetype has 6 in the middle of the values.
Which means it becomes:

SELECT Logbook.Number, Logbook.Divetype, Logbook.SupplyType
FROM Logbook
WHERE (((Logbook.Divetype)="6")) OR (((Logbook.Divetype) Like "*,6")) OR (((Logbook.Divetype) Like "6,*")) OR (((Logbook.Divetype) Like "*,6,*"));

tested it and it doesnt find 16
-------------------------------------------------------------------------------------------------------------------------
It can also be done (rather more neatly IMHO) as a VBA function (searchbyDelimiter) that turns a string into an array and then searches it:

SELECT Logbook.Number, Logbook.Divetype, Logbook.SupplyType
FROM Logbook
WHERE (((IIf(IsNull([DiveType]),False,SearchByDelimeter([DiveType],",","6")))=True));

------------------------------------------------------------------------------------------------------------------------
Public Function SearchByDelimeter(MyString As String, Delimiter As String, MySearchValue As String)
'Create variables
Dim MyArray() As String, N As Integer, Found As Boolean

Found = False

'Use Split function to divide up the component parts of the string
If Not (IsNull(MyString)) Then
MyArray = Split(MyString, Delimiter, -1, vbTextCompare)

'iterate through the array
For N = 0 To UBound(MyArray)
If MyArray(N) = MySearchValue Then
Found = True
Exit For
End If
Next N
End If
SearchByDelimeter = Found
End Function