VBA/SQL Logic problem editing a record in a form

[cyd44]

I finally got the query to accept as you have (think I missed off the final bracket). I have now got it to work in my database but this is beyond strange. I get a valid result for an invalid change?????
Your Code works perfectly on the small database but is not validating correctly in my database.
I am using your code as excact and am not getting any error but it is not validating like your example.
Code used is

strSQL = "[BookStartDate] = " & Format(dteDate, "\#mm\/dd\/yyyy\#") & " AND " & _
"[BookLocation] = '" & strRoomNo & "' AND " & _
"([BookTime] BETWEEN '" & Format(DateAdd("n", 1, dteStartTime), "Medium Time") & "' AND " & _
"'" & Format(DateAdd("n", -1, dteEndTime), "Medium Time") & "' OR " & _
"[BookEndTime] BETWEEN '" & Format(DateAdd("n", 1, dteStartTime), "Medium Time") & "' AND " & _
"'" & Format(DateAdd("n", -1, dteEndTime), "Medium Time") & "')" & _
IIf(Me.NewRecord, vbNullString, " AND " & "[BookID] <> " & BookRef)

The call to DCount is

Valid = (DCount("*", "qryEditBookings", strSQL))

Just in case it is a table problem I will empty my table and use my add booking proedure to create 3 bookings and then try to change, lets see what happens

 

[vbaInet]

You are using the wrong field names in the SQL statement. Remember the underscore?

 

[cyd44]

Ok thats worked but it complained about the underscore at first.? thats why I took it out.
I did change the date of a booking with a conflicting time and it still considered the change valid though?
Time changes appear to be working correctly but occasionally it considers an end time = start time of another booking valid. Not always but on occasion

This is not easy is it?

 

[vbaInet]

If it is occasional then it's down to your input.

It's not a difficult issue. Maybe you are finding it difficult

 

[cyd44]

I think you are right, its down to me.We have spent such a long time on this I think I was getting confused. I feel that the code does work and I am happy with that. Let us finally put this to bed and let me say a big thanks you once again for all your help.I would never have got this working without you.

OWE YOU A PINT I think

 

[vbaInet]

If you feel that some input isn't working as expected just tell me what the scenario is and I will take a look when I can.

Happy to help!

I await my pint or cyder (or is that cider)