Dcount - Validation not working

[princess12]

Hi

my attendance form is working with this dcount, however since datatype is not string for date in the dcount, the validation msgbox is not working.

here is the code



If DCount("[Employee]", "ClockInOut", "[Employee]='" & Me!Employee & "' AND [Status]='" & Me!Status & "' AND [DateTime]=#" & Me!txtDateTime & "#") > 0 Then
MsgBox "This employee already signed in."

please help

thank you

[A S MANN]

Try Val([DateTime])=& '" Val(Me!txtDateTime) & '"

[princess12]

thank you for your help

I now get an error - expected list separator or )
If DCount("[Employee]", "ClockInOut", "[Employee]='" & Me!Employee & "' AND [Status]='" & Me!Status & "' AND Val([DateTime])= & '" Val(Me!txtDateTime) & '" > 0 then

[ssanfu]

Is "Me!txtDateTime" an unbound text box? If so, I would use

AND [DateTime]=#" & CDate(Me!txtDateTime) & "#") > 0 Then

Does [DateTime] have a time component?
Does Me!txtDateTime have a time component?

[princess12]

thanks for your help

the Me!txtDateTime is not unbound

and both has time component in it

[ssanfu]

and both has time component in it

OK, I'm confused...
So how will [DateTime] ever equal Me!txtDateTime ?? If they are off even by 1 second, they will never be equal and the DCount() will return 0.

What is the control source for Me!txtDateTime?

[princess12]

I haven't put second in it the format is dd/mm/yy hh:mm

the control source is datetime which is in the table of clockinout it going to store into

[princess12]

and in vba code it has this

Me.txtDateTime = Now() without the seconds

do you recommend me separating the fields of time and date, is it not possible to keep it together
thanks

[ssanfu]

It sounds like you have a circular thing going on.

The Domain for Dcount() function is "ClockInOut" AND the control source for Me.DateTime is from the table of "clockinout"???

do you recommend me separating the fields of time and date, is it not possible to keep it together

Not necessarily... I'm not sure that DCount() is the best function to use.
I'm pointing out that it is hard to use "SomeDateTime = AnotherDateTime" because they have to match down to the minute or second.

If I had a table "CheckInOut", I would open a recordset on the table where Employee = Me.EmpName and a descending sort on DateTime.
Then you can see if the emp is logged in or out based on the status and the time.
I just don't have enough info on your structure and/or forms.

Actually, I wouldn't use "Employee"... this is a poor field to use to determine anything. What happens if you have 3 employees named "John Smith"?
(I always use an autonumber field for my PK field and names are always FName and LName)

[princess12]

ok thank you for explaining well, I didn't use employee for names but it similar.
what I am actually doing is having an employee to record their attendance and then ensuring it is not duplicated , anyways I will have to try again. thanks a lot for your help

[princess12]

if you don't mind could you give me a brief idea so I can compare against my idea of table structure for employee attendance. here is my current structurewhich I will improve at later time.

tblClockinOut (clockID, DateTime, Employee, Status
tblEmployee(employeeID, employee(is first three letters of the employee name)
tblClockStatus( statusID, status

then form has an autopopulated date and time textbox
status in combobox with clock in or clock out
and a textboxt to type their employee initials

[ssanfu]

Your table structure seems workable. But I brought up the names because how do you know who "Sam" is? "Sam" could be "Samantha" or "Samuel".
With 3 -5 employees, 3 letters might be OK. But if you have 10 or 20 or more, the chances of having more than one person having the same 3 letters goes up.

Attached is a very simple dB..... maybe it will help

[princess12]

thanks a lot for your example, I just realised my work is similar to yours excluding the checking the last checking thing. I'll just removed the date dcount and check against the employee and status only. made a lot sense once I saw your example. thanks