AARRRGGHHH!!!! Still can't get the linked table thing to work!

[Sylvialn]

Ok...so here's what I have boiled down to...

Public Function CvtEmpName(strIDNo)
' Converts an employee number into a name
Dim stringname As String
stringname = ("SELECT EmployeeInfo.FullName FROM EmployeeInfo WHERE EmployeeID =" & (strIDNo))
CvtEmpName = stringname
End Function

So...to refresh everyone's memory (HAHAHA) what I want to do is pull up the employes name based off their id number to print it out on the report. Only now, what it tells me is that the data being passed is too long for the field. So when I put CvtEmpName out in a message box for me to see, what it is showing is the entire Select statement. So..CvtEmpName has the Select statement stored in it rather than the name!!! AAARRRRGGGGHHHHH!!!! What am I doing wrong???? How can I fix this code?????? Please..anyone...can you help me!!!!?????? PLEASE!!!

Thanks,
Syl

 

[tlbroadbent]

Public Function CvtEmpName(strIDNo)
' Converts an employee number into a name
CvtEmpName = Dlookup("[FullName]","EmployeeInfo","[EmployeeID]=" & strIDNo)
End Function
Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Sylvialn]

Terry...once again...you've saved my life!!!! Thanks so much!!! I about had it!

 

[Sylvialn]

Ok Terry....I was soooo sure "we" (HAHAHA) had it. I typed your wonderful code in and it gave me a "run-time error 62506: Data type mismatch in criteria". But, when I check the 2 fields that are being compared (EmployeeID & strIDNo)they both are text fields of 3 chars. I don't understand what is wrong!!!! if you happen to get this message...PLEASE help a struggling student! Or anyone else for this matter! Thanks!!!

 

[Rohdem]

You need single quotes around the value of strIDNo

CvtEmpName=Dlookup("[FullName]","EmployeeInfo","[EmployeeID]= '" & strIDNo & "'&quot Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

 

[Nebuchednezzar]

This may be a round-a-bout way of doing it, but how about...

Public Function CvtEmpName(strIDNo)
' Converts an employee number into a name
Dim db as currentdb
Dim rst as Recordset

set rst = db.openrecordset("SELECT EmployeeInfo.FullName FROM EmployeeInfo WHERE EmployeeID = " & strIDNo)
CvtEmpName = rst!FullName
End Function


also, If you are worried about there being null values returned or anything, you can include an nz function in your select ...
Select nz(Employeeinfo.fullname, 'No Name') from empoyeeinfo where employeeid = " & stridno).....