Re: Query in VBA Type Mismatch

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

--
Duane Hookom
MS Access MVP
--

"James Stephens" <JamesStephens@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B26F04B1-82EA-42DD-9D60-86CF094D5EF3@xxxxxxxxxxxxxxxx
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

"Duane Hookom" wrote:

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: Exporting from Access 2002 to Excel 2002 into multiple tabs
    ... and it highlights the first line of code "DIM qdf As DAO.QueryDef". ... you don't need a separate query for what you seek to do. ... <MS ACCESS MVP> ... Dim strSQL As String, strTemp As String ...
    (microsoft.public.access.queries)
  • Re: Exporting from Access 2002 to Excel 2002 into multiple tabs
    ... and it highlights the first line of code "DIM qdf As DAO.QueryDef". ... you don't need a separate query for what you seek to do. ... <MS ACCESS MVP> ... Dim strSQL As String, strTemp As String ...
    (microsoft.public.access.queries)
  • Re: Exporting from Access 2002 to Excel 2002 into multiple tabs
    ... and it highlights the first line of code "DIM qdf As DAO.QueryDef". ... you don't need a separate query for what you seek to do. ... <MS ACCESS MVP> ... Dim strSQL As String, strTemp As String ...
    (microsoft.public.access.queries)
  • Re: Help with sub-form and after update code
    ... Duane Hookom ... MS Access MVP ... Dim intTotalNumberOfFaults As Integer ... Dim intTotalScoringPenalties As Integer ...
    (microsoft.public.access.formscoding)
  • Re: Scroll through records and get match
    ... Doug Steele, Microsoft Access MVP ... "User-defined type not defined" at Dim dbs As DAO.Database. ... that I posted is a select query. ... cannot use it to open an SQL statement that you generate via code. ...
    (microsoft.public.access.modulesdaovba)
Loading