Dcount for 2 criteria date fields

[rmd62163]

Is it possible if what I would like to accomplish is to rule out duplicate value. With a form with two fields one is a date field formatted as short date and the other is a lookup combo box that I use to lookup predefined times and is formatted as short time. The date and time are stored in the same table as date/time in separate fields. I attempted to use Dcount, and get syntax error missing operator. Below is one of a hundred that i have tried after scouring this forum and the net. I ended up converting the Appt_Time field to a text field in the table to eliminate one date field but still got no where. Any suggestions as to the syntax or perhaps a different approach would be greatly appreciated?

Private Sub Appt_Time_BeforeUpdate(Cancel As Integer)
    'Check table for duplicate
If DCount("[Appt_Time]", "tblexams", "Appt_Time= & Appt_Time &   And [Sch_Date] = #" & Sch_Date & "#") > 0 Then
        'Message box warning of duplication
        'MsgBox "Warning Appointment Time " _
             & Appt_Time & " has already been assigned" _
             & vbCr & vbCr & "Please select another Time", _
               vbInformation, "Duplicate Information"
        'Undo duplicate entry
       Me.Undo
        Cancel = True
    End If
End Sub

[RuralGuy]

Here's what I think about Lookup Fields: http://access.mvps.org/access/lookupfields.htm
They do not contain what you think they do.

[rmd62163]

My lookup is pulling the times from one table (tblhours) and storing that in a different table (tblExams). So if I am reading what you are saying correctly that should not pertain to this database. Or am I miss interpreting it? Or are you saying that the lookup comboBox is what's screwing the Dcount?

[RuralGuy]

I believe a Lookup Field is really an array.

[rmd62163]

Came up with a solution if anyone runs into this type of issue. Made a query.

SELECT tblexams.Appt_Time, tblexams.Sch_Date, tblexams.Patient_ID
FROM tblexams
WHERE (((tblexams.Appt_Time)=[Forms]![Patient]![Exams Subform]![Appt_Time]) AND ((tblexams.Sch_Date)=[Forms]![Patient]![Exams Subform]![Sch_Date]) AND (Not (tblexams.Cx) Is Null) AND (Not (tblexams.NS) Is Null) AND (Not (tblexams.RS) Is Null));

. then I ran

If DCount("*", "qryDupTime") > 0 Then

on the before update event.