Databases again

[coyote69]

How would I search the database to avoid overlapping appointments if the appointment date was in one field and the time was in another?

 

[coyote69]

Tried that and still get the same error.

 

[Nagrom]

What exactly is the error message?

 

[coyote69]

Run time error -2147217900 (80040e14):

sybntax error (Missing operator) in query expression [ApptDate]= #12/28/02# and where time = 11 am:


That is the message that pops up in the window. the date and time are from the current record that is displayed in the form window.

 

[Nagrom]

Do the same thing for the time field criteria as for the date field (surround it with #'s). Also, I am not sure it accepts AM or PM, though I may be wrong about that - I believe it just uses the 24 hour clock times (like military time).

 

[coyote69]

Same error message

 

[Nagrom]

Try the query directly in Access. The problem is the query string, so it is just a matter of working out how the parameters should be formatted. Also, I think there should be apostrophes in addition to the #'s (like this: [apptdate] = '#12/28/01#') around the date/time fields. I know I didn't say that before, but I don't work with Access as my database very often, and it requires some odd things in queries sometimes.

 

[coyote69]

Still does not like it. I appreciate you help maybe It time to try another way.

 

[Nagrom]

I hate to give up when the only issue is how to build the SQL string. Would you be willing to email the MDB file to me and let me see what I can do about setting up the query string correctly? I am sure I can get it working right if I have access to the database. My email address is seanpatrickmorgan@yahoo.com if you don't mind trying that.

 

[Nagrom]

Okay, I received your code and the database today. I am going to send it back to you via email as well, but I wanted to post it here in case anyone else with a similar problem was following this post.

First, I changed the data type for the "Time" field from Text to Date/Time. Next I specified the display format as "Medium Time". The last thing I did to that field was set the input mask to the "Medium Time format" (Mask string: "99:00\ >LL;0;_&quot. Don't put quotes around the mask string when you enter it into Access.

From there all we had to do was properly format the query string. I worked on it in Access and found that you do enclose the date/time criteria in your string with #'s, but do not use the apostrophe (Example: ...WHERE [ApptDate] = #4/9/2002#). You also do the same thing for the time field (Example: ...WHERE [ApptDate]= #4/9/2002# AND Time = #5:00 PM&quot.

Voila! Everything should work like you want it to after that. Give it a try and let me know how it goes.

 

[Nagrom]

Oops! In my last response I left off the closing # after the time criteria. It should have been:

...WHERE [ApptDate]= #4/9/2002# AND Time = #5:00 PM#

Not

...WHERE [ApptDate]= #4/9/2002# AND Time = #5:00 PM"

 

[coyote69]

I recieved that, thanks for the help, but my program still gives me and error message when it get to the connectionstring.open. Obviously I still have issues with my Vb or my programming skills. I chose a simpler approach (I think) of combining the date and time in one field. I can get it to find a similar appointment a send a message for that but if I do not find a match it update the first record in my database and not he one i am displaying in my form. (Maybe I need to start a new thread for this one.
Private Sub apptcheck()


Dim upDateAppt As String


upDateAppt = InputBox("Enter new appointment date and time", " _/_/__, _ am/pm&quot


On Error GoTo errorhandler

datContactManager.Recordset.FindFirst "apptdate = #" & upDateAppt & "#"
On Error GoTo errorhandler

If (datContactManager.Recordset.NoMatch = False) Then

'msgbox display if date is not available
MsgBox "That appointment time is not Available"

Exit Sub

Else
txtAppointmentDate = upDateAppt


Exit Sub
End If
errorhandler:
Exit Sub


End Sub

 

[Nagrom]

Are you using the exact code I sent you? I didn't know the location of your database so I simply refer to "C:\db1.mbd" in my code. Also, make sure you have the correct version of the Jet OLEDB provider available that is specified in the connection string.

 

[coyote69]

Yes I changed all of that and still have the same issues.