Another SQL statement question....[NOT RESOLVED!!]

[epod69]

Hey everyone, I have a form which allows you to change the time of one of your events and I want to first check to see if there is an event already scheduled for the specified time.

Here is what I have so far:

    SQLstmt_cal = "SELECT Appointment.Date, OpenHouse.Date, Showing.Date, ToDo.Date FROM Appointment, OpenHouse, Showing, ToDo " & _
    "WHERE (Appointment.Date = " & "#" & dt_Date & "#" & " And Appointment.Time = " & "#" & dt_Time & "#" & ") Or " & _
    "(OpenHouse.Date = " & "#" & dt_Date & "#" & " And OpenHouse.Time = " & "#" & dt_Time & "#" & ") Or " & _
    "(Showing.Date = " & "#" & dt_Date & "#" & " And Showing.Time = " & "#" & dt_Time & "#" & ") Or " & _
    "(ToDo.Date = " & "#" & dt_Date & "#" & " And ToDo.Time = " & "#" & dt_Time & "#" & ")"
    
    Debug.Print SQLstmt_cal
    
    Set MyRecSet_cal = New ADODB.Recordset
    MyRecSet_cal.Open SQLstmt_cal, MyConn_cal, adOpenDynamic, adLockOptimistic, adCmdText
    
    If MyRecSet_cal.BOF = False Then GoTo RecordFound

Debug.Print = SELECT Appointment.Date, OpenHouse.Date, Showing.Date, ToDo.Date FROM Appointment, OpenHouse, Showing, ToDo WHERE (Appointment.Date = #4/14/2005# And Appointment.Time = #12:00 PM#) Or (OpenHouse.Date = #4/14/2005# And OpenHouse.Time = #12:00 PM#) Or (Showing.Date = #4/14/2005# And Showing.Time = #12:00 PM#) Or (ToDo.Date = #4/14/2005# And ToDo.Time = #12:00 PM#)

For some reason the statement doesnt detect when there is a record already existing.. can anyone help me out??

[RhinoBull]

1. your fields naming convetion isn't the best you can have - Date and Time are reserved words and in majority RDBMs are functions as well ... so you may want to reconcider ...

2. what database are you using - there could be different syntax involved.
3. how date and time fields are defined in the table?
4. what are the actual values in those fields ?

[Ecniv]

Hey everyone, I have a form which allows you to change the time of one of your events and I want to first check to see if there is an event already scheduled for the specified time.

Here is what I have so far:

    SQLstmt_cal = "SELECT Appointment.Date, OpenHouse.Date, Showing.Date, ToDo.Date FROM Appointment, OpenHouse, Showing, ToDo " & _
    "WHERE (Appointment.Date = " & "#" & dt_Date & "#" & " And Appointment.Time = " & "#" & dt_Time & "#" & ") Or " & _
    "(OpenHouse.Date = " & "#" & dt_Date & "#" & " And OpenHouse.Time = " & "#" & dt_Time & "#" & ") Or " & _
    "(Showing.Date = " & "#" & dt_Date & "#" & " And Showing.Time = " & "#" & dt_Time & "#" & ") Or " & _
    "(ToDo.Date = " & "#" & dt_Date & "#" & " And ToDo.Time = " & "#" & dt_Time & "#" & ")"
    
    Debug.Print SQLstmt_cal
    
    Set MyRecSet_cal = New ADODB.Recordset
    MyRecSet_cal.Open SQLstmt_cal, MyConn_cal, adOpenDynamic, adLockOptimistic, adCmdText
    
    If MyRecSet_cal.BOF = False Then GoTo RecordFound

Debug.Print = SELECT Appointment.Date, OpenHouse.Date, Showing.Date, ToDo.Date FROM Appointment, OpenHouse, Showing, ToDo WHERE (Appointment.Date = #4/14/2005# And Appointment.Time = #12:00 PM#) Or (OpenHouse.Date = #4/14/2005# And OpenHouse.Time = #12:00 PM#) Or (Showing.Date = #4/14/2005# And Showing.Time = #12:00 PM#) Or (ToDo.Date = #4/14/2005# And ToDo.Time = #12:00 PM#)

For some reason the statement doesnt detect when there is a record already existing.. can anyone help me out??

SELECT Appointment.Date, OpenHouse.Date, Showing.Date, ToDo.Date 
FROM Appointment, OpenHouse, Showing, ToDo 
WHERE 
(Appointment.Date = #4/14/2005# And Appointment.Time = #12:00 PM#) Or 
(OpenHouse.Date = #4/14/2005# And OpenHouse.Time = #12:00 PM#) Or 
(Showing.Date = #4/14/2005# And Showing.Time = #12:00 PM#) Or 
(ToDo.Date = #4/14/2005# And ToDo.Time = #12:00 PM#)

If you ran this on the db (access?) and see whether it returns the result you are looking for?
How are you storing the dates and times?? Possibly one has picked up the other half for some reason and is therefore being filtered out..

Example:
You store 4/14/05 the db holds 4/14/05 00:00:00
Same for the time, but I do not know what the date would be, however the point I'm trying to make is that when you do not specify a date with the time and visa versa the db guesses a date/time and then this would explain why you are not getting any records.

** I recommend you read up on normalisation as the set up you have is perhaps ineffecient and duplicating. Plus you should have one field for date time (you can split and combine the displays unless you are using bound forms )

[epod69]

Ok..let me try to answer your questions, im not a database guru or anything..

I am using access as my database. I have been using date and time through entire program and haven't came across a problem yet. If I use the words by themselves I of course put brackets around them. In the database, there is no specific format for the date, all the records are "##/##/####" formated in the date fields. The time field is formated medium time (##:## AM/PM).

dt_Date is defined as date and dt_Time is defined as String (if I formated it as Date the time would end up in my example being 12:00:00 PM, and I thought the extra two 0's would kinda screw up the sql statement.