#type error difficulty

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote