Newbie having trouble with data type mismatch error...

[David618]

I'm very new to Access programming, so pardon my ignorance! I "borrowed" some code to make a log in form work and it seemed to run okay until it got to the following:

If Me.txtPassword.Value = DLookup("Emp_Pass", "tblEmployeeRoster", _


"[Emp_ID]=" & Me.cboIdentity.Value) Then

The error message I got was, "Run-time error '3464': Data type mismatch in criteria expression." [Emp_ID] is formatted as a text field. Emp_Pass is formatted as text.

I've spent nearly 2 hours trying to fix the problem to no avail. Thought I would give this forum a try (my very first time). Any help/clues would be greatly appreciated!

[ssanfu]

What is the bound field of Me.cboIdentity? What field type is the bound field?

If, in the table, "Emp_ID" is a Long number type, then the bound column (field) in Me.cboIdentity must be a Long.

[pbaldy]

See here for the syntax adjustment for a text field:

DLookup Usage Samples

[David618]

Hi Steve -

The bound field is Emp_ID from that same table (tblEmployeeRoster); so, the combo box itself shows 3 fields from that table (Last_Name, First_Name, and Emp_ID) with that third one (Emp_ID) bound. The field type for that should be the same, right? I checked the Properties for that field in the query for the Row Source of that combo and it doesn't say anything...

[pbaldy]

I wonder if I should post the link again...nah, he'll see it.

[David618]

That worked! I tried mimmicking your example:

If Me.txtPassword.Value = DLookup("Emp_Pass", "tblEmployeeRoster", _
"[Emp_ID] = '" & Me.cboIdentity.Value & "'") Then

Thanks so much!!!

[pbaldy]

Happy to help! I suspect Steve missed this in the OP: "[Emp_ID] is formatted as a text field".

[ssanfu]

I suspect Steve missed this in the OP: "[Emp_ID] is formatted as a text field".

I saw that but wasn't sure if the field [Emp_ID] was actually numeric. If [Emp_ID] is the PK, having the field type of text is problematic (from what I have read).
And if [Emp_ID] was text, my next reply was going to be about delimiters.

[David618]

Steve, what's wrong with having Emp_ID (which is the PK) as text (just curious)?

[ssanfu]

A TEXT field can be up to 255 characters long (Access 2000), which would make for a crazy long key comparison. (main reason)

Trouble with sorting. I entered these text strings and sorted them. Since text is sorted left to right.... this what you get.

A1
A100
A11
A2
A20
A22
A300
A34
A45

Will/could an employee's Emp_ID ever change?

Also, see:
http://www.bluemoosetech.com/microso...ial.php?jid=54
http://www.fmsinc.com/free/newtips/primarykey.asp

Much easier (for me) to use an autonumber as the PK.

[David618]

Thanks, Steve -

I see your point. In this case, the ID would never change - is unique and assigned by the company, which is what I have to use. If it were up to me, I probably would use the autonumber.