Re: Query in VBA Type Mismatch

I am having trouble seeing which line of code raises the error....
What do you get in the strSQL debug?
Try to be explicit with your Dim's
Dim db As DAO.Database
Dim rs As DAO.Recordset

--
Duane Hookom
MS Access MVP
--

"James Stephens" <JamesStephens@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A0F19BA9-E2D7-460F-9083-62B6D8BF1764@xxxxxxxxxxxxxxxx
I have a query that calls a function to get a list of concatenated values.
I
have used it before and it works, but I am trying to use it with a
different
query and I get a type mismatch error. Here is the function: (the value
for
EmployeeCode being passed from the query is Long Interger from an
Autonumber
Field)

Function GetAirports(EmployeeCode As Long) As String
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strReturn As String
Set db = CurrentDb
strSQL = "SELECT [tblEmployee_Airport].[Airport_Code] From
[tblEmployee_Airport] WHERE ([tblEmployee_Airport].[Employee_Code] = " &
EmployeeCode & ");"
Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL)
With rs
If Not (.EOF And .BOF) Then
Do Until .EOF
strReturn = strReturn & !Airport_Code & vbCrLf
.MoveNext
Loop
End If
.Close
End With
If Len(strReturn) > 0 Then
strReturn = Left(strReturn, Len(strReturn) - 2)
End If
GetAirports = strReturn
End Function

Anybody got an idea as to what I am doing wrong?

Thanks,
Jim



.

Relevant Pages

  • Re: VBScriptADO Max Length of String
    ... Dim objFSO, objFile, strServers, arrServers, strServer ... Dim strTableName, strSql, objConn, strSvrName, strDBName ... whole file is one string. ...
    (microsoft.public.windows.server.scripting)
  • Re: SQL Fustrations
    ... Dim strLogEvent As String ... Dim strSQL As String ...
    (microsoft.public.vb.general.discussion)
  • RE: Creating a query using multiselect list box and text boxes
    ... Dim strSQL As String ... Dim strWhere As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Extracting Data from Composite Field
    ... Function libUnravel(strDesc As String, ByVal n As Integer) As Variant ... Dim varElements As Variant ... Dim strReturn As String ... If lngPos> 0 And lngPos < Len) Then ...
    (comp.databases.ms-access)
  • Re: Help with found code
    ... Debug.Print strSQL ... Doug Steele, Microsoft Access MVP ... If it's a different database, you'll need to create a separate Connection ... Dim cn As ADODB.Connection ...
    (microsoft.public.access.modulesdaovba)
Loading