locked
Search Acsess Table or quiery RRS feed

  • Question

  • Hello,

    i am having a hard time findin a solution for my problem and it:

    i have an appointment quiery that has the time column,date column and worker column.

    i have 1 form with 3 text boxes:

    txt1 - day

    txt2 - time

    txtx3-worker

    i am tying to put some code together that checks the availability of the spercific worker at the specific time and date.

    i will be very greatfull for the help.

    Sunday, June 10, 2012 8:02 PM

Answers

  • You made some pretty major changes, which are the cause for it not working!

    Private Sub ô÷åãä110_Click()
    Dim strCriteria As String

    strCriteria = "empname = '" & Replace(me.tt44, "'", "''") & "' " & _ "AND day1 = #" & Format(Me!Day, "yyyy\-mm\-dd") & "# " & _ "AND StartHour = #" & Format(Me!StartHour, "hh:nn:ss") & "#" If DCount("*", "1appointment", strCriteria) = 0 Then MsgBox "There is no conflict." Else MsgBox "That worker is busy at that time." End If End Sub

    Rename the control on your form from Day: that's a reserved word, and you should never use reserved words for your own purposes. For a comprehensive list of names to avoid (as well as a link to a free utility to check your application for compliance), check what Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)



    Monday, June 11, 2012 7:22 PM

