SLQ syntax error

[Sketchin]

Hi All, I have an SQL statement that keeps throwing a runtime 3075 error. I am sure I am missing something dumb, but if oyu wouldn't mind taking a look it would be much appreciated.

"SELECT (([tblReservations_1].[DateOutReq]>[tblReservations].[DateInReq]) Or " & _
            "([tblReservations_1].[DateInreq]<[tblReservations].[DateOutreq])) Or " & _
            "([tblReservation_Details].[BOMNumber]<>[tblReservation_Details_1].[BOMNumber]) Or " & _
            "([tblReservation_details].[ReservationID]=[tblReservation_Details_1].[ReservationID])  AS " & _
            "NoClash, tblReservation_details.BOMNumber, tblReservation_details_1.BOMNumber, " & _
            "tblReservations.DateOutReq, tblReservations_1.DateOutReq, tblReservations.DateInReq, " & _
            "tblReservations_1.DateInReq, tblReservation_details_1.ReservationID, tblReservation_details.ReservationID, " & _
            "Count(tblReservations.[reservationID]) AS [Count]" & _
            "FROM tblReservations AS tblReservations_1 INNER JOIN tblReservation_details AS " & _
            "tblReservation_details_1 ON tblReservations_1.ReservationID = tblReservation_details_1.ReservationID, " & _
            "tblReservations INNER JOIN tblReservation_details ON tblReservations.ReservationID = tblReservation_details.ReservationID" & _
            "Group BY [tblReservations_1].[DateOutReq] > [tblReservations].[DateInReq] Or " & _
            "[tblReservations_1].[DateInReq] < [tblReservations].[DateOutReq] Or " & _
            "[tblReservation_details].[BomNumber] <> [tblReservation_Details_1].[BomNumber] Or " & _
            "[tblReservation_details].[ReservationID] = [tblReservation_Details_1].[ReservationID], " & _
            "tblReservation_details.BomNumber, tblReservation_Details_1.BomNumber, tblReservations.DateOutReq, " & _
            "tblReservations_1.DateOutReq, tblReservations.DateInReq, tblReservations_1.DateInReq, " & _
            "tblReservation_Details_1.ReservationID, tblReservation_details.ReservationID, tblReservations.ReservationID " & _
            "HAVING  ((((([tblReservations_1].[DateOutReq]>[tblReservations].[DateInReq]) Or " & _
            "([tblReservations_1].[DateInreq]<[tblReservations].[DateOutreq])) Or " & _
            "([tblReservation_Details].[BOMNumber]<>[tblReservation_Details_1].[BOMNumber]) Or " & _
            "([tblReservation_details].[ReservationID]=[tblReservation_Details_1].[ReservationID]))=False) AND " & _
            "((tblReservations.ReservationID) = [Forms]![frmReservations].[TxtReservationID]));"

 

[Beetle]

Well, just at first glance, you're missing a couple of spaces -

here (between [Count] and FROM);

 "Count(tblReservations.[reservationID]) AS [Count]" & _
 "FROM tblReservations AS tblReservations_1 INNER JOIN tblReservation_details AS " & _

and here (between ReservationID and Group By);

 "tblReservations INNER JOIN tblReservation_details ON tblReservations.ReservationID = tblReservation_details.ReservationID" & _
 "Group BY [tblReservations_1].[DateOutReq] > [tblReservations].[DateInReq] Or " & _

Also, the reference to the form control should be outside the string when writing an SQL statement in code;

"((tblReservations.ReservationID) = " & [Forms]![frmReservations].[TxtReservationID] & "));"

 

[Sketchin]

Alright that worked, but now I have a new error. Run time 2342 - a RUNSQL action requires an argument consisting of an SQL statement.

strSQL2 = " SELECT (([tblReservations_1].[DateOutReq]>[tblReservations].[DateInReq]) Or " & _
            " ([tblReservations_1].[DateInreq]<[tblReservations].[DateOutreq])) Or " & _
            "([tblReservation_Details].[BOMNumber]<>[tblReservation_Details_1].[BOMNumber]) Or " & _
            "([tblReservation_details].[ReservationID]=[tblReservation_Details_1].[ReservationID])  AS " & _
            "NoClash, tblReservation_details.BOMNumber, tblReservation_details_1.BOMNumber, " & _
            "tblReservations.DateOutReq, tblReservations_1.DateOutReq, tblReservations.DateInReq, " & _
            "tblReservations_1.DateInReq, tblReservation_details_1.ReservationID, tblReservation_details.ReservationID, " & _
            "Count (tblReservations.[reservationID]) AS [Count] " & _
            "FROM tblReservations AS tblReservations_1 INNER JOIN tblReservation_details AS " & _
            "tblReservation_details_1 ON tblReservations_1.ReservationID = tblReservation_details_1.ReservationID, " & _
            "tblReservations INNER JOIN tblReservation_details ON tblReservations.ReservationID = tblReservation_details.ReservationID " & _
            "Group BY [tblReservations_1].[DateOutReq] > [tblReservations].[DateInReq] Or " & _
            "[tblReservations_1].[DateInReq] < [tblReservations].[DateOutReq] Or " & _
            "[tblReservation_details].[BomNumber] <> [tblReservation_Details_1].[BomNumber] Or " & _
            "[tblReservation_details].[ReservationID] = [tblReservation_Details_1].[ReservationID], " & _
            "tblReservation_details.BomNumber, tblReservation_Details_1.BomNumber, tblReservations.DateOutReq, " & _
            "tblReservations_1.DateOutReq, tblReservations.DateInReq, tblReservations_1.DateInReq, " & _
            "tblReservation_Details_1.ReservationID, tblReservation_details.ReservationID, tblReservations.ReservationID " & _
            " HAVING  ((((([tblReservations_1].[DateOutReq]>[tblReservations].[DateInReq]) Or " & _
            "([tblReservations_1].[DateInreq]<[tblReservations].[DateOutreq])) Or " & _
            "([tblReservation_Details].[BOMNumber]<>[tblReservation_Details_1].[BOMNumber]) Or " & _
            "([tblReservation_details].[ReservationID]=[tblReservation_Details_1].[ReservationID]))=False) AND " & _
            "((tblReservations.ReservationID) = " & [Forms]![frmReservations].[TxtReservationID] & "));"
 
DoCmd.RunSQL (strSQL2)

To clarify, with this statement I just want ot know if its returning any records...if it is, there is an appointment clash. I know the logic is correct as it works when I manually run the query. My next step is to automate this into a message box that will tell me when I am double booking.

 

[Beetle]

You can't "run" a Select query. RunSQL (and it's equivalent CurrentDb.Execute) are for action queries (Update or Append queries). You could assign the SQL to a record set and then do your processing with the record set. Example;

With CurrentDb.OpenRecordset (strSQL2, dbOpenDynaset)
    Do something
End With

 

[Sketchin]

See, I knew I did something stupid. Seems to be partially working.

Thanks alot for your time!