Duplicate appointment message, Access bug with date?

[Mabel11]

Hello,

I have a database where I insert client appointments through a form. To avoid enterring duplicated appointments, a message pops up when the date, time and counsellor name already exist in the Appointments table. In the AfterUpdate event of the Counsellor command I added this event procedure:



Private Sub txtCounsellorID_AfterUpdate()
If Not IsNull(DLookup("[AppointmentID]", "tblAppointments", "[TimeID] = " & Me.txtTimeID & " AND [AppointmentDate]= #" & Me.AppointmentDate & "# AND [CounsellorID]= " & Me.txtCounsellorID & " ")) Then
MsgBox "Double Booking, change time, date or counsellor"
Me.Undo
End If
End Sub

This works perfectly. However I was trying to add an appointment on 8th September 2010 8/09/10 and it kept on saying there was a duplicate when this was not the case. I then figured out by doing a few tests that I had an appointment on the 9th of August 2010, 9/08/10, at the same time with the same counsellor. I tested reversing other dates and this happened every time. It seems like Access is taking 8/09/10 to be the same as 9/08/10. The field AppointmentDate format is Date/Time Short Date and the first number should be the day and the second the month.

Has this happened to anybody else before? Is there a way I can work around this? Please help! Thanks!

[NTC]

honestly it seems unlikely. Dates are put into a serial value behind the scenes, and dates is such a pervasively used feature that this would be a well known issue if it existed. I am going to guess that you will find that you are able to trigger other false positives in the end if you keep experimenting.....

you are triggering a Not IsNull state. set up a temporary output of the actual value being looked up and keep testing....

[pbaldy]

See if these help:

How to Format Dates as U.S. Dates Regardless of Regional Settings

http://www.mvps.org/access/datetime/date0005.htm

I've adapted the second method in a couple of apps.

[pkstormy]

Are you sure you don't somehow have the inputmask reversed? No - this doesn't really happen and there is most likely a logic reason for why it's happening to you.

I would change this code though:
Private Sub txtCounsellorID_AfterUpdate()
If Not IsNull(DLookup("[AppointmentID]", "tblAppointments", "[TimeID] = " & Me.txtTimeID & " AND [AppointmentDate]= #" & Me.AppointmentDate & "# AND [CounsellorID]= " & Me.txtCounsellorID & " ")) Then
MsgBox "Double Booking, change time, date or counsellor"
Me.Undo
End If
End Sub

to

Private Sub txtCounsellorID_AfterUpdate()
if isnull(me!AppointmentID) then '<- check that the record hasn't been added to the db yet (ie. an AppointmentID hasn't been created if that's your autonumber value field.)
dim AID as variant
Dim ADate as date
ADate = Format(me!AppointmentDate,"mm/dd/yyyy")
AID = dlookup("[AppointmentID]", "tblAppointments", "[TimeID] = " & Me.txtTimeID & " AND [AppointmentDate]= #" & ADate & "# AND [CounsellorID]= " & Me.txtCounsellorID & " ")
if not isnull(AID) then
MsgBox "Double Booking, change time, date or counsellor"
msgbox "The duplicated AppointmentID found is: " & AID & " on the Date: " & ADate
Me.Undo
End If
end if
End Sub

Also keep in mind that since you're doing this in the afterupdate event, the record could possibly already have been added when you're checking which would show that a record exists regardless depending on the sequence of your tab order and adding the data.

[Mabel11]

Thanks for your suggestions, I will try them and will let you know what the results are.

I have in the meantime continued to test by inputting different appointment dates in a sample database (which had no previous appointments). I added for example appointment on 9th June 2010 (09/06/2010), time 3:30, counsellor A. Then I added appointment on 6th September 2010 (06/09/2010) , time 3:30, counsellor A and the message poped up. If I however added the second appointment with a different time, then the message would not appear and it would let me save the appointment. I have tested this with several dates and always same results. Maybe there is a better way to test this but this is what I have done so far.


Thanks!