Duplicates based on date

[didacticone]

I have a form to schedule appointments. We schedule what we call a "first am" and a "first pm" appointment for each day. I have these two options displayed as check boxes on my form. They are yes/no fields in the underlying table. I would like when the user checks either one of them, for the database to look at the date that has been entered in my date field and make sure that there isnt another first am or pm already checked for that day. As we can only have one of each per day. Is something like this possible? thanks

[Seth Schrock]

It is totally possible. The check would consist of a DCount() where you search your table for all records where the date equals the selected date and first am or pm is true. If it equals more than 0 then it is already scheduled.

There are a couple of ways to implement this. You could check when the first AM box is checked and automatically uncheck it if that spot is already filled. You could also use the date field's After_Update event to run the DCount() and disable the checkbox if the DCount() returns anything. This would make it so that you would know if the spot is taken as soon as you select the date. If you want to do the later option, then I would use an If/Then statement to make sure the update to the date field wasn't to remove the date as trying to run the DCount() without the date being populated would cause an error. Something like

If Not IsNull(Date_Field Then
     DCount(Fill_this_in)
End If

[didacticone]

I am using the following and it is returning the following message "run time error '3464': data type mismatch in criteria expression". the date field is set as a date/time field in the table.

If DCount("*", "blank work order table", "[FIRSTAM]=" & Me![firstam] & " And [DATE1] = '" & _
                                     Me![Date1] & "'") > 0 Then
 
     MsgBox "ALREADY A FIRST CALL FOR THIS DAY"

[Seth Schrock]

I believe that you would need to change your criteria to

"[FIRSTAM]= True And [DATE1] = '" & Me![Date1] & "'"

I don't have a database to test this on and I'm not sure if the "True" needs to go inside the quotes or outside, but I don't think that you will get a mismatch error.

[didacticone]

still getting the same error unfortunately

[Seth Schrock]

Try creating a query and first just test for FIRSTAM = True. If that works, then remove that criteria and replace it with the DATE1 = full form reference. Let me know which one fails so that I can know which one is causing the error.

[didacticone]

i made a query and set the criteria as true and that returned results. im not totally sure what you mean by " date1 = full form reference"

[didacticone]

i actually just changed the date portion of the code and used street instead and that worked... the street field is just a text field... so it seems as though the problem is the date portion of the code

[Seth Schrock]

Oh duh, you need to remove the single quotes like this:

If DCount("*", "blank work order table", "[FIRSTAM]= True And [DATE1] = " & Me![Date1]) > 0 Then
 

The single quotes are making the Me!Date1 be passed as a string instead of a date. I must be slipping :)

[didacticone]

i just used your latest code and its not returning an error but its also not picking up the duplicate and returning my message box... its just not doing anything

[Seth Schrock]

What event is triggering this code?

[didacticone]

i have it in the afterupdate of the firstpm check box

[didacticone]

i meant firstam checkbox sorry

[didacticone]

i actually got it to work using the following

If DCount("*", "blank work order table", "[FIRSTPM]= True And [date1] = #" & Me.Date1 & "#") > 0 Then

thanks for your help!

[Seth Schrock]

No problem. I suppose I should have known, but I didn't think that the # sign was necessary unless you where typing in the actual value.

[NeoPa]

It is absolutely necessary. In fact, there is more required if you want your databases to work outside of the USA - Literal DateTimes and Their Delimiters (#).

Your DCount() reference should look like :

DCount("*", "blank work order table", "[FIRSTPM] And ([date1] = #" & Format(Me.Date1, "m\/d\/yyyy") & "#)")