Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169

    Unanswered: Creating appointments database

    Right, i am creating a database for one of our staff that controls his appointments for seeing clients, I am trying to check to see if another appointment overbooks the one trying to be made and i keep getting an error saying syntax error (missing operator) in query expression. Here is the code

    Code:
    Private Sub Confirm_Click()
        Dim rcs As DAO.Recordset
        Dim X As Integer
        Dim Y As Integer
        Dim Result As Integer
        Dim DoTill As Integer
        Dim SQL As String
        Dim SQL2 As String
        Dim StartTime As Date
    
        
        If Not IsNull(AppDurationHr) Then
        X = AppDurationHr * 4 'Get sessions in an hour
        If Not IsNull(AppDurationMin) Then
        Y = AppDurationMin / 15 '1 Session equals 15 mins
        
        Result = X + Y 'Get total number of sessions
        '-----------------------------------------------------------------------------------------------------
        'Check Nulls
        If IsNull(ApDateOfApp) Then
            MsgBox "Please enter a date for the appointment", vbCritical, "Enter Date"
        Exit Sub
        If IsNull(AppDurationMin) Or AppDurationMin = 0 Then
            MsgBox "Please select 0 or a duration in mins for this appointment", vbInformation, "Enter mins"
        Exit Sub
        If IsNull(AppDurationHr) Or AppDurationHr = 0 Then
            MsgBox "Please enter 0 or a duration in hours for this appointment", vbInformation, "Enter Hours"
        Exit Sub
        End If
            End If
                End If
        '-----------------------------------------------------------------------------------------------------
    
            SQL2 = "SELECT AppSubTbl.AppDate, AppSubTbl.AppTime " & _
            "FROM AppSubTbl " & _
            "WHERE (((AppSubTbl.AppDate)=" & "#" & [Forms]![Appointment]![ApDateOfApp] & "#" & "));"
            
        Set rcs = CurrentDb.OpenRecordset(SQL2, dbOpenSnapshot) 'Open a DB connection
        
            StartTime = AppTime 'Set the start time to the time of the appointment
        
        DoTill = 0
    
    Do Until DoTill = Result
        rcs.FindFirst "[Apptime] = " & Me!AppTime
            
        If rcs.NoMatch Then
            StartTime = DateAdd("n", 15, StartTime)
                DoTill = DoTill + 1
        Else
            MsgBox "There is a conflicting appointment, please reschedule", vbCritical, "Conflict"
                Exit Sub
        End If
    Loop
        
        DoTill = 0
        StartTime = AppTime
        
        
    Do Until DoTill = Result
            
            SQL = "Insert Into [AppSubTbl](ApSAppId,AppTime,AppDate) " & _
            "Select [Patient].PatId, #" & StartTime & "# As TimeStart, #" & ApDateOfApp & "# As DateOfApp " & _
            "From [Patient] " & _
            "Where [Patient].PatID = Forms!Appointment!ApPatID"
            
            DoCmd.SetWarnings False
        DoCmd.RunSQL SQL
            DoCmd.SetWarnings True
                    StartTime = DateAdd("n", 15, StartTime)
                
                DoTill = DoTill + 1
        Loop
            End If
                End If
    This is all the code

    I am getting an error on this line : rcs.FindFirst "[Apptime] = " & Me!AppTime
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I believe this:

    SQL = "Insert Into [AppSubTbl](ApSAppId,AppTime,AppDate) " & _
    "Select [Patient].PatId, #" & StartTime & "# As TimeStart, #" & ApDateOfApp & "# As DateOfApp " & _
    "From [Patient] " & _
    "Where [Patient].PatID = Forms!Appointment!ApPatID"

    should read as this:

    SQL = "Insert Into [AppSubTbl](ApSAppId,AppTime,AppDate) " & _
    "Select [Patient].PatId, #" & StartTime & "# As TimeStart, #" & ApDateOfApp & "# As DateOfApp " & _
    "From [Patient] " & _
    "Where [Patient].PatID = " & Forms!Appointment!ApPatID
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    That bit aint the problem? That bit works fine?
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Okay then... can you be more specific about which line is throwing the error?
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Has your staff member tried this nifty trick?

    Click on Start/All Programs/Microsoft Office/MS Outlook

    Really, how many times does the wheel need to be reinvented?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    The wheel has to be re invented because all of his patients are stored on this database and he will be connecting to the company to connect to the database because the people on site need to put appointments on aswell.

    There really was no need for the attitude on this question, i wouldnt spend time doing something if there wasnt a need to do it?

    As for the line that is chucking the error, i wrote the line under the code, it's the line that does the find first?
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  7. #7
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Appointment database

    Hi Chris,

    I would recommend using a query to find out conflicting appointments. Assuming in your appointments table you have some of these fields (or similar, the names are irrelevant as long as the meaning is the same):
    Table Name: Appointments
    Field Names:
    Appointment_ID
    User_Name
    Start_Date
    Start_Time
    End_Date
    End_Time

    Use a query of:

    SELECT Count(APPOINTMENTS.APPOINTMENT_ID) AS CountOfAPPOINTMENT_ID
    FROM APPOINTMENTS
    WHERE (((APPOINTMENTS.END_DATE)>=#1/24/2005#) AND ((APPOINTMENTS.END_TIME)>=#12/30/1899 12:0:0#)) AND ((APPOINTMENTS.User_Name = 'USER'));

    Replace the date parameters with the Start date and start time of the new appointment. The user name is the name of the user for whom you are making the appointment - if that is important or you can just leave that out. You can save this as a parameter query, use it in a Dcount statement or create a VBA function that can be called anywhere. The query will return the number of conflicts with the new appointment. A zero means no conflict. To see the appointments that are causing the conflict just use the same Where statment but attach it to a SELECT statement without the Count.

    I hope that helps
    Justin

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "i wouldnt spend time doing something if there wasnt a need to do it"

    Congrats, but you would be surprise to know how many people do...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I missed the line at the bottom...

    Since Me!apptime is in DateTime format, as is [AppTime],
    you would need to search this way:

    rcs.FindFirst "[Apptime] = #" & Me!AppTime & "#"


    Are you really surprised to find attitude on a website full of computer nerds?
    Inspiration Through Fermentation

  10. #10
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Thank you all for replies, I was going to experiment with a query to see if i can query the appointments table but unfortunately i am pretty useless at SQL, i know the basics, i will try what you suggested justin and see if i can get a result from that.

    RedNeckGeek i will try the # solution first, i did try and put the # in the search but again it gave an error, it could have been the way i did it though so will try again.

    Thanks again everyone, didnt mean to have a go blindman but it winds me up when people pass comment on something that isnt constructive.
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  11. #11
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Quote Originally Posted by RedNeckGeek
    I missed the line at the bottom...

    Since Me!apptime is in DateTime format, as is [AppTime],
    you would need to search this way:

    rcs.FindFirst "[Apptime] = #" & Me!AppTime & "#"


    Are you really surprised to find attitude on a website full of computer nerds?
    Thanks a lot, the change in the format worked, just gotta make sure that my code is reliable now, thanks again.
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It was constructive. It is always a good idea to consider off-the-shelf software as an alternative to in-house development. Unfortunately, it was not an option for you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I wonder how well you can drive outlook from access, or any other vba enabled application for that matter? Let someone else's code handle the background (appointments etc), fron end to do the data capture, allows that persons calenbday to be visible accross the network, on their pda, or mobile / cell phone / handy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •