Subquery Trouble

[ready4data]

I have a table(tblLicense) with a field called License
It has rows with License1 License2 License3 ... License15

My other table is called tblReservations
It has fields License, FromDate, ToDate
It is populated from a web app so the License field can have any combination of the selected licenses from tblLicense separated by commas (eg: License2, License8, License13)

I was trying to write a new query to exclude selecting licenses that are already being used for a date range.

The query I place in the Criteria section:

SELECT tblReservations.License FROM tblReservations WHERE (((tblReservations.FromDate)=#2/12/2017#) AND ((tblReservations.ToDate)=#2/14/2017#));

Runs fine on its own and returns the correct data which is:
License2, License8, License13

If I paste that in the Criteria of the new query as: Not In(License2, License8, License13)
They are excluded which is correct.

When I use that in the criteria section of the new query as a subquery, The query runs but those are not excluded.

Not Exists (SELECT tblReservations.License FROM tblReservations WHERE (((tblReservations.FromDate)=#2/12/2017#) AND ((tblReservations.ToDate)=#2/14/2017#));)

The full query is this:

SELECT tblLicense.License
FROM tblLicense
WHERE ((Not (tblLicense.License)=Exists (SELECT tblReservations.License FROM tblReservations WHERE (((tblReservations.FromDate)=#2/12/2017#) AND ((tblReservations.ToDate)=#2/14/2017#));)));

Can anyone shed some light on this? What am I doing wrong.
Thanks,
Scott

 

[Beetle]

You were on the right track with the Not In clause, so i'm not sure why you switched to using the Not Exists clause. The reason that won't work is because it is going to look for a License that matches the literal string "License 2, License 8, License 13". No such License exists in your License table. Stick with the Not In clause;

SELECT License
FROM tblLicense
WHERE License Not IN
(SELECT tblReservations.License FROM tblReservations WHERE tblReservations.FromDate=#2/12/2017# AND tblReservations.ToDate=#2/14/2017#);

 

[sneuberg]

I suggest trying it like this

SELECT tblLicense.License
FROM tblLicense
WHERE tblLicense.License Not In(SELECT tblReservations.License FROM tblReservations WHERE tblReservations.FromDate =#2/12/2017#  AND tblReservations.ToDate = #2/14/2017#);

 

[ready4data]

Thank you both sneuberg and Beetle but neither suggestion worked.
When I Use the actual text in the criteria Not In(License2, License8, License13)
and move out of that field, it gets replaced with Not In("License2", "License8", "License13"). It is automatically adding quotes and runs correctly.
I think Beetle is correct about the literal string but how do I overcome that in the subquery

Scott

 

[sneuberg]

You could try passing the output of the subquery to a function like below that quotes the items in the comma separated list.

Public Function QuoteCSV(InString As Variant) As Variant

Dim LArray() As String
Dim i As Long
LArray = Split(InString, ",")
For i = 0 To UBound(LArray)
    QuoteCSV = QuoteCSV & """" & LArray(i) & ""","
Next i
'remove trailing comma
If Len(guotecsv) > 0 Then
    QuoteCSV = Left(QuoteCSV, Len(QuoteCSV) - 1)
End If

End Function

So after you put this function in a standard module the query would be:

SELECT tblLicense.License
FROM tblLicense
WHERE tblLicense.License Not In QuoteCSV( (SELECT tblReservations.License FROM tblReservations WHERE tblReservations.FromDate =#2/12/2017#  AND tblReservations.ToDate = #2/14/2017#));

This might need some fine tuning. If you upload you database I see if I can get it working. I did test the function and for and input string of

license,license2

it produces

"license","license2"

 

[sneuberg]

On second thought maybe

SELECT tblLicense.License
FROM tblLicense
WHERE tblLicense.License Not In ( QuoteCSV( (SELECT tblReservations.License FROM tblReservations WHERE tblReservations.FromDate =#2/12/2017#  AND tblReservations.ToDate = #2/14/2017#)));

 

[ready4data]

Thank you for the attempt. I can't use functions since this will be asp based. I'm looking into alternate ways to what I need.

Scott