DLookup with IF and AND Statements

[Tim777]

I have a form attached to DB "Data" My data includes 5 time slots (SlotID) to add appointments. Those same 5 slots are filled on a daily basis. Before the user enters an appointment I want to check to see if there is already an appointment on the same day in that time slot so that appointments are not double booked.

Here is my code - I am receiving a Run-time error 3464 - data type mismatch. If I eliminate the 2nd criteria, (the date) it works in identifying records in the same slot but it finds them on ALL days of course. What is wrong with this syntax?

Private Sub cmbSlotID_Change()
If Me.cmbSlotID.Value = DLookup("[SlotID]", "Data", "[SlotID] ='" & Me.cmbSlotID & "'") AND _
Me.txtStart_Date = DLookup("[Start Date]", "Data", "[Start Date] ='" & Me.txtStart_Date & "'") _


Then MsgBox "This timeslot is alread filled."
End Sub


Thanks in advance!!!

-Tim

[pbaldy]

Wrong delimiter:

DLookup Usage Samples

[Tim777]

Thanks Paul, Sorry, I took a look at the samples and it's just not penetrating my thick head. Following the examples I modified my code.
Still getting type mismatch error.
What am I doing wong?

Private Sub cmbSlotID_Change()
If Me.cmbSlotID.Value = DLookup("[SlotID]", "Data", _
"[SlotID] ='" & Me.cmbSlotID And "[Start Date] ='" & Me.txtStart_Date & "'") _
Then MsgBox "This timeslot is alread filled."
End Sub


Thanks!!!

Tim

[pbaldy]

Date values should be surrounded by # rather than '.

[Tim777]

Am I close.. ?

Private Sub cmbSlotID_Change()
If Me.cmbSlotID.Value = DLookup("[SlotID]", "Data", _
"[SlotID] ='" & Me.cmbSlotID & "'" _
& AND "[Start Time] = #" & Me.txtStart_Time & "#") _
Then MsgBox "This timeslot is alread filled."
End Sub

[pbaldy]

Close, but with a common mistake. Try this:

If Me.cmbSlotID.Value = DLookup("[SlotID]", "Data", "[SlotID] ='" & Me.cmbSlotID & "' AND [Start Time] = #" & Me.txtStart_Time & "#") Then MsgBox "This timeslot is alread filled."

By the way, I personally would get it working all on one line, then break it up to separate lines if you want. Otherwise you may be having line continuation problems and syntax problems, and not know which is causing a problem.

[ssanfu]

If me.cmbSlotID is a long integer (number), there shouldn't be delimiters.
If me.cmbSlotID is a string, single quotes or double quotes as delimiters.

Private Sub cmbSlotID_Change()
  If Me.cmbSlotID = DLookup("[SlotID]", "Data","[SlotID] = " & Me.cmbSlotID & " AND [Start Time] = #" & Me.txtStart_Time & "#") Then 
    MsgBox "This timeslot is alread filled."
  End If
End Sub

Delimiters:
----------
Numbers : no delimiters
Strings (text) : single quotes or double quotes
Date/time : hash signs (oglethorpe)

[Tim777]

Paul, thanks! I get no errors with that but it still doesn't work. I kept it on one line. I can still enter duplicates and the message box does not appear.
It just does nothing.

I would think what I am trying to accomplish would be rather simple. My explanation is probably lacking.

I have a table "Data" and a Bound form "frmRepairs" I want to check the value of two controls on the form before a new record can be written;
corresponding field names are SlotID and Start Date. Only 1 record can exist for any time slot on a given day.

I have a combo box cmbSlotID (this is a string value ranging from O1 to O5) Each SlotID represents and is bound to a time slot:
O1 10:00 AM - 12:00PM
O2 12:00 PM - 1:00 PM etc...

The form contains a text box with a date txtStart_Date.
When the user selects the cmbSlotID I want to check the table on that date to make sure another record does not exist with the same SlotID
I have the code running when the cmbSlotID value changes.

Hopefully this helps. I really appreciate your help!!!

[pbaldy]

Can you post the db here, or a representative sample?

[ssanfu]

Your original code has [start TIME]....
Should it be [start time]or [start date]?

Private Sub cmbSlotID_Change()
  If Me.cmbSlotID = DLookup("[SlotID]", "Data","[SlotID] = '" & Me.cmbSlotID & "' AND [Start Date] = #" & Me.txtStart_Date & "#") Then 
    MsgBox "This timeslot is alread filled."
  End If
End Sub

[Tim777]

Thanks Paul!! Thanks Steve!!!

That was the problem. It should have been start date, not start time. Working perfectly now!!

I really appreciate your help guys!!!