Re: Query in VBA Type Mismatch

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
http://I.Am/DougSteele
(no private e-mails, please)


"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: New Login questiion
    ... Dim RS as DAO.Recordset ... Note that you can eliminate the need for a recordset by using DLookup: ... Private Sub tbBadge_AfterUpdate ... I do seem to be getting a type mismatch error around to "Set RS" code. ...
    (microsoft.public.access.formscoding)
  • Recordset based on a SQL string gives Too few parameters error -- Access97
    ... I am trying to restrict a recordset to those records which concern a ... Dim rs As DAO.Recordset ... This seems to be referring to the three fields specified in the WHERE ... WHERE statement out, I do not get an error message, and if I reduce the ...
    (microsoft.public.access.formscoding)
  • Re: Compile error:Method or data member not found
    ... You were correct to change the Public statement to DAO.Recordset instead of ... You may also try changing the Dim statement for db to ... > But now I got the subject error message when I tried to run the program. ... > The recordset was defined as follows: ...
    (microsoft.public.access.gettingstarted)
  • Re: Compile error:Method or data member not found
    ... Thank you, Wayne. ... now I got the error message "no current records". ... Dim rstMatch As DAO.Recordset ... >> Public rstDonor As Recordset ...
    (microsoft.public.access.gettingstarted)
  • Type mismatch
    ... Private Sub Command13_Click ... Dim Rs As Recordset ... I still get a type mismatch. ... Ultimately what I'm trying to do is get access to the form's recordset. ...
    (microsoft.public.access.forms)