Inner join problem or is it?

hello, this has to be said again, this is a great forum, although my search has not given me an answer to my problem so I have to post it.

I have a Inner Joing on an Access 2000 db through VB6. Using ADODB.MS JET.

It all works fine until I add a statement to the where clause:
This is OK
SQL = "SELECT Equipment.ComputerID, Equipment.Type, Equipment.Model, Reservation.ComputerID, Reservation.From_date, Reservation.To_date" _
& " FROM Equipment INNER JOIN Reservation ON (Equipment.ComputerID = Reservation.ComputerID)" _
& " WHERE ((Reservation.From_date NOT BETWEEN #" & startdate & "# AND #" & enddate & "#) AND (Reservation.To_date NOT BETWEEN #" & startdate & "# AND #" & enddate & "# ))OR Reservation.From_date Is Null

'But this:
SQL = "SELECT Equipment.ComputerID, Equipment.Type, Equipment.Model, Reservation.ComputerID, Reservation.From_date, Reservation.To_date" _
& " FROM Equipment INNER JOIN Reservation ON (Equipment.ComputerID = Reservation.ComputerID)" _
& " WHERE Equipment.Type=" & Eq & " AND ((Reservation.From_date NOT BETWEEN #" & startdate & "# AND #" & enddate & "#) AND (Reservation.To_date NOT BETWEEN #" & startdate & "# AND #" & enddate & "# ))OR Reservation.From_date Is Null "

->receives the error "No value given for one or more required parameters" I Assume it has to do with not finding the Equipment.Type but it is in the SELECT statement? So, anyone has an idea?
-----------
is the same for both Q

MsgBox SQL
Set Rs1 = conn.Execute(SQL)
' Rs1 refers to the Equipment table.

While Not Rs1.EOF
lslResults.AddItem Rs1.Fields("Equipment.ComputerID") & "--" & Rs1!Model
Rs1.MoveNext
Wend
Rs1.Close
----------------------------------

Hi Heita,

I think it will be helpful if you could show the sql with the passed parameters. In this case there would be a more clear picture of what is going wrong.

( in the immediate window just type ? sql and copy it to hte forum).


Regards,
Shurik.

If Equipment.Type is a text field, then you will need to wrap the passed value is single quotes.

& " WHERE Equipment.Type=" & Eq & " AND "...
'Should be
& " WHERE Equipment.Type = '" & Eq & "' AND "...

Enclode the field "Type" in square brackets - ".[Type]" That may help as Type is probably a reserved word.

Not sure if this is useful advice but have you tried your SQL in Access to see if the records you want come up?
Also Access seems to have a good query builder with a SQL view that you can practically copy/paste from.

Not sure if this is useful advice but have you tried your SQL in Access to see if the records you want come up?
Also Access seems to have a good query builder with a SQL view that you can practically copy/paste from.

SELECT Equipment.ComputerID, Equipment.Type, Equipment.Model, Reservation.ComputerID, Reservation.From_date, Reservation.To_date FROM Equipment INNER JOIN Reservation ON (Equipment.ComputerID = Reservation.ComputerID) WHERE Equipment.Type=Laptop AND((Reservation.From_date NOT BETWEEN #2003-01-01# AND #2003-01-30#) AND (Reservation.To_date NOT BETWEEN #2003-01-01# AND #2003-01-30# ))OR Reservation.From_date Is Null




Hi Heita,

I think it will be helpful if you could show the sql with the passed parameters. In this case there would be a more clear picture of what is going wrong.

( in the immediate window just type ? sql and copy it to hte forum).


Regards,
Shurik.

->I have tried in Access the same type of Q and it seems as if it does not interpret the "Type" correctly because if I try some other types that have not been reserved then it will still show equipment that is of other type, ie, search for Mobile and the result is still laptops? the same in Access and VB.

-> has to do with the OR statements, have to fiddle around with that.

Does it have to look like this:
enddate & "# ))OR Reservation.From_date

Or this one:
enddate & "# )) OR Reservation.From_date

please, have a look att this part of the sql

...& " WHERE Equipment.Type=" & Eq & " ...

if Type is a text field , isn't it supposed to be?:

...& " WHERE Equipment.Type='" & Eq & "' ...

Try Following Without any change.


SQL = "SELECT Equipment.ComputerID, Equipment.Type, Equipment.Model, Reservation.ComputerID, Reservation.From_date, Reservation.To_date" _
& " FROM Equipment INNER JOIN Reservation ON (Equipment.ComputerID = Reservation.ComputerID)" _
& " WHERE Equipment.[Type]='" & Eq & "' AND ((Reservation.From_date NOT BETWEEN #" & startdate & "# AND #" & enddate & "#) OR (Reservation.To_date NOT BETWEEN #" & startdate & "# AND #" & enddate & "# )) OR Reservation.From_date Is Null "


regards, :rolleyes:

I havea change the Q a bit to use OUTER JOIN to get the NULLvalues, instead of using OR but that does not seem to wort either. And change so that SQL sees the string of Type correctly.



SQL = "SELECT Equipment.ComputerID, Equipment.Type, Equipment.Model, Reservation.ComputerID, Reservation.From_date, Reservation.To_date" _
& " FROM Equipment LEFT OUTER JOIN Reservation ON (Equipment.ComputerID = Reservation.ComputerID)" _
& " WHERE Equipment.[Type]='" & Eq & "' AND((Reservation.From_date NOT BETWEEN #" & startdate & "# AND #" & enddate & "#) AND (Reservation.To_date NOT BETWEEN #" & startdate & "# AND #" & enddate & "# ))"

Did you get what you want ?

The first problem with the equipment.Type is solved BUT now I have a problem with the OR statment since it always shows the Null values regardless what equipemnt since OR is always true. SO tried the LEFT OUTER JOIN instead but this gave not the records with the NULL values ( see above post).
-> BELOW wokrs just fine, thanx all for the help!


SQL = "SELECT Equipment.ComputerID, Equipment.Type, Equipment.Model, Reservation.ComputerID, Reservation.From_date, Reservation.To_date" _
& " FROM Equipment LEFT JOIN Reservation ON (Equipment.ComputerID = Reservation.ComputerID)" _
& " WHERE Equipment.[Type]='" & Eq & "' AND((Reservation.From_date NOT BETWEEN #" & startdate & "# AND #" & enddate & "#) AND (Reservation.To_date NOT BETWEEN #" & startdate & "# AND #" & enddate & "# )OR Reservation.From_date Is Null)"