error " data type mismatch!!!!

[jocelyn ooi]

Anyone can help me why my code keep pop put this error ???

StrLeaveSql = "SELECT * FROM tblLeave WHERE emp_id = '" & rstReplacement!emp_id & "'" & _
              " AND lea_effective_date = '" & rstReplacement!lea_effective_date & "'" & _
              " AND lea_end_date = '" & rstReplacement!lea_end_date & "'" & _
              " AND lea_leave_type = '" & Me.txt_leave_type & "'"

I want to search the same effective date and end date in the table to edit the leave balance.... pls help me!!

[pdebaets]

You are doing string comparisons for all fields in your SQL statement. Is emp_Id a text field? If it is not, then that could be causing your problem. Try

... WHERE emp_id = " & rstReplacement!emp_id & _ ...

I always surround date values with "#" characters. Ex.: #1/1/2011#. Try that if your problems persist.

[NeoPa]

Good advice, but dates in SQL are a little more complicated than just surrounding them in hashes (#) (See Literal DateTimes and Their Delimiters (#) and Quotes (') and Double-Quotes (") - Where and When to use them).

You probably want something like :

StrLeaveSql = "SELECT * " & _
              "FROM   [tblLeave] "& _
              "WHERE  ([emp_id] = " & rstReplacement!emp_id & ")" & _
              "  AND  ([lea_effective_date] = #" & _
                       Format(rstReplacement!lea_effective_date, "m/d/yyyy") & "#)" & _
              "  AND  ([lea_end_date] = #" & _
                       Format(rstReplacement!lea_end_date, "m/d/yyyy") & "#)" & _
              "  AND  ([lea_leave_type] = '" & Me.txt_leave_type & "')"