Query in VBA Type Mismatch

[James Stephens]

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

 

[Duane Hookom]

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

 

[James Stephens]

Here is what I get in the debug statement:

SELECT [tblEmployee_Airport].[Airport_Code] From [tblEmployee_Airport] WHERE
([tblEmployee_Airport].[Employee_Code] = 1);

The value what the query is looking up from the table is a Number set to
Long Interger

The exact error I get is Run Time Error 13, Type Mismatch

My guess is that it has something to do with the criteria, but I can't seem
to make it work.

Thanks,
Jim

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
--

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

 

[Duane Hookom]

Try paste the SQL into a new, blank query. Check to see if Access places ""
around the 1.

--
Duane Hookom
MS Access MVP
--

Here is what I get in the debug statement:

SELECT [tblEmployee_Airport].[Airport_Code] From [tblEmployee_Airport]
WHERE
([tblEmployee_Airport].[Employee_Code] = 1);

The value what the query is looking up from the table is a Number set to
Long Interger

The exact error I get is Run Time Error 13, Type Mismatch

My guess is that it has something to do with the criteria, but I can't
seem
to make it work.

Thanks,
Jim

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
--

message

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

 

[Douglas J. Steele]

Try Duane's suggestion. You're likely getting an ADO recordset, when you
need a DAO recordset given your code. That would be the "Type Mismatch" the
error message is highlighting.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Here is what I get in the debug statement:

SELECT [tblEmployee_Airport].[Airport_Code] From [tblEmployee_Airport]
WHERE
([tblEmployee_Airport].[Employee_Code] = 1);

The value what the query is looking up from the table is a Number set to
Long Interger

The exact error I get is Run Time Error 13, Type Mismatch

My guess is that it has something to do with the criteria, but I can't
seem
to make it work.

Thanks,
Jim

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
--

message

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