Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2012
    Location
    india
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    #type error difficulty

    I am using two tables . Table1 (Master Table) consists two fields. as follows

    Table 1 (acting as Master)
    FieldName DataType
    ID Autonumber
    AttendenceDate Date/Time

    Table 2 (as sub form - child)
    FieldName DataType Description
    FieldNo1 ID Autonumber
    FieldNo2 AttendenceDate Date/Time
    FieldNo3 EmployeeID Number Inserted through Lookup Wizard from Employee table.
    FieldNo4 StatusID Number inserted through Lookup Wizard from TableStatus having
    Status as 1. Present, 2. AbsentExcused, 3. Leave, 4. UnexcusedAbsent
    FieldNo5 ReportTime Text (9.50AM on daily basis as default )
    FieldNo6 ReportedAt Test Time when actually the employee reports for duty
    FieldNo7 LateByMinuts
    The formula for LateByMinuts is =IIf([StatusID]=”Present”,DateDiff("n",[ReportTime],[ReportedAt]),0)
    Or
    =IIf([StatusID]=1,DateDiff("n",[ReportTime],[ReportedAt]),0)
    In case StatusID is a number type field

    On using VBa procedure in the current event of subFom as
    Private Sub Form_Current()
    If IsNull(Me.ReportTime) Then
    Me.ReportTime.Value = Format(Date, "d/m/yyyy") & " 9:50:00 AM"
    End If
    If IsNull(Me.ReportedAt) Then
    Me.ReportedAt.Value = Format(Now(), "hh-nn-ss ampm")
    End If
    End Sub
    In LateBYMinuts field there occurs an error as #Type!
    Even though the StatusID is changed to Number i.e.1,2,3,4

    The question is that system should record the reporting time of employee who has actually reported (i.e. is present besides it should also record how many minutes the employee is late. i. e. LateByMinuts.

    Please help
    Thank You
    saif

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    FieldNo5 ReportTime Text (9.50AM on daily basis as default )
    FieldNo6 ReportedAt Test Time when actually the employee reports for duty
    As I see it you have Reporttime as Text , and REportedAt as a Date.
    You can't calculate a DateDiff between a text field and a Date field.

    Is there a reason that REportTime is Text?
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Mar 2012
    Location
    india
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    #type error difficulty

    Hi


    I am sorry, there is a typing mistake. in fact both ReportingTime and ReportedAt are text fields. There is no reason in keeping one of the two fields as text field.

    I had written as under

    FieldNo5 ReportTime Text (9.50AM on daily basis as default )
    FieldNo6 ReportedAt Test Time when actually the employee reports for duty

    But actually both are text fields.
    Field No6 as

    FieldNo6 ReportedAt Text when actually the employee reports for duty

    THANKS

    saif

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    That makes it even worse. You can only use DateDiff with Dates.
    There is a CDate function that will convert things to Dates but you have to be sure that what you have is recognisable as a Date. With a text field that is hard to guarantee.
    Regards
    John



Posting Permissions

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