error 2001 "You canceled the previous operation"

[jocelyn_ooi]

criteria = Me.txt_emp_id & " = [emp_id] " _
& "AND ((#" & Me.txt_start_date & "# " _
& "BETWEEN [tkl_start_date] AND [tkl_end_date]) " _
& "OR (#" & Me.txt_end_date & "# " _
& "BETWEEN [tkl_start_date] AND [tkl_end_date]) " _
& "OR (#" & Me.txt_start_date & "# = [tkl_start_date]) " _
& "AND " & Me.cmb_leave_type & " = [tkl_leave_type] " _
& "AND " & Me.cmb_halfday_option & " = [tkl_halfday_option])"
If DCount("*", "tblLeaveTaken", criteria) > 0 Then
MsgBox "You have already applied this leave before !", vbExclamation
Forms!frmLeaveApply!cmdBack.SetFocus
End If

Somebody please help me why i keep receive error 2001? izit my code got problem?? HELP PLS~~~

 

[boblarson]

You have all of the fields in the wrong place and the control references in the wrong place. See this first one (I'll let you fix the rest):

This:

criteria = Me.txt_emp_id & " = [emp_id] " _

Should be:

[SIZE=3][FONT=Calibri]criteria =  [B][COLOR=red]"[emp_id]="[/COLOR][/B] &  Me.txt_emp_id & _[/FONT][/SIZE]

 

[jocelyn_ooi]

criteria = "[emp_id] = " & Me.emp_id & _
"AND ((#" & Me.txt_start_date & "# " & _
"BETWEEN [tkl_start_date] AND [tkl_end_date]) " & _
"OR (#" & Me.txt_end_date & "# " & _
"BETWEEN [tkl_start_date] AND [tkl_end_date]) " & _
"OR ([tkl_start_date] = #" & Me.txt_start_date & "# " & _
"AND [tkl_leave_type] = " & Me.cmb_leave_type & "'" & _
"AND [tkl_halfday_option] = " & Me.cmb_halfday_option & "'))"

i not so familiar with SQL. this 1 also got syntax error.. can you help me correct it?? thank you very much!!

 

[boblarson]

Hint:

These are still backwards:

"AND ((#" & Me.txt_start_date & "# " & _
"BETWEEN [tkl_start_date] AND [tkl_end_date]) " & _
"OR (#" & Me.txt_end_date & "# " & _
"BETWEEN [tkl_start_date] AND [tkl_end_date]) " & _

 

[jocelyn_ooi]

i am thinking that if i change the place
"AND ((#" & Me.txt_start_date & "# " & _
"BETWEEN [tkl_start_date] AND [tkl_end_date]) " & _
"OR (#" & Me.txt_end_date & "# " & _
"BETWEEN [tkl_start_date] AND [tkl_end_date]) " & _

izit will affect the operation i wan to run? cuz got involve "BETWEEN"

 

[boblarson]

You are supposed to be checking if the FIELD ([tkl_start_date]) is between the CONTROL values, not the way you have it. Please explain your fields, what the start and end date fields are storing and what you are expecting to try to get?

 

[jocelyn_ooi]

ok...I want to set the date for apply leave. When I apply leave for example start date is 12-09-2011 and end date is 17-09-2011. After that I try apply a leave on 15-09-2011. So the system should pop out "You have already applied this leave before!" since 15-09-2011 already applied before... It need involve the date between 12-09-2011 till 17-09-2011.