All replies

  • We need to know how your data is stored before we can offer any suggestions.

    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Sunday, June 10, 2012 8:27 PM
  • i build a table caled worker and then a quiery QWorker.
    Sunday, June 10, 2012 8:30 PM
  • What's stored in the worker table? How do we know when a worker is busy?

    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Sunday, June 10, 2012 8:33 PM
  • In table worker stored the name of the worker the date and the time.

    So basicaly what i need to do is to write in 3 text boxses the name of the worker the date and the time.

    and if finds a match in the in the table/quiery so meaning the worker is busy.

    Monday, June 11, 2012 2:13 AM
  • No offense, but you're making it very difficult to help you!

    Please give an example of what's in the table. How would you indicate that employee 1 is busy from 9:00 AM to 11:00 AM on Monday, June 11, 2012? You must be storing start and finish times, otherwise how would you be able to tell that employee 1 isn't available from 9:30 AM to 10:00 AM?


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Monday, June 11, 2012 11:54 AM
  • Hi

    sorry for my explanation.

    what i want to sort is according the date as you said and according to the start hour for example:

    I want to set an appointment for some body.

    i write the date Dec/06/2011

    iwrite the worker`s name to BOB

    and i write the start time 15:00

    after the code performing the sorting it can tell me that this day and at this time bob is busy meanin g in the table BONB already has an appointment.

    So the answer i need from the code "sorry this time at this day is ocupied but he is free at" then to out put the list of free time at the same date from 8:00 till 16:00 working day.

    Sorry for my explanation before i hope this is better because i am very stuck with my system without your help

    Thank in advance

    Oleg

    Monday, June 11, 2012 3:03 PM
  • Strikes me that your table isn't sufficient to meet your needs. You need at least two tables: one for Workers (one row for each worker), and one for WorkerAppointments (one row for each time when a given worker is busy). For instance, you'd need

    WorkerID  WorkerName
           1  John Doe
           2  Mary Smith
           3  Fred Brown

    and

    WorkerID  StartTime            StopTime
           1  2012-06-11 09:00:00  2012-06-11 11:00:00
           1  2012-06-11 13:15:00  2012-06-11 13:45:00
           2  2012-06-11 08:00:00  2012-06-11 16:00:00

    Now you can create a query (qryWorkerAppointments) that joins the two tables and use that query as the basis for your lookups:

    SELECT Worker.WorkerID, Worker.WorkerName, WorkerAppointments.StartTime, WorkerAppointments.StopTime
    FROM Worker INNER JOIN WorkerAppointments ON Worker.WorkerID = WorkerAppointments.WorkerID;

    For instance, to find out whether WorkerID 1 is available at 10:00 AM today, I can check:

    ?DCount("*", "qryWorkerAppointments", "WorkerID = 1 AND StartTime <= #2012-06-11 10:00:00# AND StopTime >= #2012-06-11 10:00:00#")
     1

    And to see whether WorkerID 1 is available at 2:00 PM today, I can check:

    ?DCount("*", "qryWorkerAppointments", "WorkerID = 1 AND StartTime <= #2012-06-11 14:00:00# AND StopTime >= #2012-06-11 14:00:00#")
     0

    The 1 for the first DCount indicates that 1 record was found overlapping the given time, therefore the worker is busy. The 0 for the second DCount indicates that no records were found overlapping the given time, therefore the worker is available.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Monday, June 11, 2012 3:38 PM
  • Hi Thanks

    for the replay.

    i have one table and qeiry from it.

    ihave saparate clumns for date,start time,end time,worker name and worker id.

    i do not understand how the above lines should work.

    like i said before i have 3 text boxes fro date,worker`s name,and start time. according to what i put in the text boxes i should recieve a masege box with the answer.

    Monday, June 11, 2012 5:14 PM
  • if you think that i should have more then one table.

    how it will be then?

    Monday, June 11, 2012 5:17 PM
  • It sounds as though your one table is essentially the same as the WorkerAppointments table I described, even though having both Name and Id in the same table is redundant, and an example of improper normalization. That was the point of qryWorkerAppointments, which joined the two properly normalized tables together. That being said, your Workers table is essentially the same as my qryWorkerAppointments, so we can proceed from there. Note that you really should have date fields for both start and end. In fact, you should have a combined Date/Time field for both.

    Let's assume your form is named frmAppointments, and it has text boxes txtWorkerId (name is NOT unique!), txtWorkDate and txtStartTime. You'd need to have a button (let's call it cmdLookup) that the user clicks on once they've filled in the three text boxes:

    Private Sub cmdLookup_Click()
    Dim strCriteria As String
      strCriteria = "WorkerID = " & Me!txtWorkerID & " " & _
        "AND WorkDate = " & Format(Me!txtWorkDate, "\#yyyy\-mm\-dd\#") & " " & _
        "AND StartTime <= " & Format(Me!txtTime, "\#hh\:nn\:ss\#") & " " & _
        "AND EndTime >= " & Format(Me!txtTime, "\#hh\:nn\:ss\#")
      If DCount("*", "worker", strCriteria) = 0 Then
        MsgBox "There is no conflict."
      Else
        MsgBox "That worker is busy at that time."
      End If
     
    End Sub


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Monday, June 11, 2012 5:31 PM
  • I wil try it and advice ASAP.

    one question:

    in this code can we combine if the is aconflict to show the availble times of this worker at that day?

    Monday, June 11, 2012 5:40 PM
  • Hi i have

    a run time error 3078

    and saying that table cannot be found

    Monday, June 11, 2012 6:11 PM
  • If you want help, you need to:

    • indicate the exact name of your table and the names of the fields in that table
    • indicate the exact name of your form and the names of the controls on that form
    • provide the exact code you used

    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Monday, June 11, 2012 6:23 PM
  • Hi Please see the code it is loke you advised only changed a little:

    Private Sub ô÷åãä110_Click()
    Dim strCriteria As String
      strCriteria = " day1 = #" & Me!Day & "# " & _
        "AND StartHour = #" & Me!StartHour & "#"
    
      If DCount("*", Me.tt44, strCriteria) = 0 Then
        MsgBox "There is no conflict."
      Else
        MsgBox "That worker is busy at that time."
      End If
    
    End Sub

    table name is "1appointment"

    the name of the fields:

    starthour

    endhour

    empname

    day1

    Form name "appointment"

    tt44 for empname

    day for day1

    starthour for starthour

    starthour for starthour

    hope it is what needed

    Thank you very much for your time and effort

    Monday, June 11, 2012 6:32 PM
  • You made some pretty major changes, which are the cause for it not working!

    Private Sub ô÷åãä110_Click()
    Dim strCriteria As String

    strCriteria = "empname = '" & Replace(me.tt44, "'", "''") & "' " & _ "AND day1 = #" & Format(Me!Day, "yyyy\-mm\-dd") & "# " & _ "AND StartHour = #" & Format(Me!StartHour, "hh:nn:ss") & "#" If DCount("*", "1appointment", strCriteria) = 0 Then MsgBox "There is no conflict." Else MsgBox "That worker is busy at that time." End If End Sub

    Rename the control on your form from Day: that's a reserved word, and you should never use reserved words for your own purposes. For a comprehensive list of names to avoid (as well as a link to a free utility to check your application for compliance), check what Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)



    Monday, June 11, 2012 7:22 PM
  • Thank you
    Monday, June 11, 2012 7:39 PM