Data Type mismatch

[ChrisLayfield]

I have the following query built and I get a "Data type mismatch in criteria expression" error. frmEmployeeBackground.EmployeeID is set to Format>General number, tbl_Employees.EmployeeID is a text primary key so I have identified that as the cause of the trouble, I just don't know the resolution. Any help?

Set rstRoleID = db.OpenRecordset("SELECT NCSRoleID " & _
                "FROM tbl_Employees " & _
                "WHERE tbl_Employees.EmployeeID = " & Forms!frmEmployeeBackground.EmployeeID)

 

[vbaInet]

There you go:

"WHERE tbl_Employees.EmployeeID = [COLOR=Red][B]'[/B][/COLOR]" & Forms!frmEmployeeBackground.EmployeeID & "[COLOR=Red][B]'[/B][/COLOR]")

Notice the quotes in red. You need those for fields of type String.

 

[ChrisLayfield]

Ok that got rid of the type mismatch error, and so I thank you. Now however I get "" = """" as a result. Then I need to convert the string to an integer for a second query so I can add the NCSRole to a text box

Set rstNCSRole = dbs.OpenRecordset("SELECT NCSRole " & _
                "FROM tbl_NCSRoles " & _
                "WHERE tbl_NCSRoles.NCSRoleID = " & rstRoleID)
Me.Text18 = rstNCSRole

 

[vbaInet]

Use a DLookup() function:

http://www.techonthenet.com/access/functions/domain/dlookup.php

Is there any particular reason your textbox is not bound?

 

[ChrisLayfield]

Wow that works perfectly thank you. The text box is just a reference to show the user the employee's role in the work and act as a reminder to the security check they are assigning. It isn't part of the table supported by this form and isn't anything that will be affected by this form.

 

[vbaInet]

You're welcome.

By the way, with the DLookup() function you would need to catch Nulls so I would suggest you use the Nz() function like this:

Text18.value = Nz(DLookup(...), "")

http://www.techonthenet.com/access/functions/advanced/nz.php

 

[ChrisLayfield]

vbaInet - thanks again, the previous code and ht elinks have been a big help. I have though run into another dlookup issue.

Dim strProjMgrID as String
Dim strProjMgrNAme as String
strProjMgrID = Nz(DLookup("ProjectMgr", "tbl_NCS_Projects", _
                    "tbl_NCS_Projects.ProjectID = " & "Forms!frmIncidentReport.ProjectID"))
strProjMgrName = Nz(DLookup("FirstName", "tbl_Employees", _
                    "tbl_Employees.EmployeeID = " & "'strProjMgrID'")) & " " & _
                 Nz(DLookup("LastName", "tbl_Employees", _
                    "tbl_Employees.EmployeeID = " & "'strProjMgrID'"))

all of the variables are text, but the strProjMgrName dlookup does not complete for some reason and I cannot for the life of me figure it out. I used breaks to determine the strPrijMgrID is in fact correct and a text value, but have no idea why the next part fails.

 

[boblarson]

It would help if you had your quotes in the right places:

strProjMgrName = Nz(DLookup("FirstName", "tbl_Employees", _
"tbl_Employees.EmployeeID = " & "'strProjMgrID'")) & " " & _
Nz(DLookup("LastName", "tbl_Employees", _
"tbl_Employees.EmployeeID = " & "'strProjMgrID'"))

SHOULD BE (See red)

strProjMgrName = Nz(DLookup("FirstName", "tbl_Employees", _
"tbl_Employees.EmployeeID = '" & strProjMgrID & "'")) & " " & _
Nz(DLookup("LastName", "tbl_Employees", _
"tbl_Employees.EmployeeID = '" & strProjMgrID & "'"))

 

[boblarson]

But if strProjMgrID is NUMERIC then it should not have the single quotes at all:

strProjMgrName = Nz(DLookup("FirstName", "tbl_Employees", _
"tbl_Employees.EmployeeID = " & strProjMgrID)) & " " & _
Nz(DLookup("LastName", "tbl_Employees", _
"tbl_Employees.EmployeeID = " & strProjMgrID))

 

[ChrisLayfield]

Bob many thanks, I have more problems with the quotations than anything else. One day I will have it down. Works like a peach.