Dlookup / Dcount ? Stumped!!!

[Taff]

Hi all,

I have a form with the following fields:-

StartTime = Format dd/mm/yyyy hh:nn:ss
ExpFinishTime = Format dd/mm/yyyy hh:nn:ss
Dentist = Number

StartTime is bound to my table, ExpFinishTime is calculated in the forms underlying query.

Before the record updates I am trying to check if anyone already has an appointment scheduled in during that particular time for that particular dentist. So if the record being added has a starttime between those two times for that dentist, throw up a message box.

Thanks for any help.

Taff

 

[flixx]

The short answer is use DLookup on the after update property of time field
you are using. DLookup command works like a query, so to speak and DCount is used to count the amount of something from something to something. Thats the real simplified explanation if you need more detail look under help for the true definitions and sample's of the DLookup and DCount Commands.

 

[WayneRyan]

Taff,

You just need a count, so use DCount, you won't have to handle
the possible Null return of the DLookUp.

If DCount("[StartTime]", _
          "Appointments", _
          "Dentist " & Me.Dentist & " And #" & Me.StartTime & "# Between StartTime And ExpFinishTime") > 0 Then
   MsgBox("Time booked for that dentist.")
End If

Wayne

 

[Taff]

Thanks for the reply Wayne,

Hoping I'm close now.

However I get a Run-time error '3075'

Syntax Error (Missing Operator) in query expression 'Dentist 1 And #10/02/2005 09:00:00# Between StartTime and ExpFinishTime'.

Have attached a sample of the problem.

Any Ideas?

Taff.

 

[WayneRyan]

Taff,

Type ... forgot the "=" sign.

"Dentist = " & Me.Dentist

Wayne

 

[Taff]

Thanks Again Wayne,

Taff.